Power Query¶

Exercise power-query-annex.pdf · PDF

Quick test, no peeking. Last lesson you ran one script and games_clean appeared. What did that script do to price? And to owners?

If you had to think, good. Now the real question: how could you find out? You open the .sql file and read it. Every decision is written there, in order. That file is the recipe of your cleaning.

Here is the catch. Most people who clean data at work never write SQL. They click. So today you clean the same file by clicking, in Power BI, and you ask one question the whole time: when you click, where does the recipe go?

A kitchen that writes the recipe¶

Power Query is the part of Power BI that prepares data before you use it: it connects to a source, and then cleans and reshapes the table step by step.

You met Power BI in Getting data with Power BI. There you pressed Load and took the data as it came. Power Query is the room you skipped: the one between the source and the report.

Think of two kitchens.

In the SQL kitchen you write the whole recipe first, then the kitchen cooks it. That was games_clean.sql.

In the Power Query kitchen you just cook. But a notebook on the wall writes down every move you make, one line per move. When you finish, the recipe is there, and you never wrote it.

Same dish. And in both kitchens the raw ingredient, the CSV file, is never touched.

flowchart LR
    CSV["<b>video_games.csv</b><br>the raw ingredient:<br>never modified"]
    subgraph PQ ["Power Query (the kitchen that writes the recipe)"]
        direction LR
        S1["Source"] --> S2["Promoted Headers"] --> S3["Changed Type"] --> S4["your steps..."]
    end
    CSV --> S1
    S4 --> M["<b>Table in Power BI</b><br>the dish: clean,<br>ready for charts"]

The Power Query Editor¶

Open Power BI Desktop and go Home → Get data → Text/CSV. Choose video_games.csv (download it from the Resources page). In the preview window press Transform Data, not Load. A second window opens.

The Power Query Editor is the window where you see a preview of your table and apply transformations to it.

Applied Steps is the list of every transformation done to the table, in the order it was done. It is the notebook on the kitchen wall.

Look at it now. It already has three lines and you have not done anything yet:

That third line deserves suspicion. In Types and formats the rule was: when you convert, you declare the format, you never let the tool guess. Power Query does guess, and it guesses from the first 200 rows of the file. Look at the small icon to the left of each column name: it shows the type that was chosen. You will correct two of those guesses in a moment.

Look before you clean¶

In Data quality you audited the table with COUNT queries before fixing anything. Power Query has the same habit built in.

On the View tab, tick Column quality and Column distribution. Under each column name a small bar appears, with the share of values that are Valid, Error and Empty.

Now look at metascore. In SQL you found that 23,838 of the 26,688 rows have no Metascore: 89%. Does Power Query agree?

Before you trust what you read, look at the bottom-left corner of the window. It says Column profiling based on top 1000 rows. Click that message and switch to Column profiling based on entire data set.

There is a second surprise, and it is the most important idea of this page. In this CSV, "empty" is not empty. It is the two letters NA:

47,Half-Life 2: Deathmatch,"Nov 1, 2004",4.99,"5,000,000 .. 10,000,000",Valve,Valve,0,0,NA

When you uploaded the file to Superset, the upload turned every NA into a real NULL, and you never saw the letters. Power Query does not assume that. To Power Query, NA is a piece of text like any other until you say otherwise. Telling it so is your first step.

The same cleaning, twice¶

Six steps. For each one you get the clicks in Power Query, the SQL twin on the table games, and one number that proves both did the same thing. After every click, look at Applied Steps: a new line has appeared.

Step 1 — NA means empty. Select the column price. Transform → Replace Values: Value to find NA, Replace with null.

There is nothing to write in SQL: the upload already did it. Count the result instead.

SELECT COUNT(*) - COUNT(price) AS empty_prices FROM games;

Answer: 3,095 prices are empty.

Step 2 — price is a number, written the American way. Right-click the header of price → Change Type → Using Locale... → Data type: Decimal Number, Locale: English (United States).

In SQL, price is already a number. But ask yourself who decided that 9.99 uses a dot for the decimals. The next section is about that question.

Step 3 — is_free, then fill. Two moves, in this order.

First, Add Column → Conditional Column: new column name is_free; if price equals null then true, else false.

Then, Replace Values on price: null → 0.

SELECT COUNT(*) AS free_games
FROM (SELECT COALESCE(price, 0) AS price, (price IS NULL) AS is_free FROM games) AS t
WHERE is_free;

Answer: 3,095 rows are flagged as free. The same number as step 1, as it must be.

Step 4 — owners becomes a number. The column holds text such as 10,000,000 .. 20,000,000. Select it, then:

