Power Query in Excel: Import and Clean

The same tool as in Power BI

Power Query has been built into Excel for Windows for several versions and into Microsoft 365; Excel for Mac and Excel for the web offer part of it. It is the same engine as in Power BI Desktop, with the same M language: everything you learn here is reused as is in the BI2 course, and vice versa. Only the location of the commands and the load options differ.

Why use it in Excel rather than copy-paste? Because cleaning becomes a recorded recipe: next month, you replace the source file, click Refresh, and all the steps are replayed. No more manual operations to redo, no more forgetting.

Importing a file

On the Data tab:

• Get Data > From File > From Text/CSV (or the From Text/CSV button);
• choose the file: a preview opens, with the file origin (encoding, 65001 for UTF-8), the delimiter and type detection;
• click Transform Data to open the editor, rather than Load, which would import without cleaning.

The editor is identical to Power BI’s: queries pane, preview, applied steps on the right, formula bar (View tab), and the Column quality, Column distribution and Column profile options for diagnosis.

Cleaning in the right order

A file exported from software often contains spaces, inconsistent capitalization, blank rows, duplicates and missing values. Recommended order:

1. Structure: check the promoted headers, Home > Remove Rows > Remove Blank Rows;
2. Text: select the text columns, Transform > Format > Trim, then Capitalize Each Word;
3. Duplicates: select the key column (or all columns), Remove Rows > Remove Duplicates;
4. Types: right-click the header > Change Type > Using Locale if the format differs from the machine’s (dates dd/mm/yyyy, decimals);
5. Missing values: Transform > Replace Values, replacing null with 0 when it makes business sense.

Normalizing text before removing duplicates is essential: “Paris” and “paris” are not duplicates for Power Query as long as the case differs.

let
    Source = Csv.Document(File.Contents("C:\Formation\clients_brut.csv"), [Delimiter = ";", Encoding = 65001]),
    Entetes = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    SansVides = Table.SelectRows(Entetes, each [ID Client] <> "" and [ID Client] <> null),
    Texte = Table.TransformColumns(SansVides, {{"Nom", each Text.Proper(Text.Trim(_)), type text},
                                              {"Ville", each Text.Proper(Text.Trim(_)), type text}}),
    SansDoublons = Table.Distinct(Texte, {"ID Client"}),
    Types = Table.TransformColumnTypes(SansDoublons, {{"ID Client", Int64.Type},
             {"Date inscription", type date}, {"CA", type number}}, "fr-FR"),
    CAComplet = Table.ReplaceValue(Types, null, 0, Replacer.ReplaceValue, {"CA"})
in
    CAComplet

You don’t have to write this code: the interface generates it. But reading it in Home > Advanced Editor lets you check the order and give the steps meaningful names.

Choosing where to load

Home > Close & Load creates a structured table on a new sheet. Close & Load To… offers a choice:

Option	When to use it
Table	the result must be visible, filtered, used in formulas
PivotTable Report	you want a PivotTable directly
Only Create Connection	intermediate query, reused by other queries
Add this data to the Data Model	large volumes, several related tables, DAX measures (lessons 3 and 4)

An Excel sheet is limited to 1,048,576 rows; the data model has no such per-sheet limit, which makes it the right destination for large volumes.

The Queries & Connections pane (Data > Queries & Connections) lists the queries, the number of rows loaded and any errors. A right-click > Load To… lets you change the destination afterwards.

Never edit the loaded table by hand

The table produced by a query is rewritten on every refresh: a correction typed into it will be lost. Every correction is made in the query. You can, however, add formula columns to the right of the loaded table: Excel keeps them and copies them down to new rows.