Split Column by Dynamic Delimiters in another Field

[m_j_holland]

Hi,

I'm using Power Query to split a field by a delimiter in another field. Below is an example of the data I'm using:

In the above example, I want to split the field "expected_pattern" by the delimiter found in the "expected_delimiter" field. You'll see that this is different for each row - the first has "|" and the second has ";".

I'm using the max of "count_of_fields" to get the total number of columns to split by:

= List.Transform({1..if List.Max(#"Filtered Taxonomy Field"[count_of_fields]) = null then 0 else List.Max(#"Filtered Taxonomy Field"[count_of_fields])}, each "field_" & Text.From(_))

I've called this step "Dynamic Column Names. I then want to split each column by the delimiter found in the "expected_delimiter" field, but I don't know how to amend the query step to make this dymanic by row:

= Table.SplitColumn(#"Filtered Taxonomy Field", "expected_pattern", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), #"Dynamic Column Names" )

Can anyone help with this?

Thanks,

Mark

[dufoq3]

Hi @m_j_holland, another solutions:

Result

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlHSUUpMTFSoUUhKSgKSycnJSrE60UrWQHFDI2MFawUTUzMgaW5hCSQNDAzAsvpA2YrKKgV9hcLyVCCZmV+gFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [expected_delimiter = _t, expected_pattern = _t]),
    Ad_Splitted = Table.AddColumn(Source, "Splitted", each 
        [ a = Text.Split([expected_pattern], [expected_delimiter]),
          b = List.Accumulate({0..List.Count(a)-1}, [], (s,c)=> Record.AddField(s, "Split0" & Text.From(c+1), Text.Trim(a{c})))
        ][b], type record ),
    FieldNames = List.Distinct(List.Combine(List.Transform(Ad_Splitted[Splitted], Record.FieldNames))),
    ExpandedSplitted = Table.ExpandRecordColumn(Ad_Splitted, "Splitted", FieldNames)
in
    ExpandedSplitted

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlHSUUpMTFSoUUhKSgKSycnJSrE60UrWQHFDI2MFawUTUzMgaW5hCSQNDAzAsvpA2YrKKgV9hcLyVCCZmV+gFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [expected_delimiter = _t, expected_pattern = _t]),
    Ad_SplitColumns = Table.Combine(List.TransformMany(Table.ToRecords(Source),
        each { Record.TransformFields(_, { "expected_pattern", (x)=> 
                    [ a = Text.Split(x, [expected_delimiter]),
                      b = Record.Combine(List.Transform({0..List.Count(a)-1}, (y)=> Record.AddField([], "Split0" & Text.From(y+1), Text.Trim(a{y})))  )
                    ][b] }) },
        (x,y)=> Table.FromRecords({x & Record.Field(y, "expected_pattern")}) ) )
in
    Ad_SplitColumns

[m_j_holland]

I've figured it out. I've amended it to this ...

{"Col_"&Number.ToText(x+1,"00"),each a{x}}),2)})))

Thanks,

Mark

[m_j_holland]

Hi @wdx223_Daniel ,

Thanks for this. We're almost there. I can see where you've added in "Col_00" for the field name. I need this to start from 1 and go on, potentially beyond 10. How do I amend it to allow for this?

[wdx223_Daniel · Accepted solution]

=Table.Combine(List.Transform(Table.ToRecords(#"Filtered Taxonomy Field"),each let a=Text.Split([expected_pattern],[expected_delimiter]) in Table.FromRecords({Record.TransformFields(_,List.Transform(List.Positions(a),(x)=>{"Col_"&Number.ToText(x,"00"),each a{x}}),2)})))