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!

SundarRaj's avatar
SundarRaj
Icon for Super User rankSuper User
1 year ago

Hi WMart_AMS , here's another solution you could possibly look at. I'll leave the images of the output, the source and text of code used. Thanks!

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],


#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_code", Int64.Type}, {"Scope opmerkingen", type text}, {"Scope indicator", Int64.Type}, {"Kwaliteit opmerkingen", type text}, {"Kwaliteit indicator", Int64.Type}, {"Planning opmerkingen", type text}, {"Planning indicator", Int64.Type}, {"Capaciteit opmerkingen", type text}, {"Capaciteit indicator", Int64.Type}, {"Financien opmerkingen", type text}, {"Financien indicator", Int64.Type}}),


Custom1 = List.Split(List.Skip(Table.ToColumns(#"Changed Type"),2),2),


Custom2 = List.Transform(Custom1, each Table.FromColumns(_)),


Custom3 = List.Transform(Custom2, each Table.AddIndexColumn(_,"Project_Code",0,1)),


Custom4 = List.Transform(Custom3, each Table.TransformColumns(_, {"Project_Code", each #"Changed Type"[Project_code]{_}})),


Custom5 = List.Transform(Custom4, each Table.AddIndexColumn(_,"Index",0,1)),


Custom6 = List.Transform(Custom5, each Table.TransformColumns(_, {"Index", each #"Changed Type"[Index]{_}})),


Custom7 = Table.Combine(Custom6),


#"Reordered Columns" = Table.ReorderColumns(Custom7,{"Index", "Project_Code", "Column1", "Column2"}),


#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Index","Project_Code"}, {{"All", each _, type table [Index=number, Project_Code=number, Column1=nullable text, Column2=nullable number]}})[All],


Custom8 = List.Transform(#"Grouped Rows", each Table.AddIndexColumn(_, "Category",0,1)),


Custom9 = {"Scope","Kwaliteit","Planning","Capaciteit","Financien"},


Custom10 = List.Transform(Custom8, each Table.TransformColumns(_,{"Category", each Custom9{_}})),


Custom11 = Table.Combine(Custom10),


#"Reordered Columns1" = Table.ReorderColumns(Custom11,{"Index", "Project_Code", "Category", "Column2", "Column1"}),


#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Column2", "Indicators"}, {"Column1", "Comments"}})


in


#"Renamed Columns"