Power Query¶

Exercici power-query-annex.pdf · PDF

Prova ràpida, sense fer trampes. A la lliçó passada vas executar un script i va aparèixer games_clean. Què li va fer aquell script a price? I a owners?

Si has hagut de pensar-hi, perfecte. Ara la pregunta de veritat: com ho podries esbrinar? Obres el fitxer .sql i el llegeixes. Cada decisió hi és escrita, per ordre. Aquest fitxer és la recepta de la teva neteja.

Aquí hi ha el parany. La majoria de gent que neteja dades a la feina no escriu mai SQL. Fa clics. Així que avui nets el mateix fitxer fent clic, a Power BI, i et fas una sola pregunta tota l'estona: quan fas clic, on va a parar la recepta?

Una cuina que escriu la recepta¶

Power Query és la part de Power BI que prepara les dades abans d'usar-les: es connecta a una font i després neteja i remodela la taula pas a pas.

Ja vas conèixer Power BI a Obtenir dades amb Power BI. Allà vas prémer Load i vas agafar les dades tal com venien. Power Query és la sala que et vas saltar: la que hi ha entre la font i l'informe.

Pensa en dues cuines.

A la cuina SQL escrius primer la recepta sencera i després la cuina la cuina. Això era games_clean.sql.

A la cuina Power Query simplement cuines. Però una llibreta a la paret apunta cada moviment que fas, una línia per moviment. Quan acabes, la recepta hi és, i tu no l'has escrita mai.

El mateix plat. I a les dues cuines l'ingredient cru, el fitxer CSV, no es toca mai.

flowchart LR
    CSV["<b>video_games.csv</b><br>l'ingredient cru:<br>mai es modifica"]
    subgraph PQ ["Power Query (la cuina que escriu la recepta)"]
        direction LR
        S1["Source"] --> S2["Promoted Headers"] --> S3["Changed Type"] --> S4["els teus passos..."]
    end
    CSV --> S1
    S4 --> M["<b>Taula a Power BI</b><br>el plat: net,<br>a punt per als gràfics"]

L'editor de Power Query¶

