Forum Discussion

WMart_AMS's avatar
WMart_AMS
Frequent Visitor
1 year ago
Solved

transform multiple columns into 2 columns (List.Zip, (un)pivot)

Hi Everybody,   Hopefully somebody can help me out. I am working with the data set in the image below. basically i have 5 categrories: Scope, Planning, Financien, Capaciteit and Kwaliteit. These ca...
Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    One more simple way:

     

    Your Source:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZSxTsMwEEB/5ZS5Q+JQuheEijpQkbHq4CaH6yqxIzcpEiPfg8RH8Cd8CVcLQomjKm3MYF3s2C+Xp/Mtl0EUjIJJHF5FYRhNIqBZkuoSKcY0ZljBXmuzRoMyk0rs1nUmaFHuQJcCN1qLDApa+Hx9i4PVqA2kyfyZ57JCWdEzo3GLsEEsDOfZASMVTStYJA+w5wrYGJ5wbWpuJAj8eFfd0EXOlaJ8vpn3CjKEpKprI4zG0qIyTLGgzOFFbu370ugtphV2I294ydOfRFWd5xSmcns4uJg+zoFztaekhRVyWBW1TcF+i36hm3onFVFR/TJtON7LaPP4epD6Dp5n8w3Un/kG6dV8Q+1pPmZexVucb/EW6le8RXoXb6knxLP29TgWf4klB9hWf4Emh9kyf7YhB9jl/fyyc7A9xDe3Y1jFOzgfFe9Ah1e8g/RS8Q61p/iYDW/yf3n/YN5Tr3GQ3s2f7DWrLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_code = _t, Category = _t, Indicator = _t, Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Category", type text}, {"Indicator", Int64.Type}, {"Comments", type text}})
in
    #"Changed Type"

     

     

    Target:

     

    let
    Source = Table,
    #"Replaced Value" = Table.ReplaceValue(Source,null,-99999,Replacer.ReplaceValue,{"Indicator"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","null","-9999990",Replacer.ReplaceValue,{"Comments"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Index", "Project_code", "Category"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Category", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Scope Indicator", type text}, {"Scope Comments", type text}, {"Kwaliteit Indicator", type text}, {"Kwaliteit Comments", type text}, {"Planning Indicator", type text}, {"Planning Comments", type text}, {"Capaciteit Indicator", type text}, {"Capaciteit Comments", type text}, {"Finacien Indicator", type text}, {"Finacien Comments", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type","-99999",null,Replacer.ReplaceValue,{"Scope Indicator", "Kwaliteit Indicator", "Planning Indicator", "Capaciteit Indicator", "Finacien Indicator"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","-9999990",null,Replacer.ReplaceValue,{"Scope Comments", "Kwaliteit Comments", "Planning Comments", "Capaciteit Comments", "Finacien Comments"})
in
    #"Replaced Value3"

    Output:

     

    I used replace with some standard value for nulls and after the format achieved, replaced back to nulls. 

     

    Hope this helps!

     

  • WMart_AMS's avatar
    WMart_AMS
    1 year ago

    I tried this out and it worked! Thanks!

slorin's avatar
slorin
Super User
1 year ago

Hi WMart_AMS 

 

A solution with List.TransformMany

let
Source = Your_Source,
ColumnNames = Table.ColumnNames(Source),
Data = Table.ToRows(Source),
Transform = List.TransformMany(Data, each {1..5},
  (x,y) => List.FirstN(x,2) & {Text.BeforeDelimiter(ColumnNames{y*2+1}, " ")} & List.Reverse(List.Range(x,y*2,2))),
Result = Table.FromRows(Transform, {"Index", "Project_code", "Category", "Indicator", "Comment"})
in
Result

or

let
Source = Your_Source,
ColumnNames = List.Distinct(List.Transform(
  List.RemoveFirstN(Table.ColumnNames(Source),2),
  each Text.BeforeDelimiter(_, " "))),
Data = Table.ToRows(Source),
Transform = List.TransformMany(Data, each {1..5},
  (x,y) => List.FirstN(x,2) & {ColumnNames{y-1}} & {x{y*2+1}} & {x{y*2}}),
Result = Table.FromRows(Transform, {"Index", "Project_code", "Category", "Indicator", "Comment"})
in
Result

 

 

Stéphane