Hace algunos días expuse como 'desapilar' columnas de datos empleando unas funciones estándar de nuestra hoja de cálculo favorita (ver post aquí).
La idea de hoy es conseguir el mismo resultado empleando Power Query para Excel (Obtener y transformar).
Supondremos nuestro listado de Datos apilado, el cuál hemos convertido en Tabla...
El objetivo a lograr es disponer por columnas los conceptos listados...
Para ello usaremos algunas funciones M de Power Query, como:
List.Sum(listado): que nos devuelve una suma a partir de los elementos del listado indicado.
Table.SelectRows(tabla_de_trabajo, condición as function)): Devuelve una tabla que contiene únicamente las filas que responden a la condición dada.
List.Distinct(listado, optional equationCriteria as any, criteria as any): Filtra un listado abajo mediante la eliminación de duplicados. Podemos especificar un valor de criterio de ecuación de manera opcional para controlar la comparación de igualdad. Se elige el primer valor de cada grupo de igualdad.
Y alguna otra más simple ya utilizada anteriormente... o que sencillamente requiere el uso del Asistente de Power Query.
El primer paso consiste, como siempre, en cargar nuestra Tabla de información.
El código completo de la consulta sería:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]
Que desgranamos en los pasos relevantes...
Las dos primeras líneas de código son las básicas que nos da directamente el asistente de consultas:
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),
con ellas se cargan los datos de la tabla 'Tabla1' del libro actual, y posteriormente forzamos el tipo de datos para las dos columnas.
La siguiente línea añade una columna que rellenamos con unos...
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),
Estos unos los usaremos en pasos siguientes para acumular y obtener un orden...
Otro paso consiste en añadir una columna tipo Índice que comenzará desde 1:
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),
columna que emplearemos para construir nuestras condiciones...
Hasta ahora tenemos en nuestra consulta este aspecto:
Siguiente paso, muy importante. Ahora toca obtener una acumulado creciente condicionado a nuestros conceptos...
Esto se consigue con la línea de código:
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]
Añade la columna nueva 'Id creciente', donde se incrementa/acumula el valor de la columna 'Cond1' cuando el 'concepto' coincide y el número de fila del 'Indice' creado va aumentando...
Este paso es fundamental para alcanzar nuestra meta, ya que nos permite tener un nexo de unión común para cada pack de datos ('empresa', 'país', 'facturación').
Los pasos últimos son sencillos..
Uno nos permite eliminar columnas que ya no necesitamos ver ("Cond1" y "Fila")
BorraColumnas = Table.RemoveColumns(RunTotal,{"Cond1","Fila"}),
Y el último transforma la disposición de datos a modo de tabla dinámica... siendo desde ahora los conceptos nuevas columnas de información:
Pivoted_Column = Table.Pivot(BorraColumnas, List.Distinct(BorraColumnas[concepto]), "concepto", "valores")
tomando como referencia de fila única nuestra columna acumulada...
Conseguido... tenemos nuestros datos 'desapilados'.
La idea de hoy es conseguir el mismo resultado empleando Power Query para Excel (Obtener y transformar).
Supondremos nuestro listado de Datos apilado, el cuál hemos convertido en Tabla...
El objetivo a lograr es disponer por columnas los conceptos listados...
Para ello usaremos algunas funciones M de Power Query, como:
List.Sum(listado): que nos devuelve una suma a partir de los elementos del listado indicado.
Table.SelectRows(tabla_de_trabajo, condición as function)): Devuelve una tabla que contiene únicamente las filas que responden a la condición dada.
List.Distinct(listado, optional equationCriteria as any, criteria as any): Filtra un listado abajo mediante la eliminación de duplicados. Podemos especificar un valor de criterio de ecuación de manera opcional para controlar la comparación de igualdad. Se elige el primer valor de cada grupo de igualdad.
Y alguna otra más simple ya utilizada anteriormente... o que sencillamente requiere el uso del Asistente de Power Query.
El primer paso consiste, como siempre, en cargar nuestra Tabla de información.
El código completo de la consulta sería:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]
Que desgranamos en los pasos relevantes...
Las dos primeras líneas de código son las básicas que nos da directamente el asistente de consultas:
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),
con ellas se cargan los datos de la tabla 'Tabla1' del libro actual, y posteriormente forzamos el tipo de datos para las dos columnas.
La siguiente línea añade una columna que rellenamos con unos...
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),
Estos unos los usaremos en pasos siguientes para acumular y obtener un orden...
Otro paso consiste en añadir una columna tipo Índice que comenzará desde 1:
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),
columna que emplearemos para construir nuestras condiciones...
Hasta ahora tenemos en nuestra consulta este aspecto:
Siguiente paso, muy importante. Ahora toca obtener una acumulado creciente condicionado a nuestros conceptos...
Esto se consigue con la línea de código:
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]
Añade la columna nueva 'Id creciente', donde se incrementa/acumula el valor de la columna 'Cond1' cuando el 'concepto' coincide y el número de fila del 'Indice' creado va aumentando...
Este paso es fundamental para alcanzar nuestra meta, ya que nos permite tener un nexo de unión común para cada pack de datos ('empresa', 'país', 'facturación').
Los pasos últimos son sencillos..
Uno nos permite eliminar columnas que ya no necesitamos ver ("Cond1" y "Fila")
BorraColumnas = Table.RemoveColumns(RunTotal,{"Cond1","Fila"}),
Y el último transforma la disposición de datos a modo de tabla dinámica... siendo desde ahora los conceptos nuevas columnas de información:
Pivoted_Column = Table.Pivot(BorraColumnas, List.Distinct(BorraColumnas[concepto]), "concepto", "valores")
tomando como referencia de fila única nuestra columna acumulada...
Conseguido... tenemos nuestros datos 'desapilados'.
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
