Forum Discussion
Solved
append
Hey everybody ! I have a table like this : id theme 1 theme 2 theme 3 1 car 2 car bike 3 car 4 car moto 5 moto 6 moto bike 7 ...
Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}}) in #"Renamed Columns"
Have you tried unpivot transformation? Copy and paste this into your Query Editor for reference:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpOLAKSIBSrE61kBBdJysxOVYAJG2MoNEEWyc0vyQeLmsI4CIVmCCEUI80xlVpgCllCtaEaApYyNEDIgZXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"theme 1" = _t, #"theme 2" = _t, #"theme 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"theme 1", type text}, {"theme 2", type text}, {"theme 3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Theme"}})
in
#"Renamed Columns"Indeed that works
Thank you very much Nick !
