Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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, ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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"