Hi Mynda,
I'm wanting to pivot the attached so that I have the Dimension Code as columns across the top, and the values (Dimension Value Code) under each Dim Code with the rows being just one for each Dim Set ID (see for example Dim Set ID 14 has 2 Dim codes, so I want one row for 14, with 2 of the 3 Dim Code columns populated with the Dim Value Code.
I have tried using Pivot columns in Power Query, but of course that just aggregates so I have the value 1 or 0 instead of "SI-OTA" for example.
How is best way to get this result?
This table of data will continue to grow, e.g. there will definitely be more Dim Set ID numbers, and Dim Value Codes, and it is possible there will be more Dimension Codes as well, so need the solution to allow for that.
Kind regards
Toni
Hi Toni,
It looks like you're using Power BI. This data layout isn't supported in Power BI. How are you wanting to use this data once it has been pivoted to the layout you want?
Mynda
Hi Mynda, sorry yes I'm using Power BI.
Have manually entered a table to show my desired output, see attached. I will use this via a relationship from the fact table to the DimSetID, and then I want to display the values for the Restype, region etc.
Thanks
Toni
Hi Toni,
Thanks for clarifying. See example file attached. Note: the vehicle names aren't the same as yours because it took me long enough to re-type out your example data as it was, so please consider these as placeholders. In future, please provide a small sample file to save me having to recreate your data to answer your question.
Note: You can only create relationships between columns that have unique values. In this case the SetID column.
Mynda
Thanks Mynda, sorry I didn't realise you would need to recreate. I learnt from the step in your return file that I just needed to go to Advanced under Pivot Column, and select "Don't Aggregate", and that solves it - thank you 🙂
