Forum Discussion

m_j_holland's avatar
m_j_holland
Regular Visitor
2 years ago
Solved

Split Column by Dynamic Delimiters in another Field

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

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    =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)})))

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    =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)})))

  • m_j_holland's avatar
    m_j_holland
    Regular Visitor
    2 years ago

    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?

  • m_j_holland's avatar
    m_j_holland
    Regular Visitor
    2 years ago

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

     

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

     

    Thanks,

    Mark