How to create new column that parses correct values from a row to a list

Question

I am struggling on creating a formula with Power Bi that would split a single rows value into a list of values that i want.

So I have a column that is called ID and it has values such as:

"ID001122, ID223344" or "IRRELEVANT TEXT ID112233, MORE IRRELEVANT;ID223344 TEXT"

What is important is to save the ID and 6 numbers after it. The first example would turn into a list like this: {"ID001122","ID223344"}. The second example would look exactly the same but it would just parse all the irrelevant text from between.

I was looking for some type of an loop formula where you could use the text find function to find ID starting point and use middle function to extract 8 characters from the start but I had no progress in finding such. I tried making lists from comma separator but I noticed that not all rows had commas to separate IDs.

The end results would be that the original value is on one column next to the list of parsed values which then could be expanded to new rows.

ID                                    Parsed ID
"Random ID123456, Text;ID23456"       List {"ID123456","ID23456"}

Any of you have former experience?

Karhu12 Karhu12 · Accepted Answer · 2018-08-06T12:46:01

Hey I found the answer by myself using a good article similar to my problem.

Here is my solution without any further text parsing which i can do later on.

each let
    PosList = Text.PositionOf([ID],"ID",Occurrence.All),
    List = List.Transform(PosList, (x) => Text.Middle([ID],x,8)) 
in List

For example this would result "(ID343137,ID352973) ID358388" into {ID343137,ID352973,ID358388}

Ended up being easier than I thought. Suppose the solution relied again on the lists!

How to create new column that parses correct values from a row to a list

1 Answers