Forum Discussion
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_patte...
=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)})))
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