Power Query¶

Ejercicio power-query-annex.pdf · PDF

Test rápido, sin mirar. En la lección anterior ejecutaste un script y apareció games_clean. ¿Qué le hizo ese script a price? ¿Y a owners?

Si has tenido que pensarlo, bien. Ahora la pregunta de verdad: ¿cómo lo averiguarías? Abres el archivo .sql y lo lees. Cada decisión está escrita ahí, en orden. Ese archivo es la receta de tu limpieza.

Aquí viene el problema. La mayoría de la gente que limpia datos en el trabajo no escribe nunca SQL. Hace clics. Así que hoy limpias el mismo archivo haciendo clics, en Power BI, y te haces una sola pregunta todo el rato: cuando haces clic, ¿adónde va la receta?

Una cocina que escribe la receta¶

Power Query es la parte de Power BI que prepara los datos antes de usarlos: se conecta a una fuente y después limpia y reorganiza la tabla paso a paso.

Conociste Power BI en Obtener datos con Power BI. Allí pulsaste Load y cogiste los datos tal como venían. Power Query es la sala que te saltaste: la que queda entre la fuente y el informe.

Piensa en dos cocinas.

En la cocina SQL escribes primero toda la receta y luego la cocina la cocina. Eso fue games_clean.sql.

En la cocina Power Query simplemente cocinas. Pero hay un cuaderno en la pared que apunta cada movimiento que haces, una línea por movimiento. Cuando terminas, la receta está ahí, y no la has escrito tú.

Mismo plato. Y en las dos cocinas el ingrediente en bruto, el archivo CSV, no se toca nunca.

flowchart LR
    CSV["<b>video_games.csv</b><br>el ingrediente en bruto:<br>nunca se modifica"]
    subgraph PQ ["Power Query (la cocina que escribe la receta)"]
        direction LR
        S1["Source"] --> S2["Promoted Headers"] --> S3["Changed Type"] --> S4["tus pasos..."]
    end
    CSV --> S1
    S4 --> M["<b>Tabla en Power BI</b><br>el plato: limpio,<br>listo para los gráficos"]

El Power Query Editor¶

Abre Power BI Desktop y ve a Home → Get data → Text/CSV. Elige video_games.csv (descárgalo de la página de Recursos). En la ventana de vista previa pulsa Transform Data, no Load. Se abre una segunda ventana.

El Power Query Editor es la ventana donde ves una vista previa de tu tabla y le aplicas transformaciones.

Applied Steps es la lista de todas las transformaciones hechas a la tabla, en el orden en que se hicieron. Es el cuaderno de la pared de la cocina.

Míralo ahora. Ya tiene tres líneas y todavía no has hecho nada:

Esa tercera línea merece sospecha. En Tipos y formatos la regla era: cuando conviertes, declaras el formato, nunca dejas que la herramienta adivine. Power Query sí adivina, y adivina a partir de las primeras 200 filas del archivo. Mira el pequeño icono a la izquierda del nombre de cada columna: muestra el tipo que se ha elegido. Dentro de un momento corregirás dos de esas suposiciones.

Mira antes de limpiar¶

En Calidad de los datos auditaste la tabla con consultas COUNT antes de arreglar nada. Power Query tiene ese mismo hábito integrado.

En la pestaña View, marca Column quality y Column distribution. Bajo el nombre de cada columna aparece una barrita con el porcentaje de valores Valid, Error y Empty.

Ahora mira metascore. En SQL descubriste que 23,838 de las 26,688 filas no tienen Metascore: el 89%. ¿Está de acuerdo Power Query?

Antes de fiarte de lo que lees, mira la esquina inferior izquierda de la ventana. Dice Column profiling based on top 1000 rows. Haz clic en ese mensaje y cambia a Column profiling based on entire data set.

Hay una segunda sorpresa, y es la idea más importante de esta página. En este CSV, "vacío" no está vacío. Son las dos letras NA:

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

Cuando subiste el archivo a Superset, la subida convirtió cada NA en un NULL de verdad, y nunca viste las letras. Power Query no asume eso. Para Power Query, NA es un trozo de texto como cualquier otro hasta que le dices lo contrario. Decírselo es tu primer paso.

La misma limpieza, dos veces¶

Seis pasos. Para cada uno tienes los clics en Power Query, el gemelo en SQL sobre la tabla games y un número que demuestra que ambos hicieron lo mismo. Después de cada clic, mira Applied Steps: ha aparecido una línea nueva.

Paso 1 — NA significa vacío. Selecciona la columna price. Transform → Replace Values: Value to find NA, Replace with null.

En SQL no hay nada que escribir: la subida ya lo hizo. Cuenta el resultado.

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

Respuesta: 3,095 precios están vacíos.

Paso 2 — price es un número, escrito a la americana. Clic derecho en la cabecera de price → Change Type → Using Locale... → Data type: Decimal Number, Locale: English (United States).

En SQL, price ya es un número. Pero pregúntate quién decidió que 9.99 usa un punto para los decimales. La siguiente sección trata de esa pregunta.

Paso 3 — is_free, y luego rellenar. Dos movimientos, en este orden.

Primero, Add Column → Conditional Column: nombre de la columna nueva is_free; si price equals null entonces true, si no false.

Después, Replace Values en 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;

Respuesta: 3,095 filas quedan marcadas como gratuitas. El mismo número que en el paso 1, como debe ser.

Paso 4 — owners se convierte en un número. La columna contiene texto como 10,000,000 .. 20,000,000. Selecciónala y luego:

• Transform → Extract → Text Before Delimiter, delimitador ..
• Replace Values: , → nada
• Replace Values otra vez, con Advanced options → Replace using special characters → Non-breaking Space → nada
• Cambia el tipo a Whole Number

