How to Clean Messy Sales Data in Power BI Power Query

By Ravindra Bagale · Last updated: 15 October 2026

Practice files: blinkit-orders-small-raw.csv (the 12 rows used on this page) and blinkit-orders-raw.csv (315 rows with the same kinds of mess, for the practice task).

Made-up data: every order, product, store and number here is fictional practice data. The file imitates an export from a quick-commerce app such as Blinkit in Maharashtra cities, but no real order, shop or person is used. Amounts are in ₹ and dates are written DD-MM-YYYY.

1. The problem

Here is the small file opened in Notepad. It has the mess you will meet in real exports:

If you load this straight into a report:

1. Power BI calls the columns Column1, Column2 … because the first line is a title.
2. Order BLK1003 and BLK1006 are counted twice.
3. "2$" is text, so the sales column cannot be summed.
4. "NA" in price makes the column text, or gives errors when you change it to a number.

2. The idea in simple words

Power Query is the kitchen; the report is the dining table. You wash and cut the vegetables in the kitchen, once, and every dish served afterwards is clean. Every cleaning action you take is saved as a step, and Power BI replays the steps each time you refresh.

The one rule that decides what to delete:

Why keep the row when a metric is missing? Because the order still happened. If revenue is unknown for one order, the order still counts in "Total orders", its units still count in "Sales" and its city still counts in a city chart. Deleting the row throws all of that away.

Why null and not 0? Null means "not known". Power BI's SUM and AVERAGE skip nulls. A 0 is a real number: it says the price was zero, and it pulls every average down.

3. The data

The 12 order rows (line 1 and the header line are not shown). The cells that need attention are in bold:

The file also has promo_type, promo_bin, delivery_date and an empty Column13. You will remove promo_bin and Column13 because no report needs them.

Before you click anything, predict with a pencil: which rows will survive? (Answer in section 5.)

4. Step by step

Load the file

1. Open Power BI Desktop. Home › Get data › Text/CSV.
2. Pick blinkit-orders-small-raw.csv and click Open.
3. In the preview window click Transform Data (not Load). The Power Query Editor opens.

Fix the top of the table

4. Home › Remove Rows › Remove Top Rows, type 1, OK. The title line is gone.
5. Home › Use First Row as Headers. The columns are now called order_id, product_id and so on.

Primary ID: order_id

6. Click the filter arrow on order_id, untick (blank), OK. Row 5 goes.
7. Click the order_id header to select it. Home › Remove Rows › Remove Duplicates. The second BLK1003 and the second BLK1006 go.

Foreign ID: product_id

8. Click the filter arrow on product_id, untick (blank) and NA, OK. Rows 6 and 9 go.
9. Do not remove duplicates here. P101, P102, P103 and P105 each sell more than once, and that is correct.

Remove columns you do not need

10. Ctrl+click the promo_bin and Column13 headers, right-click › Remove Columns.

Metric columns: keep the rows, fix the values

11. Right-click the sales header › Transform › Extract › Text Before Delimiter, type $, OK. "2$" becomes "2".
12. Click the type icon (ABC) at the left of the sales header › Whole Number.
13. Right-click the price header › Replace Values. Value To Find: NA. Replace With: leave it empty. Open Advanced options and tick Match entire cell contents. OK.
14. Change the price type to Decimal Number. The empty cell now shows null.
15. Do steps 13 and 14 for revenue.
16. Do step 13 for stock, then set its type to Whole Number.

Dates written DD-MM-YYYY

17. Right-click order_date › Change Type › Using Locale…. Data Type: Date. Locale: English (India). OK.
18. Do the same for delivery_date.

Load and check

19. Home › Close & Apply.
20. Go to Table view (the grid icon on the left) and read the row count at the bottom of the screen.

5. Your result should look like this

Table view shows 7 rows:

(Table view shows the dates in your computer's date format; the values are the same dates.)

Check these totals by hand from the table:

6. What happened

The row count tells the story:

The last line is the one to remember. The metric steps changed values, never the number of rows.

7. Explore further

• Every click is a step. Look at Applied Steps on the right: Source, Promoted Headers, Filtered Rows, Removed Duplicates and so on. Click any step to see the data at that moment. The next chapter uses this to fix an error.
• Blank cells become null on their own. When a number column has an empty cell, the type change turns it into null. Text such as "NA", "-" or "n/a" does not; you must replace it first.
• Why "Match entire cell contents"? Without it, Replace Values also replaces the letters NA inside a longer value in the same column, so a value such as "NA-12" would turn into "-12". With the box ticked, only cells that are exactly NA change.
• Why Using Locale for dates? A computer set to English (United States) reads 05-04-2025 as 4 May. Telling Power Query the file is English (India) makes it 5 April. Check one date you know after the change.
• Your computer matters for big files. Power BI Desktop runs on Windows and holds the data in memory, so large files need RAM. A hexa-core processor is one CPU with 6 cores; more cores and more RAM help refresh speed. If Power BI freezes, open Task Manager (Ctrl+Shift+Esc), and end the Power BI task only if it stays "Not responding", because unsaved work is lost.

8. Common mistakes

9. Practise on your own

10. Think like a professional

• Business view. A missing revenue value is a question for the source team, not a reason to lose the order. Keep the row, show the null count in your notes ("22 orders have no revenue in the export") and ask the team to fix it at the source.
• Performance. Remove unwanted columns early. Fewer columns mean a smaller model and faster refresh.
• Maintenance. Rename steps you will need to find again (right-click a step › Rename, for example "Removed blank order_id"). Next month's file goes through the same steps on refresh.
• Check every refresh. Keep a row-count card on a hidden check page. A sudden drop after refresh means the export changed.

Interview questions

1. Which rows do you delete when an ID is blank, and which when a metric is blank? Delete rows with a blank primary or foreign ID; keep rows with a blank metric and leave the metric as null.
2. Why replace NA with null rather than 0? Null is skipped by SUM and AVERAGE; 0 is counted and makes averages wrong.
3. Where do you remove duplicates? Only on the primary key, such as order_id.
4. Why use Change Type with Locale? So DD-MM-YYYY dates are read as day-month, whatever the computer's region setting is.

Recap

Ravi Sir's Cards for this lesson

Revision card

मराठी cards