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!

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

Hi WMart_AMS, query for your sample data:

Output

let
    Source = Table.TransformColumns(Table.FromColumns(List.Split(Text.Split("1,1,1,1,1,2,2,2,2,2,3,3,3,7304100171,7304210056,7304210032,7304210053,7304210033,7304100171,7304210056,7304210032,7304210053,7304210033,7304100171,7304210056,7304210032,Tekst scope 1,Tekst scope 2,Tekst scope 3,Tekst scope 4,Tekst scope 5,Tekst scope 6,Tekst scope 7,Tekst scope 8,Tekst scope 9,Tekst scope 10,Tekst scope 11,Tekst scope 12,Tekst scope 13,3,3,3,,,3,3,3,,,3,3,3,Tekst kwaliteit1,Tekst kwaliteit2,Tekst kwaliteit3,Tekst kwaliteit4,Tekst kwaliteit5,Tekst kwaliteit6,Tekst kwaliteit7,Tekst kwaliteit8,Tekst kwaliteit9,Tekst kwaliteit10,Tekst kwaliteit11,Tekst kwaliteit12,Tekst kwaliteit13,2,3,3,,,2,3,3,,,2,3,3,Tekst planning1,Tekst planning2,Tekst planning3,Tekst planning4,Tekst planning5,Tekst planning6,Tekst planning7,Tekst planning8,Tekst planning9,Tekst planning10,Tekst planning11,Tekst planning12,Tekst planning13,2,2,2,,,2,2,2,,,2,2,2,Tekst capaciteit1,Tekst capaciteit2,Tekst capaciteit3,Tekst capaciteit4,Tekst capaciteit5,Tekst capaciteit6,Tekst capaciteit7,Tekst capaciteit8,Tekst capaciteit9,Tekst capaciteit10,Tekst capaciteit11,Tekst capaciteit12,Tekst capaciteit13,2,2,2,,,2,2,2,,,2,2,2,Tekst financien1,Tekst financien2,Tekst financien3,Tekst financien4,Tekst financien5,Tekst financien6,Tekst financien7,Tekst financien8,Tekst financien9,Tekst financien10,Tekst financien11,Tekst financien12,Tekst financien13,3,2,2,,,3,2,2,,,3,2,2", ","), 13), {"Index", "Project_code", "Scope opmerkingen", "Scope indicator", "Kwaliteit opmerkingen", "Kwaliteit indicator", "Planning opmerkingen", "Planning indicator", "Capaciteit opmerkingen", "Capaciteit indicator", "Financien opmerkingen", "Financien indicator"}), {}, each if _ = "" then null else _),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each List.Split(List.Skip(_, 2), 2),
        (x,y)=> {x{0}, x{1}} & y ), {"Index", "Project_code", "Category", "Indicator"})
in
    Transformed