I have a wide fact table, which is likely to get wider in future, and that I would like to transform into a much much less wide fact table.
The original wide one looks like so:
At the moment I am “cheating” by creating duplicate copies of the table (within Query editor view) to edit and append to achieve the outcome that I would like, which looks like so:
This approach of creating temp tables won’t be sustainable since the columns shown in the original raw data are likely to grow and it does not make sense to keep doing things the way that I am currently doing. There must be a quicker way.
I currently can’t figure out which some combination of transpose/pivot/unpivot to use to achieve the goal within one table (if that is possible).
I hope someone can assist with this.
Power BI file attached.
Power query - structure challenge.pbix (31.1 KB)
Thanks for your time.