Forum Discussion
Split multiple columns using deliminator
Column name: Unit-cost is split by using the below command.
= Table.SplitColumn(#"Filtered rows- Invoice posted from 2017", "Unit-cost", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Unit-cost"})
I'd like to split two more columns by adding commands into the above formula. This will reduce the extra steps of splitting each column one by one.
The other two column name are Qty- ord and Qty-rcv
Thank you in advance,
Hardik Pokal
for the moment there is no "s" at the end of Table.SplitColumn. While waiting for MS to add this letter, you could use something like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYu5EYAwEMR6uRgFPA3QAf8AHgcG3xWAA7ePIdLMauWchJQws4AFI2uWSgIXd2G/c3CKr5zok5UUsVj2moa2cGVj+XVSolnk0e7ra66G+7vMDIxM4v0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), colsToSplit=Table.ColumnNames(Source), #"Split ColumnS by Delimiter" = List.Accumulate(colsToSplit,Source,(s,c)=> Table.SplitColumn(s, c, Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv))) in #"Split ColumnS by Delimiter"
1 Reply
for the moment there is no "s" at the end of Table.SplitColumn. While waiting for MS to add this letter, you could use something like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYu5EYAwEMR6uRgFPA3QAf8AHgcG3xWAA7ePIdLMauWchJQws4AFI2uWSgIXd2G/c3CKr5zok5UUsVj2moa2cGVj+XVSolnk0e7ra66G+7vMDIxM4v0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), colsToSplit=Table.ColumnNames(Source), #"Split ColumnS by Delimiter" = List.Accumulate(colsToSplit,Source,(s,c)=> Table.SplitColumn(s, c, Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv))) in #"Split ColumnS by Delimiter"