Grouping/splitting a column

[ABR002]

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.

[v-henryk-mstf]

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,
Henry

[ABR002]

Nothing complex, there's just other data there, like sub skus or peoples names or regions, for now though I think this is fine.

[parry2k · Accepted solution]

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

[ABR002]

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.