Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ebecerra
Employee
Employee

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

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.

DateInfo
6/14/2019TA56TB54VZ2
6/13/2019TA2TB21FD5SL32PPR1

 

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

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

 

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

 

Thanks!

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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:

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

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:

OMG Thank you @jdbuchanan71! You're brilliant!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.