Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Now is the actual problem, how do I spilt it by the ";" on new rows to look like this:
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.
Could you please show me if there is a better (straightforward) way?
Solved! Go to Solution.
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:
Then do the same operations in another two tables, finally merge these tables based on index column, you can get what you want.
Hope it's helpful to you.
Jimmy Tao
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:
Then do the same operations in another two tables, finally merge these tables based on index column, you can get what you want.
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |