Forum Discussion
Two tables with same data but different formatting. Power Query or Power Pivot?
Hello, I'm looking for help with being pointed in the right direction for this problem I've been facing. The end result I'm looking to accomplish, is create Pivot Table & Dynamic Map together, s...
cpartridge You need to UNpivot the second table. The site in the link below would be a good place to start learning about PQ.
https://exceloffthegrid.com/power-query-introduction/
Work your way through the first page. Almost at the end you'll see this:
etc.
Post number 12 deals with unpivotting data in particular.
Once you master that you can turn the blue table into the green one. The only thing left is renaming the columns Attribute and Value.
I'm not really sure what you want to do next, but I guess you want to load both Table A and the "green one" to the DM, relate the Departments and then create your Pivot table. But, since neither of the two tables contains a column with unique departments you would have to create a so-called bridge table between them. A table that lists all existing (unique) department names. Based on your example that would be something like this:
Hi thank you for your response. Since posting, I did watch a few more videos about unpivot in Power Query, and gave it a try. I was then able to figure out how to merge the two tables in power query, vertically if I recall, as both tables has the same columns headers at that point. Then, I loaded into the sheet and was able make a pivot table from the PQ table.