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
Anonymous
Not applicable

Convert a single column to multiple table columns

I’m still fairly new at PBI, and find it’s a great tool, especially since we often work with millions of records, but I do have a situation I need some help with.

 

I have a table in which I may have one, none, or several identifiers for a given account:

 

Account               Identifier

ABC                        482

BCD                        538

BCD                        674

BCD                        926

CDE                       

DEF                        893

 

I’m trying to group the records by Account in a table, such that the Identifiers are in separate columns:

 

Account               Identifier1           Identifier2           Identifier3           Identifier4

ABD                       482

BCD                       538                         674                         926

CDE                       

DEF                        893

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

@Anonymous Steps to take in Edit Queries

1) Highlight "Value" column and under the Transform toolbar click "Unpivot Columns"

2) Go to Add Column tool bar - click "Index Column" (this will add an index column to your dataset

3) Highlight the Attribute and Index columns, right click and "Merge Columns" (Pick a delimiter)

4) Hightlight the Value and Merged columns, go back to the Transform toolbar, click "Pivot Column"

the "Value" column should be in the Values Column selector, click open Advanced options -> select "Don't Aggregate" from the dropdown. - click OK

Your output should look like this:

pivot.PNG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

9 REPLIES 9

@Anonymous Steps to take in Edit Queries

1) Highlight "Value" column and under the Transform toolbar click "Unpivot Columns"

2) Go to Add Column tool bar - click "Index Column" (this will add an index column to your dataset

3) Highlight the Attribute and Index columns, right click and "Merge Columns" (Pick a delimiter)

4) Hightlight the Value and Merged columns, go back to the Transform toolbar, click "Pivot Column"

the "Value" column should be in the Values Column selector, click open Advanced options -> select "Don't Aggregate" from the dropdown. - click OK

Your output should look like this:

pivot.PNG


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

image.jpg

Hi, is it possible to plot such values in power bi? 
I have a situation where i have to plot mark-up% (measure) against each product id (column). Now there are cases where one product id has multiple mark-ups. Please suggest if it is possible to show that in a scatter plot. For now, i have merged the product ids and mark-up% using a delimeter because mark-up% were showing average (single mark) in scatter plot for product ids which has more than one mark-ups. After merging them, i have placed product id in X-axis, mark-up% in Y-axis and ProductId_markup% in legend to show multiple dots for each product. Also, can you please advise, how can i change the color of these dots to single color because there is no color fx available in power bi. It is impossible to change 1000+ marks one by one

 

I have the same problem and i was wondering how did you get the "value" column?

Anonymous
Not applicable

Thank you so much Eno1978!!!  I'd been wrestling with it for a week, so you were a huge help!  I'd danced around the indexing on several tries, but didn't think to merge.

Anonymous
Not applicable

Is there a response missing from this thread now? The previous message says 'Thanks Eno1978..' but there is no message from Eno1978 and the Accepted Solution seems to be only a partial solution. Is it possible to squash the table back up, so that it doesn't run to hundreds of columns? Thanks.

@Anonymous Eno1978 was my old handle. I changed it. I assume you have a different use case that this solution does not work for? I would suggest that you create a new post that references this one if it is close, but different. That way people that answer all questions will recognize it as a new thread instead of just someone posting on an old "solved" thread.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

@Seth_C_Bauer I've applied the same steps for the below scenario which is not giving me the expected result. Is it different from the above.

sivanjali_0-1619436039485.png

 

dramus
Continued Contributor
Continued Contributor

Looks like you are trying to pivot the table.

 

  • What problem are you trying to solve?
  • Is there any logic behind the identifiers?
Anonymous
Not applicable

Thanks for reaching out!  It's now working fine.  Yes, I was trying to pivot but, without the indexing, I was getting a huge number of columns . . .

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.