cancel
Showing results for 
Search instead for 
Did you mean: 
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

8 REPLIES 8

@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

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.

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.

@jamiezerfahs 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

@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
Responsive Resident
Responsive Resident

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.