Forum Discussion
Split comma delimited cell into multiple rows, keeping row details
Hi all, I'm looking to write a piece of DAX or do something within the PBi Edit Query function which would transform something like the first example below into something like the second - does a...
Yes it should work I have included some screenshots showing as much. I have also included the Power Query Code if you want to replicate the example. Just create a blank query then paste it into the advanced editor within the query editor and it will replicate the table and all the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY89C8IwEIb/S+ZAkkvSj7HaYAd1aAWH0kGhQ1FoQf8/vhfTQocH7k3y5O76XlRCCgMu83N6jyi0Udoq0ibnkCldcCgRumBkFwhY4IAXg+zFAXcEzo/lOy9sOaU9WxkHSv8VCE1Fsqk8yKN6xJndqV7pclMxilnVcM2iUqN2u4HRgPiVSwqtfnsq5K0to8Z7elCPn9e/lTVpyjyFbcrujkUjVgzDDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, ID = _t, Product = _t, Start = _t, End = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"ID", Int64.Type}, {"Product", type text}, {"Start", type date}, {"End", type date}, {"Location", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Location", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Location.1", "Location.2", "Location.3", "Location.4", "Location.5"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Location.1", type text}, {"Location.2", type text}, {"Location.3", type text}, {"Location.4", type text}, {"Location.5", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Company", "ID", "Product", "Start", "End"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}) in #"Removed Columns"
Thanks Thomas, would this work if the same location value (e.g. SE2) occurred in more than one row?
Yes it should work I have included some screenshots showing as much. I have also included the Power Query Code if you want to replicate the example. Just create a blank query then paste it into the advanced editor within the query editor and it will replicate the table and all the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY89C8IwEIb/S+ZAkkvSj7HaYAd1aAWH0kGhQ1FoQf8/vhfTQocH7k3y5O76XlRCCgMu83N6jyi0Udoq0ibnkCldcCgRumBkFwhY4IAXg+zFAXcEzo/lOy9sOaU9WxkHSv8VCE1Fsqk8yKN6xJndqV7pclMxilnVcM2iUqN2u4HRgPiVSwqtfnsq5K0to8Z7elCPn9e/lTVpyjyFbcrujkUjVgzDDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, ID = _t, Product = _t, Start = _t, End = _t, Location = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"ID", Int64.Type}, {"Product", type text}, {"Start", type date}, {"End", type date}, {"Location", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Location", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Location.1", "Location.2", "Location.3", "Location.4", "Location.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Location.1", type text}, {"Location.2", type text}, {"Location.3", type text}, {"Location.4", type text}, {"Location.5", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Company", "ID", "Product", "Start", "End"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"That's really helpful thank you.