Forum Discussion
Splitting multiple columns using delimiter
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"
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
Worked like a charm for me! Thank you so much
