Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver III
3 years ago

Problem with extracting numeric characters based on a pre-specified list

Hiya good people of Power query,   I probably have reached out with the same problem before and even accepted some of the solutions proferred here. But I am continuing to have the problem since the...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

I would find each combination of delimiters between which your values reside, make a separate query that extracts the values based on each of these delimiter combinations, and then combine (append) the tables.  For example, a table like 

 

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "(", "||"), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

 

Then another table like

 

= Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "#|||", "#"), type text}})

the values based on each of these delimiter combinations, and then combine (append) the tables.  For example, a table like 

 

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, "(", "||"), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

 

Source = Table.TransformColumns(Table1, {{"Values", each Text.BetweenDelimiters(_, ",,", ","), type text}}),

RemoveNulls = Table.SelectRows(Source, each [Values] <> "")

 

Once you are sure you've found all of the different delimiter combinations, then use the GUI Append Tables function to combine all of the tables. Not easy, but I can't think of another way to do it!

 

--Nate