Forum Discussion
Solved
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 usi...
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"
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"
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!