Begginer question: Split several delimiters

[Anonymous]

Hello, I'm a begginer with Power BI and Power Query and I'm currently lost.

I currently have 10000 lines like this:

F550876]27.10.2021]Emp3]West
C546767[29.10.2021^Emp3[North
D642576[02.11.2021\Emp3]West

There is different delimiters at the same place and I don't know how I can do to split them into columns. I tried to find solution on the forum already but everything I tried didn't work.

Do you have an idea on how I can do please?

Thank you!

Kind Regards

[tackytechtom · Accepted solution]

Hi @Anonymous ,

You can add this code in the Advanced Editor:

#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"[","]","\", "_", "^"}, QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"})   

You can specify all potential Delimeters in the SplitTextByAnyDelimiter() function.

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Anonymous]

That's working perfectly, thank you!!

[tackytechtom]

Hi @Anonymous ,

You can split columns by positions:

I chose these ones in your case:

And this is the result I got:

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Anonymous]

Thank you for your answer Tom!

The small problem I have is that sometimes the data can be like this:

L49106_28.10.2021]Emp10^West
P539648]29.10.2021\Emp6[East

So the delimiter by position don't really work sadly. Sorry I should have show it before.