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
Gabrielfavali
New Member

New to the Power BI - Spliting content of multiple columns into a single table

Hello!

 

I am new to the Power BI community, and need some help on data formatting.

I am working with Power Query trying to split a single line table into a actual table for analysis.

My data source comes out as a string of text with two separators, like this:

 

ID#,ITEM,QTY,DESCRIPTION
0001,A;B;C,1;10;1,aa;bb;cc
0002,D,40,dd
0003,A;D;E;F;G,1;1;1;2;1,aa;dd;ee;ff;gg

Once I split by the first separator -","- it looks like this: 

Capturar.PNG

 

 

 

 

Now is the actual problem, how do I spilt it by the ";" on new rows to look like this:Capturar2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

I did managed to do it by breaking it down to a table for each column, splitting it normally, adding a index column, and merging it back using the index as reference, but it looks more like a workaround, as I end up with 5 queries on my table.

Capturar3.PNG

 

Could you please show me if there is a better (straightforward) way?

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi Gabrielfavali,

 

After you split all columns by semicolon delimiter, you can dupicate the table as another two tables, then you have table1, table2 and table3. In table1, click on columns [ITEM.1]~[ITEM.5] -> click "Unpivot Columns" -> remove useless columns -> create an index , then you will get a table like this:

1.PNG 

 

Then do the same operations in another two tables, finally merge these tables based on index column, you can get what you want.

2.PNG 

 

PBIX: https://www.dropbox.com/s/ds84g5csxzwh4ck/New%20to%20the%20Power%20BI%20-%20Spliting%20content%20of%....

 

Hope it's helpful to you.

 

Jimmy Tao

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

Hi Gabrielfavali,

 

After you split all columns by semicolon delimiter, you can dupicate the table as another two tables, then you have table1, table2 and table3. In table1, click on columns [ITEM.1]~[ITEM.5] -> click "Unpivot Columns" -> remove useless columns -> create an index , then you will get a table like this:

1.PNG 

 

Then do the same operations in another two tables, finally merge these tables based on index column, you can get what you want.

2.PNG 

 

PBIX: https://www.dropbox.com/s/ds84g5csxzwh4ck/New%20to%20the%20Power%20BI%20-%20Spliting%20content%20of%....

 

Hope it's helpful to you.

 

Jimmy Tao

Thanks Jimmy.

 

Is there a way to remove these extra tables from the query table on the final sheet?

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.