En la entrada anterior hablamos de los posibles orígenes de datos. Una vez que hemos terminado la selección de los datos que queremos obtener del origen, siempre llegamos a un punto común, la ventana ‘Query Editor’ que es la que nos permite hacer las transformaciones Power Query que estimemos necesarias a partir de los datos seleccionados.
Introducción a las Transformaciones Power Query
Veamos a continuación una imagen completa de esta ventana con el resultado de los datos obtenidos en el ejemplo de los amigos de la cuenta de Facebook:
Como hemos comentado anteriormente, existe una gran variedad de transformaciones Power Query que nos permiten ir convirtiendo los datos y haciendo los cálculos necesarios para realizar la integración entre diversas fuentes y obtener la estructura que hemos diseñado como destino aplicando las técnicas de modelado dimensional.
Vamos a pasar a continuación a conocer todas las transformaciones power query disponibles, haciendo un estudio de los tres elementos de menús disponibles en la “cinta” (Ribbon), que son: ‘Home’, ‘Transform’, ‘Add Column’ y ‘View’. Al tratarse de bastante información dividiremos los contenidos en dos entregas. En esta primer entrega veremos las opciones de Home y Transform dejando las restantes para una segunda entrada.
Menús en las Transformaciones Power Query
Menu ‘Home’
Grupo de opciones ‘Query’: son opciones relativas a la consulta al origen de datos.
- Close & Load: permite cerrar el proceso de extracción y transformaciones y proceder a la carga de los datos en el destino. Se verá con detalle más adelante, cuando se estudien los destinos disponibles.
- Refresh Preview: actualiza los datos del modelo. Elimina totalmente el contenido del modelo y lo vuelve a extraer del origen, aplicar los pasos con las transformaciones definidas y cargar en el destino.
- Properties: permite modificar el nombre y la descripción de la consulta actual.
Grupo de opciones ‘Reduce columns’: permiten quitar columnas que no necesitamos.
- Choose Columns: permite seleccionar las columnas que queremos tener disponibles y eliminar las demás. Se pueden marcar o desmarcar cualquiera de las columnas de la consulta, quedando disponibles sólo las que dejemos marcadas.
- Remove Columns: permite eliminar columnas de la tabla.
Grupo de opciones ‘Reduce Rows’: permiten eliminar filas que no necesitamos.
- Keep Top Rows: permiten mantener un conjunto de filas, bien del principio, bien del final, bien un rango, eliminando las que queden fuera de la selección.
- Remove Top Rows: permiten eliminar un conjunto de filas, bien del principio, bien del final, bien un rango, manteniendo las que queden fuera de la selección. Es la operación contraria a la anterior.
- Remove Duplicates: elimina filas duplicadas, basándose en las columnas seleccionadas en ese momento.
- Remove Errors: permite eliminar o mantener las filas que contengan errores.
Grupo de opciones ‘Sort’: permiten realizar ordenaciones de los datos.
- A-Z: ordenación ascendente de las filas en base a la columna o columnas seleccionadas.
- Z-A: ordenación descendente de las filas en base a la columna o columnas seleccionadas.
Grupo de opciones ‘Transform’: permiten realizar transformaciones de datos.
- Split Column: podemos dividir una columna en varias, bien por un delimitador, bien por una longitud fija. Por ejemplo, si tenemos una columna con el nombre y apellidos de los clientes, con las filas almacenadas de la siguiente forma: ‘apellido1 apellido2, nombre’, podríamos dividirla en dos columnas, una con el nombre y otra con los apellidos, indicando que el delimitador es la coma.
- Group By: podemos agrupar conjuntos de filas y aplicar funciones de agregación sobre ellos (suma, promedio, contar, mínimo, máximo, etc.). Por ejemplo, a partir de la tabla de clientes, podríamos agrupar por provincia y obtener una fila por cada provincia con el número de clientes que tenemos en ella.
- Data Type: permite hacer conversiones de tipos de datos.
- Use First Row AS Headers: indica si la primera fila obtenida del origen son datos o son los encabezados de columnas. Si se pulsa, considera la primera fila como encabezados de columnas. Se suele usar en archivos de texto.
- Replace Values: es como el buscar y reemplazar de Excel, indicamos el valor a buscar y el valor por el que lo queremos reemplazar.
Grupo de opciones ‘Combine’: permiten realizar combinaciones de tablas con datos.
Merge Queries: la operación Merge implica unir columnas de dos tablas que tienen valores en común por otra columna, habitualmente se unen por las claves. El número de filas nunca será superior al número de filas de la tabla que más filas tenga. Esta imagen le será de ayuda para entender la operación:
- Append Queries: la operación Append implica unir dos tablas con la misma estructura en una sola, obteniendo una nueva tabla con las mismas columnas y con el total de filas de ambas tablas. Por ejemplo, si tenemos una tabla con las ventas de enero y otra con las ventas de febrero, podemos obtener una sola tabla con las ventas de enero y febrero. Esta imagen le será de ayuda para entender la operación:
Menu ‘Transform’
Grupo de opciones ‘Table’: permiten realizar operaciones sobre el conjunto de la tabla. Algunas opciones también están en otros menús y se repiten aquí por usabilidad, evitando, en la medida de lo posible, que tengamos que estar cambiando de menús.
- Group By: podemos agrupar conjuntos de filas y aplicar funciones de agregación sobre ellos (suma, promedio, contar, mínimo, máximo, etc.). Por ejemplo, a partir de la tabla de clientes, podríamos agrupar por provincia y obtener una fila por cada provincia con el número de clientes que tenemos en ella. Está también en el menú ‘Home’.
- Use First Row AS Headers: indica si la primera fila obtenida del origen son datos o son los encabezados de columnas. Si se pulsa, considera la primera fila como encabezados de columnas. Se suele usar en archivos de texto. Está también en el menú ‘Home’.
- Transpose: pasa la información de filas a columnas y viceversa. Tenga en cuenta que puede devolver un gran número de columnas si hay muchas filas, y que además perdería los nombres de columnas originales, quedando nombradas como Column1 a ColumnN.
- Reverse Rows: invierte el orden de las filas. Aunque existe esta opción, es recomendable utilizar las opciones de ordenación y conseguir con ellas la ordenación más adecuada.
- Count Rows: elimina las filas de la tabla y devuelve el número de filas de dicha tabla. Su única utilidad es almacenar el número de filas resultante, sin ningún otro dato adicional.
Grupo de opciones ‘Any Column’: permiten realizar operaciones sobre cualquier columna disponible en ese punto del proceso de transformación.
- Data Type: permite cambiar el tipo de datos de una columna. Está también en el menú ‘Home’.
- Replace Values: es como el buscar y reemplazar de Excel, indicamos el valor a buscar y el valor por el que lo queremos reemplazar. Está también en el menú ‘Home’.
- Replace Errors: reemplaza los errores por el valor que le indiquemos.
- Fill: permite rellenar valores nulos (Null) aplicando el valor de la fila anterior (Fill Down) o el de la fila posterior (Fill Up)
- Pivot Column: permite pivotar columnas de una tabla.
- Unpivot Columns: permite despivotar columnas de una tabla. Las operaciones Pivot y Unpivot cambian filas por columnas y viceversa, funciona de la misma forma que la operación ‘Transponer’ de Excel.
- Expand: equivale a pulsar el botón estudiado anteriormente y utilizado en uno de los ejemplos.
- Aggregate: sólo está activo en las columnas que contengan el botón (tablas relacionadas) y permite obtener datos agregados de las columnas numéricas que contienen. Por ejemplo, en la tabla ‘Tienda’ podemos obtener la suma de unidades de cada tienda desde la tabla .
- Move: permite mover las columnas hacia la izquierda o derecha. Es igual que si pinchamos sobre una columna y la arrastramos a la posición deseada, resultando esta segunda opción más cómoda.
Grupo de opciones ‘Text Column’: permiten realizar operaciones específicas sobre las columnas que contienen texto (cadenas de caracteres).
- Split Column: podemos dividir una columna en varias, bien por un delimitador, bien por una longitud fija. Por ejemplo, si tenemos una columna con el nombre y apellidos de los clientes, con las filas almacenadas de la siguiente forma: ‘apellido1 apellido2, nombre’, podríamos dividirla en dos columnas, una con el nombre y otra con los apellidos, indicando que el delimitador es la coma. Está también en el menú ‘Home’.
- Format: permite, sobre la columna elegida: convertirla a mayúsculas o minúsculas, poner la primera letra de cada palabra en mayúsculas, eliminar espacios por la derecha e izquierda, y eliminar caracteres no imprimibles.
- Merge Columns: concatena las columnas seleccionadas en una sola columna, pudiendo además incluir un carácter separador entre el texto de cada columna. Por ejemplo si tenemos una columna con el nombre y otra con los apellidos y queremos conseguir el formato ‘apellido1 apellido2, nombre’ lo podríamos hacer con esta opción. Es la acción opuesta a ‘Split Column’.
- Length: permite obtener la longitud (número de caracteres) de la columna seleccionada.
- Parse: permite transformar una parte de texto en XML o JSON. Es una opción orientada a técnicos, no suele ser utilizada por usuarios.
Grupo de opciones ‘Number Column’: permiten realizar operaciones específicas sobre las columnas que contienen valores numéricos.
- Statistics: elimina las filas de la tabla y devuelve el resultado de la función utilizada (suma, mínimo, máximo, promedio, desviación standard, cuenta valores o cuenta valores distintos) para las filas de dicha tabla en base a la columna seleccionada. Su única utilidad es almacenar el dato resultante, sin ningún otro dato adicional.
- Standard: permite hacer una operación ‘standard’ (suma, resta, multiplicación, división, división entera y resto de la división) que afectará a todas las filas de la tabla, para la columna seleccionada. Por ejemplo nos valdría para incrementar el precio un 10%, para ello seleccionamos la columna ‘precio’, utilizamos la función standard ‘Multiply’ y como valor ponemos 1,10.
- Scientific: permite hacer una operación ‘scientific’ (valor absoluto, potencia, exponencial, raíz cuadrada, logaritmo, factorial) que afectará a todas las filas de la tabla, para la columna seleccionada.
- Trigonometry: permite hacer una operación trigonométrica (seno, coseno, tangente, arcoseno, arcocoseno, arcotangente) que afecta a todas las filas de la tabla, para la columna seleccionada.
- Rounding: permite hacer redondeos que afectarán a todas las filas de la tabla, para la columna seleccionada.
- Information: permite saber si el valor es par, impar, positivo, cero o negativo:
- Is Even: devuelve TRUE si el valor es par, FALSE en otro caso.
- Is Odd: devuelve TRUE si el valor es impar, FALSE en otro caso.
- Sign: devuelve 0 si el valor es 0, 1 si es positivo, -1 si es negativo.
Grupo de opciones ‘From Date & Time’: permiten realizar operaciones específicas sobre las columnas que contienen fechas y horas.
- Date: permite aplicar diferentes funciones de fecha sobre una columna tipo ‘Date’. Por ejemplo: año, mes, día, último día del mes, etc.
- Time: permite aplicar diferentes funciones de hora sobre una columna tipo ‘Date/Time’ o ‘Time’.
- Duration: permite calcular intervalos transcurridos, sólo aplica a columnas de tipo ‘Duration’.
Hasta aquí la entrada de hoy sobre transformaciones Power Query, para la siguiente veremos los menús restantes de las opciones de transformación, los menús Add Column y View, esperamos que esta serie esté siendo de utilidad y que podáis sacarle el máximo provecho.
Si os gustan nuestros posts, desde SolidQ os invitamos a visitar nuestro blog o a suscribiros a nuestra newsletter para recibir las últimas novedades del sector en vuestro correo 🙂
