Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
@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:
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?
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 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.
@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.
Looks like you are trying to pivot the table.
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 . . .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |