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 anyone please have any suggestions?
Thanks so much.
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"
6 Replies
You can also do this without having to write any code. Just select the column with the data you want to split (Locations, in your example), then go to the Transform tab, click the drop-down arrow on the Split Column button. Select the option to Split Column by Delimiter then select your delimeter,click the arrow to expand the "Advanced options", and then select Rows.
The easiest way is probably to do it in the query editor split the location comma using comma as the delimiter then unpivot the table on the multiple location columns. You may need to filter out nulls as you have a varying amount of locations.
As for doing this in dax I am not sure of the best way to do it and it may be impossible. It might be possible with something like summarizecolumns.
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"
