Forum Discussion
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...
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!
I tried this out and it worked! Thanks!
AntrikshSharma
Community Champion
Community Championlet
Source = Table,
Transform = Table.TransformRows (
Source,
(Row) =>
let
ColumnValues = List.Skip ( Record.ToList ( Row ), 2 ),
ColumnNames = List.Skip ( Record.FieldNames ( Row ), 2 ),
Comments = List.Alternate ( ColumnValues, 1, 1, 1 ),
Indicator = List.Alternate ( ColumnValues, 1, 1 ),
Category = List.Distinct (
List.Transform ( ColumnNames, ( x ) => Text.BeforeDelimiter ( x, " " ) )
),
ColumnsCombined = Table.FromColumns (
{ { Row[Index] } }
& { { Row[Project_code] } }
& { Category }
& { Indicator }
& { Comments },
type table [
Index = Int64.Type,
Project Code = Int64.Type,
Category = text,
Indictory = Int64.Type,
Commments = text
]
),
FillDownColums = Table.FillDown ( ColumnsCombined, { "Index", "Project Code" } )
in
FillDownColums
),
CombineTables = Table.Combine ( Transform )
in
CombineTables