• Transform → Extract → Text Before Delimiter, delimiter ..
• Replace Values: , → nothing
• Replace Values again, with Advanced options → Replace using special characters → Non-breaking Space → nothing
• Change the type to Whole Number

The third bullet is the invisible space you fought in Units and duplicates: it looks like a space and is not one.

SELECT MAX(REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint) AS max_owners_min
FROM games;

Answer: 100,000,000. That is Dota 2.

Step 5 — release_date becomes a date. Right-click the header → Change Type → Using Locale... → Date, English (United States). Then look at Column quality: is there any Error?

In SQL this was the CASE with two TO_DATE calls. Count how many rows fit each format:

SELECT COUNT(*) FILTER (WHERE release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$') AS full_dates,
       COUNT(*) FILTER (WHERE release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$')             AS month_only
FROM games;

Answer: 26,646 full dates such as Nov 16, 2004, and 41 with only a month, such as Jun 2009. That leaves exactly one row that fits neither: 8 Oct, 2014. Find out what Power Query does with those 42 odd rows. Whatever it does, Column quality will tell you.

Step 6 — a band and a filter. Add Column → Conditional Column: name price_band, with one branch per band (Free, Under 5, 5 to 15, 15 to 30, 30 or more). Then click the filter arrow on the header of game and untick NA.

In SQL: the CASE WHEN from Categorisation and WHERE game IS NOT NULL.

SELECT COUNT(*) FROM games WHERE game IS NOT NULL;

Answer: 26,685 rows. The three games without a name are gone.

To finish, press Home → Close & Apply. Until now you were working on a preview. Now the steps run on the whole file and the table lands in Power BI.

Your Applied Steps list now reads like this (repeated steps get a number):

The order is the logic¶

Go back to step 3 and swap its two moves. First replace null with 0, then add is_free.

What happens? Every game is "not free". When the conditional column looks for null, there is none left: you already overwrote the evidence.

In a recorded recipe, the order of the lines is the logic. SQL has the same rule in a different shape: in games_clean.sql, is_free reads the original price, not the filled one.

The same reasoning explains step 1. If you skip it, no price is ever null, so is_free is also false everywhere.

A number is not a number: the locale¶

A locale is the set of regional conventions a computer uses to read and write values: which character marks the decimals, in which order a date is written, how the months are called.

Here is a sentence that sounds obviously true: "9.99 is 9.99 on every computer." It is false when the number arrives as text, which is what a CSV is.

Power Query Desktop reads text using the regional format of your Windows. On a PC set to Spanish, the text 9.99 can turn into 999 or into an error. Nothing warns you. The column simply fills with wrong numbers.

Using Locale tells the kitchen which country the ingredient comes from. This file was written in the United States, so dates look like Nov 16, 2004 and decimals use a dot. That is why steps 2 and 5 use Change Type → Using Locale... and not a plain Change Type.

Rewind, and read the code¶

Back in the editor (Home → Transform data), click an early line in Applied Steps. The preview travels back to that moment: the table as it was after that step. Click the last line and you are back to now. The cross next to a step deletes it. That is your undo.

Now open View → Advanced Editor. A window full of code appears.

M is the language in which Power Query writes down your steps. Every click you made added one line of M.

The kitchen was writing the recipe all along. Clicking did not replace the code. It wrote the code for you.

Power Query or SQL?¶

Imagine your boss sends a new CSV every Monday, with the same columns. Which tool do you choose, and what do you do each Monday?

Either one. In Power Query you press Refresh and every step runs again on the new file. In SQL you run the script again. What matters is not clicks versus code. It is that the recipe exists and can be replayed.

The only wrong answer is the third kitchen: opening the CSV in a spreadsheet and fixing cells by hand. It leaves no recipe at all. Next Monday you start from zero, and nobody can check what you did.

Key takeaways¶

• Power Query is the data-preparation part of Power BI. You work in the Power Query Editor, opened with Transform Data.
• Applied Steps records every transformation, in order. It is the recipe, and behind it there is code in a language called M.
• Every click has a SQL twin: Replace Values ↔ COALESCE, Conditional Column ↔ CASE WHEN, Text Before Delimiter ↔ SPLIT_PART, Filtered Rows ↔ WHERE.
• The order of the steps is the logic: flag the empty prices before you fill them, or the flag is false for every row.
• A number or a date written as text has a locale. Use Change Type → Using Locale... to say where the file comes from.
• Profiling reads only the first 1,000 rows unless you switch to the entire data set, and NA is text until you replace it with null.
• Clicks or code is not the question. The question is whether the recipe exists and can be replayed.

Quick check¶

The table is clean, in two kitchens. Nobody has asked it anything yet. Next: Exploratory statistics, where games_clean finally answers a simple question with a surprising answer: how much does a game cost?