Forum Discussion
Splitting multiple columns using delimiter
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 |
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"
6 Replies
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"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 :smileyhappy:
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!
Brilliant, thanks
Yeesh! ImkeF.
