Forum Discussion

Lyvili's avatar
Lyvili
Frequent Visitor
2 years ago
Solved

Transform in columns (transpone, unpivot..)

Good afternoon, 

I am trying to solve this topic through several ways in PowerQuery. Unfortunately I could not. Could you please assist me?

Thanks in advance!

 

Original:

 

TO BE:

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Your data appears to be a single table. So:

    • Split the table at the first null row
    • Pivot Part 1
    • Add the pivoted Part 1 to each row of Part 2
    let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text},
        {"Column6", type any}, {"Column7", type text}, {"Column8", type text}}),

    #"Split Table" = Table.SplitAt(#"Changed Type", List.PositionOf(#"Changed Type"[Column1],null, Occurrence.First)),
    Part1 = Table.SelectColumns(#"Split Table"{0}, 
                List.FirstN(Table.ColumnNames(#"Split Table"{0}),2)),
    Part2 = Table.PromoteHeaders(Table.RemoveFirstN(#"Split Table"{1},1),[PromoteAllScalars=true, Culture="en-US"]),

    #"Pivot Part1" = Table.Pivot(Part1, Part1[Column1], "Column1","Column2"),
    #"Combine Part2" = Table.AddColumn(Part2, "Part1", each #"Pivot Part1", type table),
    #"Reordered Columns" = Table.ReorderColumns(#"Combine Part2",{"Part1", "ID", "ID A", "ID K", "ID M", "ID G", "Nivel", "Dept", "Centro"}),
    #"Expanded Part1" = Table.ExpandTableColumn(#"Reordered Columns", "Part1", {"Агеа de planificacion", "Fесћа", "С447 -FKA", "C447 -FKB", "С447 -FLK"}, {"Агеа de planificacion", "Fесћа", "С447 -FKA", "C447 -FKB", "С447 -FLK"})
in
    #"Expanded Part1"

     

    Source

     

    Results

    +

     

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Your data appears to be a single table. So:

    • Split the table at the first null row
    • Pivot Part 1
    • Add the pivoted Part 1 to each row of Part 2
    let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text},
        {"Column6", type any}, {"Column7", type text}, {"Column8", type text}}),

    #"Split Table" = Table.SplitAt(#"Changed Type", List.PositionOf(#"Changed Type"[Column1],null, Occurrence.First)),
    Part1 = Table.SelectColumns(#"Split Table"{0}, 
                List.FirstN(Table.ColumnNames(#"Split Table"{0}),2)),
    Part2 = Table.PromoteHeaders(Table.RemoveFirstN(#"Split Table"{1},1),[PromoteAllScalars=true, Culture="en-US"]),

    #"Pivot Part1" = Table.Pivot(Part1, Part1[Column1], "Column1","Column2"),
    #"Combine Part2" = Table.AddColumn(Part2, "Part1", each #"Pivot Part1", type table),
    #"Reordered Columns" = Table.ReorderColumns(#"Combine Part2",{"Part1", "ID", "ID A", "ID K", "ID M", "ID G", "Nivel", "Dept", "Centro"}),
    #"Expanded Part1" = Table.ExpandTableColumn(#"Reordered Columns", "Part1", {"Агеа de planificacion", "Fесћа", "С447 -FKA", "C447 -FKB", "С447 -FLK"}, {"Агеа de planificacion", "Fесћа", "С447 -FKA", "C447 -FKB", "С447 -FLK"})
in
    #"Expanded Part1"

     

    Source

     

    Results

    +

     

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Lyvili, different approach. This one should be dynamic

     

    Result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY7BCoMwEER/JeRcJRu19hoVQcSeBREJNqUBUREp/fxu1LZWRDbLbGbfhBQFFYOS5KZI38hW33Uta9219ERFMyqJ+nfKU0FjVT/MgoENzOaMO3tU6Lo+seJUoMHPR0SABrhHRIjGZQ/YeoudRDglERGzpLNkKFf9VA1qpPoRJVTtOHRTJq+mQvO1aoadBfB5GKrcwjIfXkGwhQCAVz8ObwvJvyR4vuWZxSoBm4SZnJ1E+QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    UpperInfo = Table.FirstN(Source, each [Column1] <> "" and [Column1] <> null),
    UpperInfoRows = List.Transform(Table.ToRows(UpperInfo), each List.Select(_, (x)=> x <> null and x <> "")),
    StepBack = Source,
    LowerData = Table.PromoteHeaders(Table.SelectRows(Table.Skip(StepBack, each [Column1] <> null and [Column1] <> ""), (x)=> x[Column1] <> null and x[Column1] <> "")),
    FinalTable = List.Accumulate( 
     UpperInfoRows,
     LowerData,
     (s,c)=> Table.AddColumn(s, c{0}, each c{1})
     ),
    ReorderedColumns = Table.ReorderColumns(FinalTable, UpperInfo[Column1] & Table.ColumnNames(LowerData))
in
    ReorderedColumns