Splitting multiple columns using delimiter

[scoobymoo]

Hi

I have a table that has several columns with delimiters. I need to split the columns into rows and maintain the order of the values in each column. I know how to split one column into rows using the delimiters but can't seem to do this successfully with multiple columns and maintain the order. Is this possible?

Thank you

Example. I need to split this:

Person ID	Type	Order ID	Date of Transaction	Date of Payment
001	Blue	100,101	01/01/18, 02/01/18	02/01/18, 03/01/18
002	Blue	102,103,104	01/01/18, 01/01/18, 02/01/18	02/01/18, 02/01/18, 03/01/18
003	Red	105,106	01/12/2016, 13/04/2015	02/12/2016, 13/04/2015

Into this:

Person ID	Type	Order ID	Date of Transaction	Date of Payment
001	Blue	100	01/01/2018	02/01/2018
001	Blue	101	02/01/2018	03/01/2018
002	Blue	102	01/01/2018	02/01/2018
002	Blue	103	01/01/2018	02/01/2018
002	Blue	104	02/01/2018	03/01/2018
003	Red	105	01/01/2018	02/01/2018
003	Red	106	02/01/2018	03/01/2018

[ImkeF · Accepted solution]

I'd go for this solution: Add a column that creates an expandable table from the relevant columns.

Table.FromColumns({Text.Split([Order ID], ","), Text.Split([Date of Transaction], ","), Text.Split([Date of Payment], ",") }, {"OrderIDs", "Transactions", "Payments"} )

To see how this works, you can paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hcw9CoAwDAXgq5TMgealPzh7BNfSzW6u3t/UiigIQkLCS/hKIRDTvO3NBkQY0gOBt8LETnRsPdQ7DFdYuZA+ATUgWMc38sN9w8GelraebjIzDxPqVZDZwZ5j39PQvg61Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, Type = _t, #"Order ID" = _t, #"Date of Transaction" = _t, #"Date of Payment" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromColumns({Text.Split([Order ID], ","), Text.Split([Date of Transaction], ","), Text.Split([Date of Payment], ",") }, {"OrderIDs", "Transactions", "Payments"} )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OrderIDs", "Transactions", "Payments"}, {"OrderIDs", "Transactions", "Payments"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Transactions", type date}, {"Payments", type date}})
in
    #"Changed Type1"

[MarjorieHG]

Worked like a charm for me! Thank you so much

[MMMiranda]

Brilliant, thanks

[scoobymoo]

I'm a total newbie to PowerBi - this was the first file I uploaded so it took me a while to figure out the code with my actual work related fields but this worked brilliantly! Thank you!

[Sean]

So if anyone is following @ImkeF's steps in a Different Locale ( for example US ) just change the last step to include "en-GB"

and the error you may be getting in the last row will be gone!

...
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom", {{"Transactions", type date},
{"Payments", type date}}, "en-GB")
in
    #"Changed Type1"

Alternatively you could delete the last Step in the Applied Steps in the Query Editor

then Ctrl+select both Transactions and Payments columns

right-click either column header - Change Type - Using Locale... - Data Type: Date - Locale: English (UK)

and that would generate the "en-GB" to convert the European dates to US format

[Greg_Deckler]

Yeesh! @ImkeF.