☰

¿Cómo transpongo una tabla de Excel a columnas únicas de muchas filas duplicadas? (preferiblemente en PowerQuery)

tag-icon • tag-icon microsoft-excel power-query
¿Cómo transpongo una tabla de Excel a columnas únicas de muchas filas duplicadas? (preferiblemente en PowerQuery)

Busqué en todas partes y lo intenté mucho, aparentemente la mayoría de las personas no necesitan que se resuelva este problema :-( Tengo una tabla con dos columnas. La columna A (TAG) tiene 4 valores distintos, que se repiten en ese patrón una y otra vez. Esos se convertirán en mis nombres de columna distintos. La columna B (VALOR) tiene los valores coincidentes para esas etiquetas.

Tabla de ejemplo:
Tabla fuente

Ahora simplemente me gustaría crear una tabla con este aspecto: (Cada valor distinto de la fila ETIQUETA se convierte en un encabezado de columna y todos los valores de la columna VALOR se convierten en valores individuales debajo de esas nuevas columnas)

Tabla de objetivos deseados:
Tabla de objetivos deseados

Preferiblemente haría toda esta transposición en PowerQuery cuando importo el CSV del que provienen los datos, pero también estaría bien hacer esto en alguna operación de rango/tabla más adelante.

Cualquier ayuda sería REALMENTE apreciada :-)

Respuesta1

En Power Query, debe girar la columna ETIQUETA, con la columna VALOR como valores y no aggregation.

Sin embargo, si hace precisamente eso en su conjunto de datos, obtendrá valores de error porque tiene varias entradas para el mismo valor de Pivote.

Esto se puede evitar agregando una columna de índice que numere secuencialmente cada una de las entradas de etiquetas individuales.

Para hacer esto, y también permitir que los TAG no estén siempre en el mismo orden, puede

  • Agrupar por etiqueta
  • agregar una columna de índice a cada subtabla
  • Ampliar las tablas
  • Pivotar en TAG sin agregación
  • Eliminar la columna Índice

En el código siguiente, examine los comentarios y los pasos aplicados para comprenderlo mejor.

Si este método es demasiado lento en su conjunto de datos, hay una función personalizada publicada que puede ejecutarse más rápidamente.

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//Group By Tage
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TAG"}, {

        //add an Index column to each subtable
        {"with Index", each Table.AddIndexColumn(_,"Index",0,1), type table [TAG=nullable text, VALUE=nullable text, Index=Int64.Type]}}),

//Expand the sub tables
    #"Expanded with Index" = Table.ExpandTableColumn(#"Grouped Rows", "with Index", {"VALUE", "Index"}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Expanded with Index", List.Distinct(#"Expanded with Index"[TAG]), "TAG", "VALUE"),

//Remove the Index column
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

ingrese la descripción de la imagen aquí

Editado para mostrar una función personalizada para ejecutar un pivote sin agregación

Función personalizada
pegar en una consulta en blanco y cambiar el nombre como en los comentarios del código

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Código principal

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//call custom pivot function
    pivot = fnPivotAll(#"Changed Type","TAG","VALUE")
in
    pivot

información relacionada