Forum Discussion
Grouping/splitting a column
I have a list of items that are made at various factories. Some are made at only one factory, others are made at multiple factories, however the factory is only one column. What I need to do is split a column into multiple columns if the item is made at more than one factory. Current Data:
| Factory | Product # |
| 555 | 12345 |
| 555 | 23456 |
| 555 | 34567 |
| 666 | 34567 |
| 666 | 56789 |
| 666 | 32456 |
| 777 | 32456 |
Desired Outcome
| Product # | Factory 1 | Factory 2 |
| 12345 | 555 | |
| 23456 | 555 | |
| 34567 | 555 | 666 |
| 56789 | 666 | |
| 32456 | 666 | 777 |
I guess it's kind of like grouping, but instead of a row count I need the values themselves.
ABR002 add a new column called Factory Number
Factory Number = "Factory " & RANKX ( FILTER ( Factory, Factory[Product # ] = EARLIER ( Factory[Product # ] ) ), Factory[Factory], , ASC )and in matrix visual use following
and here is the output
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
4 Replies
ABR002 add a new column called Factory Number
Factory Number = "Factory " & RANKX ( FILTER ( Factory, Factory[Product # ] = EARLIER ( Factory[Product # ] ) ), Factory[Factory], , ASC )and in matrix visual use following
and here is the output
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
This does work to an extent, but is there a way to do it in the query editor or a way to otherwise make it in a normal table? I have to analyze it with multiple fields, not just the SKU number.
Hi ABR002 ,
I did the same test and thought that using matrix visual is the best choice. The table visual method will have an impact on the table structure, and the use of virtual tables cannot achieve this effect.
Regarding what you mean by the complex field, can you provide more information?
Best Regards,
HenryNothing complex, there's just other data there, like sub skus or peoples names or regions, for now though I think this is fine.
