Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
m_j_holland
Regular Visitor

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:

 

Screenshot 2024-08-06 090339.png

 

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

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @m_j_holland, another solutions:

 

Result

dufoq3_0-1722929771323.png

 

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

m_j_holland
Regular Visitor

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
Regular Visitor

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
Super User
Super User

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors