Power BI Desktop: tabla pivotada a tabla tabulada
Las aplicaciones de empresa o bien institucionales nos permiten introducir los datos derivados de las diferentes gestiones y operativa cotidiana a través de pantallas y de formularios comprensibles por parte del usuario que las maneja. Somos nosotros los usuarios con nuestras interacciones en dichos programas, o en las webs, o en redes sociales, o quizás en aplicaciones de móvil quienes alimentamos las bases de datos. Pero esos datos se almacenan en las bases de datos de forma estructurada y bien organizada de acuerdo con la lógica de quienes analizaron (analistas de aplicaciones) y programaron (programadores de aplicaciones) dichas aplicaciones.
No siempre la forma en la que los entornos y programas de empresa devuelven la información gestada es en forma de tabla tabulada que es la forma con la que habitualmente procesamos las tablas. En estos casos va a ser precisa una importante transformación de la organización de los datos y Power Query nos facilita las herramientas necesarias para ello.
Tabla tabulada
Es una estructura ordenada de datos en la forma tradicional y normalizada en la que una tabla tiene forma de columnas (campos) y filas (registros) y en donde la primera fila está conformada, no por datos sino por los nombres o etiquetas de los campos de dicha tabla.
Por ejemplo, la exportación de una base de datos SQL devuelve la información con una disposición a modo de tabla de referencias cruzadas que nos recuerda a las tablas dinámicas, a las tablas pivotadas (pivot tables).
Power Query nos puede ayudar a reorganizar de forma sencilla, a través de varios pasos de transformación, esas tablas y convertirlas en pocos pasos, de tablas pivotadas en tablas tabuladas.
Veamos la forma de una tabla en la que se almacenan los datos sobre las ventas en unas localidades de determinados departamentos a lo largo de los años:
Tenemos por un lado las localidades, por otro los departamentos, por otro los años y por otro las cifras de las ventas.
Esta es sin duda una buena forma de presentar la información, es una tabla cuya información se "busca" al cruce, es una tabla pivotada, es una tabla de referencias cruzadas.
Pero esa información no está normalizada. No tenemos un campo llamado Localidad, los campos (columnas) Telefonía, Sonido e Imagen se repiten en la tabla...
La forma habitual de organizar la información sería que por cada venta de cada año de cada localidad de cada servicio de un determinado importe, se presentara en una fila. Bajo estas consignas la estructura a conseguir debería ser similar a esta (por ejemplo para las ventas de Santander en el año 2007):
Ejemplo. Editor de consultas
Para asimilar bien este tipo de transformación más avanzada, para esta práctica vamos a trabajar sobre el archivo descargado llamado ventas_ciudades_en_matriz.xlsx.
Lo que en Excel se denomina Power Query, en Power BI Desktop (PBID) se llama indistintamente también Editor de consultas, y al editor de consultas para efectuar transformaciones en los datos se accede desde la Ficha Inicio - Transformar datos (antes llamado editar consultas).
En este caso y para conseguir pasar de una tabla pivotada a una tabla tabulada, desde el editor de consultas (Power Query) deberemos utilizar las herramientas siguientes:
- Transponer
- Usar la primera fila como encabezado
- Rellenar hacia abajo
- Anular dinamización de columnas (previo seleccionar las columnas a despivotar)
- ... Y quizás alguna acción más, como cambiar el nombre de las columnas, cambiar el tipo de datos para alguna de ellas (por ejemplo para cambiarla de tipo texto a tipo numérico -o al revés-...)
Cuando disponemos de un archivo Excel producto de haber sido exportado (por ejemplo) desde un programa ERP de empresa, al abrirlo desde Excel observamos con frustración que no está tabulado, que su información se encuentra organizada al estilo de una tabla pivotada, como una tabla dinámica por decirlo así.
Pero con Power BI Desktop, no tenemos problema:
Lo primero que haremos desde Power BI Desktop es, desde la ficha inicio - Obtener datos - Libro de Excel - y tras elegir seleccionando, el nombre de la hoja que contiene la tabla (Hoja1), conexionarla, pero con el fin de transformarla es decir:
- O bien la Cargamos en Power BI Desktop para ver cómo queda conexionada e inicialmente sin transformar.
- O bien directamente pulsamos sobre el botón Transformar datos para pasar directamente a Power Query.
A continuación entraremos en Power Query como "taller" desde donde poder editar la consulta que nos permite transformar la tabla de pivotada en tabla tabulada. De estar en PBID, esto lo realizaremos mediante el botón de la Ficha Inicio - Transformar datos.
ESTE ES EL DETALLE IMPORTANTE QUE SEGÚN VERSIONES (o si lo hagamos desde Excel o PBID),TENEMOS QUE REALIZAR, pero siempre razonando:
En caso de observar que parte de lo que son realmente datos (poblaciones) aparecen en Power Query en la primera fila de cabecera "entremezclados" con los títulos de columna column1, column2..., ya en Power Query, eliminaremos de la lista de pasos aplicados, los pasos de Tipo cambiado y el de Encabezados promovidos.
Estos pasos los agrega automáticamente Power Query (según la versión con la que trabajemos - Excel o PBID-) y NO nos interesan, es más, nos perjudican. Por este motivo, los eliminaremos.
El primer paso es TRANSPONER, desde la ficha Transformar - En el primer grupo de botones Tabla.
A continuación, en la propia ficha Transformar elegiremos el botón de Usar la primera fila como encabezado.
Los años se promocionan a la fila de cabecera.
Después, seleccionaremos la primera columna con las localidades y los valores de null, y extenderemos los contenidos (las localidades) a sus celdas de debajo que están en null mediante la Ficha Transformar - Rellenar hacia abajo (es un botón con un cuadro y una flecha hacia abajo).
Se rellenan y extienden los nombres de las ciudades, debajo de los que teníamos.
A continuación seleccionaremos las columnas a despivotar (las de los años), y ayudados de la Ficha Transformar - Grupo de botones Cualquier columna - botón de Anular dinamización de columnas.
O mejor aún, seleccionaremos las que no son los años (las dos primeras columnas) y anularemos la dinamización de otras columnas, es decir, de "las otras".
Esta segunda forma de hacerlo es más interesante dado que en el caso de que el año que viene, tengamos este archivo con este nombre y con las ventas en forma de tabla pivotada, aunque tengamos ventas de uno o varios años más, siempre nos anulará la dinamización de todos los años que tengamos (tengamos los que tengamos), no solo del rango de columnas (años), que en el momento de crear los pasos de transformación, tengamos en ese momento. Sería una forma más general y efectiva de hacerlo.
Finalmente cambiaremos el nombre de las columnas a como deseemos que queden definitivamente (Ciudad - Servicio - Año - Importe), cambiaremos el tipo de datos quizás para alguna columna (Año a número entero, e Importe a número decimal fijo) desde el icono izquierdo en la propia zona de cabecera...
Finalmente Archivo - Cerrar y aplicar.
Link interesante sobre transformaciones y combinaciones de datos:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-shape-and-combine-data/