Relating Column Headers to Table Rows

[Anonymous]

Hello,

I've done some looking and I think there is likely a trick with transpose, but I think by transposing the data I would just create a similar problem for myself, so looking to see if there is an option to bridge or something else which I can do to achieve the desired outcome. Essentially, I have a Sharepoint list where Column 1 contains a Unique ID and then there are over 40 other column headers which represent a code. 

I also have a table in which the codes are listed in Column 1, and the description for the code along in the row.

There is also another table relationship (1-1) where the Unique IDs have a full description associated with them.

What I want to be able to do is describe things by the codes (how many times do they appear, etc.), but also be able to describe things by event type and see if there is a relationship there. There is another 'CC Codes' column which contains comma separated values which I have another table for as well. 

Hope that makes some sense, I'm really looking for ideas on how to approach this and keep those relationships. Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Yes, if transposing for the whole table will break things. How about trying to select those columns and do a unpivot? The result would be something like this:

Unpivot columns - Power Query | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group