Transformar filas con valor repetido a columna. Power Query

[West]

Buenas tardes:

Tengo una tabla denomida "Tabla Actual" cuya columna "nº" tiene valores repetidos y estos valores quería traspasarlos a columnas según la figura que adjunto.

Agradería ayuda para hacer la transformación usando power query en Power BI.

Muchas gracias.

West.

[Anonymous · Accepted solution]

Hi @West ,

If I understand correctly, the issue is that you want to convert rows with duplicate values to columns. Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Click on Transform data.

3.Select the table and choose advanced editor.

4.Enter the following statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABLGSrE6EK4pEJvAeWZwOSOwuA6QhvHMwBjEMwaL6yhZgHkmYD1Ag8E8U7A4TKUpij5TFH0gniXEvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [N = _t, BI = _t, VA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"N", Int64.Type}, {"BI", Int64.Type}, {"VA", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"N"}, {{"Count", each _, type table [N=nullable number, BI=nullable number, VA=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"BI", "VA", "Index"}, {"BI", "VA", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"BI", "VA"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Only Selected Columns", {{"Index", type text}}, "en-US"),{ "Attribute","Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

5.The result is shown below.

Best Regards,

Wisdom Wu