Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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"
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks Thomas, would this work if the same location value (e.g. SE2) occurred in more than one row?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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"
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      That's really helpful thank you.