Preparar los datos en Power Query

Realizar transformaciones comunes en el editor de Power Query

El acceso y la preparación de los datos se consideran, con razón, pasos clave para crear un buen informe, especialmente cuando se conecta a archivos personales o sitios web. Este tipo de fuente se denomina «no estructurada» y se opone a los datos estructurados como es el caso de las bases de datos.

La importancia del trabajo que requiere este paso (en términos de esfuerzo y tiempo) no debe subestimarse de ninguna manera.

La preparación de datos implica, por ejemplo:

Consiste en preparar la consulta que se envía a la fuente de datos al actualizar el informe, a la fuente u origen de datos. Por lo tanto...

Limpiar los datos

Ahora veamos en detalle las diversas operaciones comunes de limpieza de datos. Dependiendo del tipo de origen y su calidad, se le pedirá que realice una o más de estas operaciones.

Tres operaciones son muy comunes:

Usar la fila de encabezado

En la mayoría de los casos, Power BI es capaz de identificar correctamente la fila de encabezados cuando hay una.

Al consultar archivos de Excel en particular, y en ausencia de un identificador de fila, puede ser necesario «ayudar» al software: este es especialmente el caso cuando la tabla contiene solo datos de texto.

A continuación, se muestra un ejemplo de esta operación:

No se han identificado los encabezados de columna

Una vez aplicada la transformación, los encabezados se revierten

Cambiar el nombre de una columna

Según el tipo de fuente que consulte, el nombre de las columnas puede ser más o menos descriptivo. Este nombre será el que identifique los datos cuando cree los visuales...

Filtrar

Filtrar es un término general para designar el descarte de ciertas columnas que no serán útiles para el informe, o la reducción del número de filas en una tabla. Ambas operaciones aceleran la carga de archivos y la ejecución de Power BI.

Eliminar columnas

Al eliminar una columna, solo se trabaja en la copia de los datos almacenados en el archivo de Power BI. En ningún caso se eliminan los datos de del origen.

Puede encontrar la herramienta de administración de columnas en el grupo Administrar columnas en la pestaña Inicio:

Esta herramienta le permite seleccionar o deseleccionar rápidamente las columnas que se van a conservar, utilizando casillas de verificación.

También puede proceder columna por columna, haciendo clic con el botón derecho en el encabezado de estas.

Con cada acción que realice en Power Query, se crea un nuevo paso (por ejemplo, columnas eliminadas) en el panel Configuración de la consulta. La cruz a su izquierda le permite cancelar la operación. Cuando aparece una rueda dentada a la derecha...

Añadir columnas

La columna añadida, ya sea calculada por el origen de datos o calculada por el lenguaje M de Power BI, se almacena físicamente en el modelo tabular, es decir, ocupa espacio y aumenta el tamaño del archivo. Se vuelve a calcular para cada fila de la tabla cuando se actualizan los datos. En Power BI, esta noción tiene un nombre muy específico, el contexto de fila, cuya importancia veremos cuando hablemos de fórmulas en lenguaje DAX.

Crear una columna a partir de ejemplos

La herramienta Columna a partir de los ejemplos de la pestaña Agregar columna es una de esas herramientas «mágicas» de Power BI para agregar una columna: una vez seleccionada la columna o las columnas originales, se indica en la nueva columna lo que se desea ver. A continuación, Power BI «adivina» el tipo de transformación que desea realizar y deduce la fórmula.

Crearemos una nueva columna...

Otros dos ejemplos de transformación

Además de las transformaciones del contenido de la columna, algunas de las cuales hemos visto en la sección anterior, hay dos operaciones comunes que permiten trabajar en la estructura de los datos.

La primera consiste en usar una tabla dinámica como origen y restaurar la estructura de columnas que Power BI necesita (es decir, «descruzar» o anular la dinamización de la tabla). La segunda consiste en agregar filas de una o más tablas a las filas de otra tabla con la misma estructura. Por ejemplo, si una hoja contiene datos del mes de enero, la siguiente hoja contiene datos del mes de febrero, y así sucesivamente, es posible que desee agrupar esos datos en una sola tabla, especialmente para poder compararlos.

Quitar la dinamización de una tabla dinámica

La estructura en forma de tabla es necesaria para Power BI: por tabla, debemos entender una estructura basada en columnas que identifican campos distintos, cada fila de los cuales es un valor.

Por esta razón, el uso de datos de una tabla dinámica (o TD) no es directamente posible para Power BI: se requiere una operación inicial, que le permitirá reestructurar los datos cruzados en una tabla de columnas, sin perder ninguna información.

El archivo Tabla dinámica.xlsx es un buen ejemplo. Se ha creado una tabla dinámica, que muestra los importes de las ventas por trimestre (filas) y año (columnas):

Para usar estos datos en Power BI, primero debe reestructurar esta tabla en columnas (Año, Trimestre, Ventas) y generar una tabla de 16 filas (4 años de 4 trimestres).

Las herramientas de administración de consultas

La mayoría de las operaciones descritas en esta sección tienen lugar en el panel Consultas, en el lado izquierdo de la pantalla del editor de Power Query. Se trata de trabajar sobre las características de la consulta o d a propio origen.

Agregar un nuevo origen

En un archivo, siempre es posible agregar nuevos datos. Pueden provenir de la base de datos o del archivo que ya está en uso, o de cualquier otra fuente.

Para agregar un nuevo origen, use el botón Orígenes recientes de la pestaña Inicio - grupo Datos de Power BI o de Power Query y siga el proceso habitual. La configuración de la relación, si se requiere, se realizará en otra pantalla, la vista Modelo, en Power BI.

Cambiar el nombre, duplicar o eliminar una consulta

Las operaciones comunes de administración de consultas están disponibles haciendo clic con el botón derecho en la consulta, en el panel Consultas de Power Query.

Agregar la fecha de actualización

Generar la fecha de actualización significa poder mostrar, en el informe, la fecha y hora exactas en que se actualizaron los datos (en el informe). Por lo tanto, es un concepto que puede resultar crucial.

Curiosamente, no hay ningún «botón» listo para usar que inserte este valor.

Pero esta es una oportunidad para ver un primer ejemplo de código M.

Encontrará, con los archivos fuente, un archivo Actualización.txt: abra este archivo y copie todo el código que contiene:

let  
    MadridTime = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 1), 
// UTC+1  
    ColumnNames = {"Fecha de Actualización"},  
    ColumnValues = {{MadridTime}},  
    ConvertToTable = Table.FromColumns(ColumnValues, ColumnNames),  
    #"Tipo Modificado" = Table.TransformColumnTypes(ConvertToTable, 
{{"Fecha de Actualización", type datetime}})  
in  
    #"Tipo Modificado" 

Crearemos una consulta a partir de una página en blanco y pegaremos el código.

Para ello: