Search code examples
excelpowerbipowerquerym

Power Query split a column of text according to a separate column containing a list of integers

I'm trying to use Power Query (m) to split a column of text strings called textStringColumn.

The points where I would like to split the column are in another column called indexColumn.

They are both in a table called myTable.

My approach was as here:

#"output" = Table.SplitColumn(myTable, ColumnToSplit, 
Splitter.SplitTextByPositions(indexColumn), textStringColumn, "default" )

This resulted in an error:

Expression.Error: There is an unknown identifier. 
Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Subsequent attempts using

#"output" = Table.SplitColumn(myTable, ColumnToSplit, 
each Splitter.SplitTextByPositions(indexColumn), textStringColumn, "default" )

produced the same error.

Could you advise how I might correct my query?

Thanks and regards.

EDIT: test data ('pretending' the input data was comma delimited since I can't cut and paste the list column)

textStringColumn,indexColumn
abc 1234 and ghyyyu 432,"[3,19]"
453,"[0]"
hky 7332 4343,"[3,8]"
Solution

  • According to your data, your output will look like this:

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVjA0MjZRSMxLUUjPqKysLFUwMTZS0lGKNtYxtIxVitWJVjIxNQYJGEB4GdmVCubGxkZAdSbGEIUWQJlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [textStringColumn = _t, indexColumn = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"[","",Replacer.ReplaceText,{"indexColumn"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"indexColumn"}),
    #"Parsed List" = Table.TransformColumns(#"Replaced Value1",{{"indexColumn", each List.Transform( Text.Split(_,","), each Number.FromText (_) ) }}),
    #"Added Custom" = Table.AddColumn(#"Parsed List", "Custom", each Splitter.SplitTextByPositions(   [indexColumn] )([textStringColumn])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv))
in
    #"Split Column by Delimiter"

    There is probably a more efficient way to do this so keen to see if horseyride or Ron have alternatives.