Power Query in Excel for Beginners: Import a CSV, Remove Top Rows, Remove Duplicates, Column Quality and Date Locale

By Ravindra Bagale · Last updated: 15 October 2026

Practice files: quick-commerce-orders-raw.csv (the messy export) and power-query-clean-orders-step1-answer.xlsx (what your data should look like at the end of this chapter, with the list of applied steps).

Made-up data: the orders, product IDs, store IDs and amounts are made up for practice. They imitate an export from a quick-commerce app such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

About the figures: they are simplified diagrams of the Power Query Editor, drawn for this chapter, not screenshots. Menus can look slightly different in your version of Excel.

1. The messy file

Open the CSV in Notepad first, to see it as it really is:

1. Line 1 is a title, "Quick commerce orders export (made-up data)".
2. Line 2 says when it was exported.
3. Line 3 holds the column names: order_id, order_date, city, product_id, sales, price, revenue, stock, store_id, an unnamed empty column, promo_code and order_date_2.
4. Line 4 is blank (only commas).
5. Then 32 order lines, with these problems:
   • ORD3005, ORD3012 and ORD3021 appear twice;
   • one line has no order_id and one has NA;
   • ORD3099 has NA as its product_id;
   • order_date is month-day-year: 09-13-2026 is 13 September.

2. Get Data and the Power Query Editor

Data > Get Data lists every source Power Query can read:

1. From File: Workbook, Text/CSV, XML, JSON, PDF, Folder.
2. From Database: SQL Server, Access and others.
3. From Azure and From Online Services (such as SharePoint lists).
4. From Other Sources: web pages, ODBC and more.

For this file:

1. Data > Get Data > From File > From Text/CSV.
2. Pick quick-commerce-orders-raw.csv and click Import.
3. A preview opens. Click Transform Data (not Load).

The Power Query Editor opens in its own window: the data in the middle, the ribbon on top, and on the right the Query Settings pane with Applied Steps. Every change you make is added to that list. Click a step to see the data as it was at that point; click the × next to a step to delete it.

If Power Query already took the title line as the column names, delete its automatic steps Promoted Headers and Changed Type with the × and start from Source.

3. Remove the title lines and set the headers

1. Home > Remove Rows > Remove Top Rows, type 2, OK. The title and export lines are gone; the column names are now the first row.
2. Home > Use First Row as Headers. The names move up into the column headers.
3. Home > Remove Rows > Remove Top Rows, type 1, OK. The blank row is gone.

Applied Steps now shows three new steps. The unnamed empty column is called Column10 by Power Query, because it is the tenth column and its header was empty.

4. Remove bad IDs and duplicates

Filter out orders without an ID:

1. Click the filter arrow on order_id.
2. Untick (blank) and NA, click OK. 30 rows remain.

A filter removes the whole row, not just the NA cell. That is right here, because an order without an ID cannot be counted or matched. For other columns, think first.

Remove duplicate orders:

1. Select the order_id column.
2. Home > Remove Rows > Remove Duplicates. The second copies of ORD3005, ORD3012 and ORD3021 go: 27 rows.

Remove Duplicates on one selected column keeps the first row for each order_id. Only use it on a column that must be unique, like an order ID.

product_id: filter out NA the same way (ORD3099 has no product): 26 rows. Do not remove duplicates in product_id. Many orders buy the same product, so P101 appearing many times is correct.

5. Column Quality: find the empty columns

View > Column Quality adds three bars under each header:

1. Valid: cells with a proper value.
2. Error: cells that could not be converted.
3. Empty: blank cells.

Column10 shows 100% Empty: it has no data at all. Right-click its header > Remove. store_id shows 100% Valid. Column Quality looks only at the first 1,000 rows unless you switch the status bar option to Column profiling based on entire data set.

6. IDs are text, not numbers

order_id, product_id and store_id are labels, not quantities. Adding two order IDs means nothing, so keep them as Text (the ABC icon in the header). If Power Query ever guesses a number type (123) for an ID column, click the icon and choose Text. A leading zero in an ID such as S01 survives only as text.

7. Dates in the wrong order: Using Locale

order_date holds text like 09-13-2026. Click its type icon and choose Date. If your Windows region is India (day-month-year), two things go wrong:

1. 09-13-2026 becomes Error: there is no 13th month.
2. 09-05-2026 becomes 9 May 2026 without any warning, but it really is 5 September.

The second problem is worse because nothing looks broken. Undo the step (× in Applied Steps) and tell Power Query how the text was written:

1. Click the type icon of order_date > Using Locale...
2. Data Type: Date. Locale: English (United States).
3. Click OK.

Now 09-13-2026 is 13 September and 09-05-2026 is 5 September. Excel shows them in your own date format later.

8. Power Query's language

Every step in Applied Steps is written for you in Power Query's own formula language, M. Click View > Advanced Editor to see the whole recipe as text. You do not need to write M to use Power Query; the buttons write it. (DAX, which you may hear about, is a different language, used for calculations in Power Pivot and Power BI.)

9. The whole chapter on one page

1. Get Data > From Text/CSV > Transform Data opens the Power Query Editor.
2. Applied Steps records every change; × deletes a step.
3. Remove Duplicates only on a column that must be unique.
4. Column Quality shows Valid, Error and Empty for each column.
5. Using Locale reads dates in the order they were written.

10. Try at home

Recap

Quick revision cards

Englishमराठीहिंदी

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

More cards for every chapter: Ravi Sir's Cards.