Meses atrás subí al blog una forma de convertir información representada en tablas de referencias cruzadas en una tabla 'estándar' sobre la que poder trabajar directamente... (ver)...
Pues el tema de hoy tiene bastante relación, pero de una forma infinitamente más potente.. empleando Power Query (complemento de Microsoft del que ya hemos hablado alguna vez en este mismo blog).
Aunque el nombre empleada en la herramienta Anular la dinamización de columnas no es muy descriptivo.. al menos en la versión en inglés sí es más gráfico 'Unpivot...', esto es, deshacer lo logrado con una tabla dinámica o similar; en definitiva, volver a tener una estructura de campos mínimo sobre los que trabajar.
Partiremos de nuestros datos en un libro de Excel llamado 'Origen_Tabla Ref Cruz.xlsx' con la siguiente estructura de datos:
Sobre estos datos queremos posteriormente reagruparlos en base a nuevos criterios o estructuras... por eso nuestro siguiente paso será emplear Power Query para tratar, filtrar o transformar la información tal cual existe ahora mismo en esta otra forma:
Desde esa nueva tabla construiremos el informe definitivo consolidado o según nuestras necesidades...
Empezamos entonces desde la ficha de Power Query > grupo Obtener datos externos > botón De Archivo > Desde Excel
Tras buscar el fichero Excel origen y seleccionarlo se abrirá una ventana diálogo mostrando las hojas del libro... seleccionaremos la que contiene la información válida:
Al hacer clic sobre la hoja se abrirá nuestro Editor de consultas de Power Query:
Emplearemos entonces algunas herramientas de Power Query para tratar y editar de alguna manera la información tal cual la tenemos ahora mismo.
Como primer paso seleccionaremos la Columna1 y desde la ficha Transformar > grupo Cualquier columna > botón Rellenar conseguiremos se efectúe el efecto de autorrellenado de las celdas vacías...
En segundo lugar utilizaremos la primera fila como cabecera, para que no nos aparezca Columna1, Columna2, etc... y sí Ene, Feb, mar, etc.
Así desde la ficha Inicio > grupo Transformar > botón Usar la primera fila como encabezado lo conseguimos:
Limpiaremos ahora los datos que no nos interesan... a saber: cuando en la segunda columna nos aparezca el dato vacía (Null) o con la palabra Total (ya que no nos interesa el dato subtotalizado). Para ello simplemente aplicaremos un filtro sobre el campo:
Estamos preparados para Anular la dinamización de columnas / Unpivot. Seleccionaremos las columnas de los meses y desde la ficha Transformar > grupo Cualquier columna > botón Anular la dinamización de columnas convertiremos nuestra tabla de referencias cruzadas en una tabla estándar, con los meses desplegados en un solo campo...
Por sencillez cambiaremos los nombres de los campos, para lo que basta hacer doble clic sobre ellos.
Acabaremos Cerrando y cargando para tener el dato en la hoja de cálculo como veíamos al inicio de la explicación...
Después bastaría construir nuestra Tabla dinámica sobre lo conseguido y trabajar agrupando elementos según necesitemos...
Una gran ventaja: Nuevos datos incorporados en el origen (si guardan una estructura similar) se incorporarán automáticamente a nuestro informe!!!.
Pues el tema de hoy tiene bastante relación, pero de una forma infinitamente más potente.. empleando Power Query (complemento de Microsoft del que ya hemos hablado alguna vez en este mismo blog).
Aunque el nombre empleada en la herramienta Anular la dinamización de columnas no es muy descriptivo.. al menos en la versión en inglés sí es más gráfico 'Unpivot...', esto es, deshacer lo logrado con una tabla dinámica o similar; en definitiva, volver a tener una estructura de campos mínimo sobre los que trabajar.
Partiremos de nuestros datos en un libro de Excel llamado 'Origen_Tabla Ref Cruz.xlsx' con la siguiente estructura de datos:
Sobre estos datos queremos posteriormente reagruparlos en base a nuevos criterios o estructuras... por eso nuestro siguiente paso será emplear Power Query para tratar, filtrar o transformar la información tal cual existe ahora mismo en esta otra forma:
Desde esa nueva tabla construiremos el informe definitivo consolidado o según nuestras necesidades...
Empezamos entonces desde la ficha de Power Query > grupo Obtener datos externos > botón De Archivo > Desde Excel
Tras buscar el fichero Excel origen y seleccionarlo se abrirá una ventana diálogo mostrando las hojas del libro... seleccionaremos la que contiene la información válida:
Al hacer clic sobre la hoja se abrirá nuestro Editor de consultas de Power Query:
Emplearemos entonces algunas herramientas de Power Query para tratar y editar de alguna manera la información tal cual la tenemos ahora mismo.
Como primer paso seleccionaremos la Columna1 y desde la ficha Transformar > grupo Cualquier columna > botón Rellenar conseguiremos se efectúe el efecto de autorrellenado de las celdas vacías...
En segundo lugar utilizaremos la primera fila como cabecera, para que no nos aparezca Columna1, Columna2, etc... y sí Ene, Feb, mar, etc.
Así desde la ficha Inicio > grupo Transformar > botón Usar la primera fila como encabezado lo conseguimos:
Limpiaremos ahora los datos que no nos interesan... a saber: cuando en la segunda columna nos aparezca el dato vacía (Null) o con la palabra Total (ya que no nos interesa el dato subtotalizado). Para ello simplemente aplicaremos un filtro sobre el campo:
Estamos preparados para Anular la dinamización de columnas / Unpivot. Seleccionaremos las columnas de los meses y desde la ficha Transformar > grupo Cualquier columna > botón Anular la dinamización de columnas convertiremos nuestra tabla de referencias cruzadas en una tabla estándar, con los meses desplegados en un solo campo...
Por sencillez cambiaremos los nombres de los campos, para lo que basta hacer doble clic sobre ellos.
Acabaremos Cerrando y cargando para tener el dato en la hoja de cálculo como veíamos al inicio de la explicación...
Después bastaría construir nuestra Tabla dinámica sobre lo conseguido y trabajar agrupando elementos según necesitemos...
Una gran ventaja: Nuevos datos incorporados en el origen (si guardan una estructura similar) se incorporarán automáticamente a nuestro informe!!!.
Buen aporte
ResponderEliminar