Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Replace Values and Fill Down


Share

Where You Are

This page continues the cleanup from Split and Merge.

Open pq_cleanup.pbix, the report you saved there, select Home > Transform data, and select the last step of the poke_mart_raw_2024 query, Renamed Columns1.

After the Split and Merge chapter, the query has 8 columns and 1,276 rows.

The first rows look like this:

123OrderIDDATEOrderDateABCStoreABCRegionABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove CityHoennHyper Potion112001200
10021/1/2024Cerulean CityKantopoke ball192003800
10031/2/2024Lilycove CityHoenn  Great Ball 136007800
10041/2/2024Cerulean CityKantoAntidote9100900
10051/3/2024Violet CityJohtoPotion193005700
10061/3/2024Slateport CityHoennParalyze Heal1200200
10071/3/2024Lilycove CityHoennPARALYZE HEAL92001800
........................

Two problems are left:

  • Region has a typo: some rows say Hoen instead of Hoenn.
  • Product is written in many ways: poke ball, PARALYZE HEAL, and names with extra spaces.

You fix both in this chapter.

You also learn Fill Down, which you need for the targets file in the next chapter.


Find the Typo

Select the filter arrow in the Region column header.

The list shows four values, but the shop has only three regions.

Select Cancel to close the filter list without changing anything.

To see how many rows have each value, select Region with Column profile turned on and profiling based on the entire data set, as in Filter Rows.

These are the counts in the 1,276 rows:

RegionRows
Hoen11
Hoenn377
Johto462
Kanto426

The 11 Hoen rows belong to the Hoenn region.


Replace Values

Replace Values replaces one value with another in the selected columns.

  1. Select the Region column.
  2. On the Home tab, select Replace Values. The same command is on the Transform tab, and on the right-click menu of the column header.
  3. In Value To Find, type Hoen.
  4. In Replace With, type Hoenn.
  5. Expand Advanced options and select the Match entire cell contents check box.
  6. Select OK.

The Replace Values dialog in the Power Query Editor with Hoen in Value To Find, Hoenn in Replace With, and Match entire cell contents selected under Advanced options

Power Query adds a step called Replaced Value to Applied Steps.

The 11 Hoen cells now say Hoenn, and Region has three values:

RegionRows
Hoenn388
Johto462
Kanto426


The Trap: Match Entire Cell Contents

Why the check box?

In a text column, Replace Values does not compare whole cells by default.

It replaces the text wherever it appears inside a cell.

The word Hoenn contains Hoen.

So without the check box, Power Query also turns every correct Hoenn into Hoennn:

OrderIDStoreRegion beforeWithout the check boxWith the check box
1001Lilycove CityHoennHoennnHoenn
1006Slateport CityHoennHoennnHoenn
1066Lilycove CityHoenHoennHoenn
1078Slateport CityHoenHoennHoenn

In this data, all 377 correct Hoenn cells would become Hoennn.

You would fix a typo in 11 rows and create a new one in 377 rows.

Region would still have four values:

Region without the check boxRows
Hoenn11
Hoennn377
Johto462
Kanto426

If you already made this mistake, you do not need to start again.

In Applied Steps, select the gear icon next to Replaced Value.

The Replace Values dialog opens again.

Select Match entire cell contents and select OK.

Note: Excel's Find and Replace dialog has the same trap, and the same fix: an option called Match entire cell contents.



Clean the Product Column

The shop sells 10 products, but the Product column writes them in 40 different ways.

285 of the 1,276 rows have a messy product name:

ProblemExampleRows
All lowercasepoke ball125
All UPPERCASEPARALYZE HEAL78
Extra spaces before and after"  Great Ball "82

Power Query compares text exactly, including uppercase, lowercase and spaces.

For Power Query, poke ball, POKE BALL and Poke Ball are three different values.

Two commands on the Format menu fix all of them:

  1. Select the Product column.
  2. On the Transform tab, select Format > Trim. This removes the spaces at the start and end of each value. The new step is called Trimmed Text.
  3. With Product still selected, select Format > Capitalize Each Word. This makes the first letter of each word uppercase and all other letters lowercase. The new step is called Capitalized Each Word.

The Transform tab of the Power Query Editor with the Product column selected and the Format menu open, showing lowercase, UPPERCASE, Capitalize Each Word, Trim and the other format commands

Here are real rows from the file, before and after each step.

The quotes show where the spaces are:

OrderIDProduct in the fileAfter TrimAfter Capitalize Each Word
1001"Hyper Potion""Hyper Potion""Hyper Potion"
1002"poke ball""poke ball""Poke Ball"
1003"  Great Ball ""Great Ball""Great Ball"
1007"PARALYZE HEAL""PARALYZE HEAL""Paralyze Heal"
1020"antidote""antidote""Antidote"
1021"  Poke Ball ""Poke Ball""Poke Ball"
1040"HYPER POTION""HYPER POTION""Hyper Potion"

After Trim, the column still has 30 different values.

After Capitalize Each Word, only 10 are left: exactly the 10 products the shop sells.

Note: Trim in Power Query removes spaces only at the start and the end of the text.

The Excel TRIM function also removes double spaces between words.


Fill Down

In the next chapter you load a second file, poke_mart_targets_2025.xlsx, with the sales targets for 2025. Its Region column is filled in only for the first store of each region, the way people often type a report by hand.

In Power Query it looks like this:

ABCRegionABCStore123Jan123Feb123Mar...
KantoViridian City510006500075000...
nullCerulean City520005000072000...
JohtoGoldenrod City10400055000104000...
nullViolet City250003400041000...
HoennSlateport City590005400077000...
nullLilycove City530005500031000...

Power Query shows the empty cells as null, which means "no value".

A person can see that Cerulean City belongs to Kanto. Power BI cannot.

A chart of targets by region would put Cerulean City, Violet City and Lilycove City in a group with no region at all.

Fill Down copies each value down into the null cells below it, until it reaches the next value:

  1. Select the Region column.
  2. On the Transform tab, select Fill > Down. You can also right-click the column header and select Fill > Down.

The filled cells are marked in green:

ABCRegionABCStore123Jan123Feb123Mar...
KantoViridian City510006500075000...
KantoCerulean City520005000072000...
JohtoGoldenrod City10400055000104000...
JohtoViolet City250003400041000...
HoennSlateport City590005400077000...
HoennLilycove City530005500031000...

There is also Fill > Up, which copies values upward into the null cells above them.

Note: Fill Down fills only null cells.

A cell that holds empty text is not null, so it is not filled.

If your column has empty text, first use Replace Values with an empty Value To Find and null in Replace With.

You will use Fill Down on the real targets file in the next chapter.


Check Your Work

The poke_mart_raw_2024 query still has 8 columns and 1,276 rows.

Applied Steps has three new steps: Replaced Value, Trimmed Text and Capitalized Each Word.

The first rows now look like this:

123OrderIDDATEOrderDateABCStoreABCRegionABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove CityHoennHyper Potion112001200
10021/1/2024Cerulean CityKantoPoke Ball192003800
10031/2/2024Lilycove CityHoennGreat Ball136007800
10041/2/2024Cerulean CityKantoAntidote9100900
10051/3/2024Violet CityJohtoPotion193005700
10061/3/2024Slateport CityHoennParalyze Heal1200200
10071/3/2024Lilycove CityHoennParalyze Heal92001800
........................

Region now has 3 values and Product has 10. Good job! The 2024 data is clean.

Select Close & Apply, then File > Save to save pq_cleanup.pbix.

The next page continues from here.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.