Creating a table from another table's column based on a regular expression

[ebecerra]

Hi everyone, this is the problem I have. I have this table with a column with some "string" that I need to split into different rows based on a regex. Let me show you what I mean.

I have this table that has an Info column with a string that has a tag and a number associated with it. There is always a string and then a number next to it.

Date	Info
6/14/2019	TA56TB54VZ2
6/13/2019	TA2TB21FD5SL32PPR1

And I would like to create a new table with the Info column to expand it and look like this:

Date	TAG	VALUE
6/14/2019	TA	56
6/14/2019	TB	54
6/14/2019	VZ	2
6/13/2019	TA	2
6/13/2019	TB	21
6/13/2019	FD	5
6/13/2019	SL	32
6/13/2019	PPR	1

Is this something possible in PBI? If so, could you point me to the right direction on how to do this please?

Thanks!

[jdbuchanan71 · Accepted solution]

Hello @ebecerra ,

You can load the data into PowerQuery then use the Split Column by DigitToNonDigit and then Unpivot then Split Column by NonDigitToDigit.  I put together a quick video showing the steps:

[ebecerra]

OMG Thank you @jdbuchanan71! You're brilliant!