Obre Power BI Desktop i ves a Home → Get data → Text/CSV. Tria video_games.csv (descarrega'l de la pàgina de Recursos). A la finestra de previsualització prem Transform Data, no Load. S'obre una segona finestra.

El Power Query Editor és la finestra on veus una previsualització de la teva taula i hi apliques transformacions.

Applied Steps és la llista de totes les transformacions fetes a la taula, en l'ordre en què es van fer. És la llibreta de la paret de la cuina.

Mira-la ara. Ja té tres línies i encara no has fet res:

Aquesta tercera línia fa sospitar. A Tipus i formats la regla era: quan converteixes, declares el format, mai deixes que l'eina ho endevini. Power Query sí que ho endevina, i ho fa a partir de les primeres 200 files del fitxer. Mira la icona petita a l'esquerra del nom de cada columna: mostra el tipus que s'ha triat. En un moment corregiràs dues d'aquestes endevinalles.

Mira abans de netejar¶

A Qualitat de les dades vas auditar la taula amb consultes COUNT abans d'arreglar res. Power Query té el mateix costum integrat.

A la pestanya View, marca Column quality i Column distribution. Sota cada nom de columna apareix una barra petita, amb el percentatge de valors Valid, Error i Empty.

Ara mira metascore. En SQL vas trobar que 23,838 de les 26,688 files no tenen Metascore: el 89%. Power Query hi està d'acord?

Abans de fiar-te del que llegeixes, mira la cantonada inferior esquerra de la finestra. Diu Column profiling based on top 1000 rows. Fes clic en aquest missatge i canvia a Column profiling based on entire data set.

Hi ha una segona sorpresa, i és la idea més important d'aquesta pàgina. En aquest CSV, "buit" no és buit. Són les dues lletres NA:

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

Quan vas pujar el fitxer a Superset, la pujada va convertir cada NA en un NULL de veritat, i mai no vas veure les lletres. Power Query no ho suposa. Per a Power Query, NA és un tros de text com qualsevol altre fins que no li diguis el contrari. Dir-li-ho és el teu primer pas.

La mateixa neteja, dues vegades¶

Sis passos. Per a cadascun tens els clics a Power Query, el bessó SQL sobre la taula games i un nombre que prova que tots dos han fet el mateix. Després de cada clic, mira Applied Steps: ha aparegut una línia nova.

Pas 1 — NA vol dir buit. Selecciona la columna price. Transform → Replace Values: Value to find NA, Replace with null.

No hi ha res a escriure en SQL: la pujada ja ho va fer. Compta el resultat:

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

Resposta: 3,095 preus són buits.

Pas 2 — price és un nombre, escrit a l'americana. Clic dret a la capçalera de price → Change Type → Using Locale... → Data type: Decimal Number, Locale: English (United States).

En SQL, price ja és un nombre. Però pregunta't qui va decidir que 9.99 usa un punt per als decimals. La secció següent tracta d'aquesta pregunta.

Pas 3 — is_free, i després omplir. Dos moviments, en aquest ordre.

Primer, Add Column → Conditional Column: nom de la columna nova is_free; si price equals null llavors true, si no false.

Després, Replace Values sobre 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;

Resposta: 3,095 files marcades com a gratuïtes. El mateix nombre que al pas 1, com ha de ser.

Pas 4 — owners esdevé un nombre. La columna conté text com 10,000,000 .. 20,000,000. Selecciona-la i després:

• Transform → Extract → Text Before Delimiter, delimitador ..
• Replace Values: , → res
• Replace Values un altre cop, amb Advanced options → Replace using special characters → Non-breaking Space → res
• Canvia el tipus a Whole Number

El tercer punt és l'espai invisible amb què vas lluitar a Unitats i duplicats: sembla un espai i no ho és.

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

Resposta: 100,000,000. Aquest és Dota 2.

Pas 5 — release_date esdevé una data. Clic dret a la capçalera → Change Type → Using Locale... → Date, English (United States). Després mira Column quality: hi ha algun Error?

En SQL això era el CASE amb dues crides a TO_DATE. Compta quantes files encaixen amb cada 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;

Resposta: 26,646 dates completes com Nov 16, 2004, i 41 amb només un mes, com Jun 2009. Això deixa exactament una fila que no encaixa en cap: 8 Oct, 2014. Esbrina què fa Power Query amb aquestes 42 files estranyes. Faci el que faci, Column quality t'ho dirà.

Pas 6 — una franja i un filtre. Add Column → Conditional Column: nom price_band, amb una branca per franja (Free, Under 5, 5 to 15, 15 to 30, 30 or more). Després fes clic a la fletxa del filtre de la capçalera de game i desmarca NA.

En SQL: el CASE WHEN de Categorització i WHERE game IS NOT NULL.

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

Resposta: 26,685 files. Els tres jocs sense nom han desaparegut.

Per acabar, prem Home → Close & Apply. Fins ara treballaves sobre una previsualització. Ara els passos s'executen sobre el fitxer sencer i la taula arriba a Power BI.

La teva llista d'Applied Steps ara es llegeix així (els passos repetits reben un número):

L'ordre és la lògica¶

Torna al pas 3 i intercanvia'n els dos moviments. Primer substitueix null per 0, després afegeix is_free.

Què passa? Tots els jocs són "no gratuïts". Quan la columna condicional busca null, ja no en queda cap: has sobreescrit l'evidència.

En una recepta enregistrada, l'ordre de les línies és la lògica. SQL té la mateixa regla amb una altra forma: a games_clean.sql, is_free llegeix el price original, no el que s'ha omplert.

El mateix raonament explica el pas 1. Si te'l saltes, cap preu no és mai null, i per tant is_free també és false a tot arreu.

Un nombre no és un nombre: la configuració regional¶

Una configuració regional (locale) és el conjunt de convencions regionals que un ordinador usa per llegir i escriure valors: quin caràcter marca els decimals, en quin ordre s'escriu una data, com es diuen els mesos.

Aquí tens una frase que sona òbviament certa: "9.99 és 9.99 a tots els ordinadors." És falsa quan el nombre arriba com a text, que és el que és un CSV.

Power Query Desktop llegeix el text amb el format regional del teu Windows. En un PC configurat en castellà, el text 9.99 pot convertir-se en 999 o en un error. Res no t'avisa. La columna simplement s'omple de nombres equivocats.

Using Locale li diu a la cuina de quin país ve l'ingredient. Aquest fitxer es va escriure als Estats Units, així que les dates tenen l'aspecte Nov 16, 2004 i els decimals usen un punt. Per això els passos 2 i 5 usen Change Type → Using Locale... i no un simple Change Type.

Rebobina i llegeix el codi¶

De tornada a l'editor (Home → Transform data), fes clic en una línia anterior d'Applied Steps. La previsualització viatja enrere fins a aquell moment: la taula tal com era després d'aquell pas. Fes clic a l'última línia i tornes a ser a l'ara. La creu al costat d'un pas l'elimina. Aquest és el teu desfés.

Ara obre View → Advanced Editor. Apareix una finestra plena de codi.

M és el llenguatge en què Power Query escriu els teus passos. Cada clic que has fet hi ha afegit una línia de M.

La cuina escrivia la recepta tota l'estona. Fer clic no ha substituït el codi. T'ha escrit el codi.

Power Query o SQL?¶

Imagina que el teu cap t'envia un CSV nou cada dilluns, amb les mateixes columnes. Quina eina tries, i què fas cada dilluns?

Qualsevol de les dues. A Power Query prems Refresh i cada pas s'executa de nou sobre el fitxer nou. A SQL tornes a executar l'script. El que importa no és clics contra codi. És que la recepta existeixi i es pugui repetir.

L'única resposta equivocada és la tercera cuina: obrir el CSV en un full de càlcul i arreglar cel·les a mà. No deixa cap recepta. El dilluns següent comences de zero, i ningú no pot comprovar què vas fer.

Idees clau¶

• Power Query és la part de preparació de dades de Power BI. Treballes al Power Query Editor, que s'obre amb Transform Data.
• Applied Steps registra cada transformació, per ordre. És la recepta, i al darrere hi ha codi en un llenguatge anomenat M.
• Cada clic té un bessó SQL: Replace Values ↔ COALESCE, Conditional Column ↔ CASE WHEN, Text Before Delimiter ↔ SPLIT_PART, Filtered Rows ↔ WHERE.
• L'ordre dels passos és la lògica: marca els preus buits abans d'omplir-los, o la marca és false a totes les files.
• Un nombre o una data escrits com a text tenen una configuració regional (locale). Usa Change Type → Using Locale... per dir d'on ve el fitxer.
• L'anàlisi de perfil només llegeix les primeres 1,000 files si no canvies a tot el conjunt de dades, i NA és text fins que el substitueixes per null.
• La pregunta no és clics o codi. La pregunta és si la recepta existeix i es pot repetir.

Comprovació ràpida¶

La taula és neta, a dues cuines. Encara ningú no li ha preguntat res. Ara toca Estadística exploratòria, on games_clean per fi respon una pregunta senzilla amb una resposta sorprenent: quant costa un joc?