Convert a single column to multiple table columns

[Anonymous]

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!

[Anonymous · 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:

[Shipra361]

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

[benjellounm]

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

[Anonymous]

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]

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]

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

[Anonymous]

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

[dramus]

Looks like you are trying to pivot the table.

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

[Anonymous]

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 . . .