El tercer punto es el espacio invisible contra el que luchaste en Unidades y duplicados: parece un espacio y no lo es.

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

Respuesta: 100,000,000. Ese es Dota 2.

Paso 5 — release_date se convierte en fecha. Clic derecho en la cabecera → Change Type → Using Locale... → Date, English (United States). Después mira Column quality: ¿hay algún Error?

En SQL esto era el CASE con dos llamadas a TO_DATE. Cuenta cuántas filas encajan en cada formato:

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;

Respuesta: 26,646 fechas completas como Nov 16, 2004, y 41 con solo el mes, como Jun 2009. Eso deja exactamente una fila que no encaja en ninguno: 8 Oct, 2014. Averigua qué hace Power Query con esas 42 filas raras. Haga lo que haga, Column quality te lo dirá.

Paso 6 — una banda y un filtro. Add Column → Conditional Column: nombre price_band, con una rama por banda (Free, Under 5, 5 to 15, 15 to 30, 30 or more). Después haz clic en la flecha del filtro en la cabecera de game y desmarca NA.

En SQL: el CASE WHEN de Categorización y WHERE game IS NOT NULL.

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

Respuesta: 26,685 filas. Los tres juegos sin nombre han desaparecido.

Para terminar, pulsa Home → Close & Apply. Hasta ahora trabajabas sobre una vista previa. Ahora los pasos se ejecutan sobre el archivo entero y la tabla aterriza en Power BI.

Tu lista de Applied Steps queda así (los pasos repetidos llevan un número):

El orden es la lógica¶

Vuelve al paso 3 e intercambia sus dos movimientos. Primero reemplaza null por 0, y después añade is_free.

¿Qué pasa? Todos los juegos son "no gratuitos". Cuando la columna condicional busca null, ya no queda ninguno: has sobrescrito la prueba.

En una receta grabada, el orden de las líneas es la lógica. SQL tiene la misma regla con otra forma: en games_clean.sql, is_free lee el price original, no el rellenado.

El mismo razonamiento explica el paso 1. Si te lo saltas, ningún precio es null, así que is_free también es false en todas partes.

Un número no es un número: el locale¶

Un locale (configuración regional) es el conjunto de convenciones regionales que un ordenador usa para leer y escribir valores: qué carácter marca los decimales, en qué orden se escribe una fecha, cómo se llaman los meses.

Aquí tienes una frase que parece obviamente cierta: "9.99 es 9.99 en todos los ordenadores". Es falsa cuando el número llega como texto, que es lo que es un CSV.

Power Query Desktop lee el texto usando el formato regional de tu Windows. En un PC configurado en español, el texto 9.99 puede convertirse en 999 o en un error. Nada te avisa. La columna simplemente se llena de números equivocados.

Using Locale le dice a la cocina de qué país viene el ingrediente. Este archivo se escribió en Estados Unidos, así que las fechas tienen el aspecto Nov 16, 2004 y los decimales usan punto. Por eso los pasos 2 y 5 usan Change Type → Using Locale... y no un simple Change Type.

Rebobinar y leer el código¶

De vuelta en el editor (Home → Transform data), haz clic en una línea temprana de Applied Steps. La vista previa viaja atrás hasta ese momento: la tabla tal como estaba después de ese paso. Haz clic en la última línea y vuelves al presente. La cruz junto a un paso lo elimina. Ese es tu deshacer.

Ahora abre View → Advanced Editor. Aparece una ventana llena de código.

M es el lenguaje con el que Power Query anota tus pasos. Cada clic que hiciste añadió una línea de M.

La cocina estuvo escribiendo la receta todo el tiempo. Hacer clic no sustituyó al código. Lo escribió por ti.

¿Power Query o SQL?¶

Imagina que tu jefe te manda un CSV nuevo cada lunes, con las mismas columnas. ¿Qué herramienta eliges y qué haces cada lunes?

Cualquiera de las dos. En Power Query pulsas Refresh y cada paso se ejecuta de nuevo sobre el archivo nuevo. En SQL vuelves a ejecutar el script. Lo que importa no es clics contra código. Es que la receta exista y se pueda repetir.

La única respuesta equivocada es la tercera cocina: abrir el CSV en una hoja de cálculo y arreglar celdas a mano. No deja ninguna receta. El próximo lunes empiezas de cero, y nadie puede comprobar lo que hiciste.

Ideas clave¶

• Power Query es la parte de preparación de datos de Power BI. Trabajas en el Power Query Editor, que se abre con Transform Data.
• Applied Steps registra cada transformación, en orden. Es la receta, y detrás hay código en un lenguaje llamado M.
• Cada clic tiene un gemelo en SQL: Replace Values ↔ COALESCE, Conditional Column ↔ CASE WHEN, Text Before Delimiter ↔ SPLIT_PART, Filtered Rows ↔ WHERE.
• El orden de los pasos es la lógica: marca los precios vacíos antes de rellenarlos, o la marca será false en todas las filas.
• Un número o una fecha escritos como texto tienen un locale. Usa Change Type → Using Locale... para indicar de dónde viene el archivo.
• El perfilado lee solo las primeras 1,000 filas salvo que cambies a todo el conjunto de datos, y NA es texto hasta que lo reemplazas por null.
• La pregunta no es clics o código. La pregunta es si la receta existe y se puede repetir.

Comprobación rápida¶

La tabla está limpia, en dos cocinas. Todavía nadie le ha preguntado nada. Siguiente: Estadística exploratoria, donde games_clean por fin responde una pregunta sencilla con una respuesta sorprendente: ¿cuánto cuesta un juego?