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!
jgeddes
Super User
Super UserMaybe something like this may work...
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdRNioNAEAXgq4ReZ2F1Rdu5x+yCCxEnNJGOEIe5fiKZrv6pKpe+F8KHFu96NWDOxmFzgaYBtz98z/fndnpOj3U+7c9I2f1vXPw2+22PLcXrMobgw61Mp3EdJ+HXPz6MYfJz+Pz3cE4E+za0XUWwMqGMI8EqBCsTPnFNQFsRUCagSECFgDIBRUKLFeHyfg6/y8IVrIkQViQLq4hDDXsptahVRayJIlYkEatI1OYie3SsnfyZOvFYyzRBypwUHR2rNQfH6mRCGUeCUwhOJji6lJzAjrWXCb1I6BVCLxN6kcCO9Us9DdZECCuShVXEoYa9lFoEjUriFe0Za7JNY13atSZn4eG8avuqDKy6sNrEpo1Fc3C2oIwsyCsL2syCsrOQhjZ3sNsFZWlBnlrQthaUsf3Ph+EF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_code = _t, #"Scope opmerkingen" = _t, #"Scope indicator" = _t, #"Kwaliteit opmerkingen" = _t, #"Kwaliteit indicator" = _t, #"Planning opmerkingen" = _t, #"Planning indicator" = _t, #"Capaciteit opmerkingen" = _t, #"Capaciteit indicator" = _t, #"Financien opmerkingen" = _t, #"Financien indicator" = _t]),
set_types =
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}
}
),
indicator_columns =
List.Select(
Table.ColumnNames(set_types),
each Text.Contains(_, "indicator")
),
opmerkingen_columns =
List.Select(
Table.ColumnNames(set_types),
each Text.Contains(_, "opmerkingen")
),
columns_list =
List.Transform(
opmerkingen_columns,
each Text.BeforeDelimiter(_, " ")
),
group_rows =
Table.Group(
set_types,
{"Index", "Project_code"},
{
{"AllRows", each List.Zip({columns_list, Record.ToList(Table.SelectColumns(_, indicator_columns){0}), Record.ToList(Table.SelectColumns(_, opmerkingen_columns){0})}), type list}
}
),
expand_nested =
Table.ExpandListColumn(
group_rows,
"AllRows"
),
extract_text =
Table.TransformColumns(
expand_nested,
{
{"AllRows", each Text.Combine(List.Transform(List.ReplaceValue(_, null, "", Replacer.ReplaceValue), each Text.From(_)), "|")}
}
),
split_column =
Table.SplitColumn(
extract_text,
"AllRows",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Category", "Indicator", "Comment"}
)
in
split_column