Forum Discussion

cpartridge's avatar
cpartridge
Copper Contributor
Feb 01, 2023

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, see screenshot directly below.

One table (Table A) is formatted in the following way, with department as a column header. This works well for created the above end result,

Table A

The other table (Table B) I'm working with, however, has the information in a different format, with each department as a header, and then counts given within the rows,

Table B

I was just trying to use relationship with the power pivot data model, but I think that doesn't work for this problem.

 

Therefore, I think I may need to use Power Query. I would pull in Table B to create a new column "Department." Then, I would pivot all the columns that are a departments into that new column "Department."

 

Let me know if that's feasible, as I'm having trouble envisioning this working. Furthermore, I haven't figured out how to create columns and pivot columns in Power Query yet, so it'd take me a bit of time to figure this out on my own, and it'd be good to know that this is the best way to do it.

 

I did a brief check if there were any similar posts to this, and couldn't find anything. Thanks!

Excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 02, 2023

    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:

    • cpartridge's avatar
      cpartridge
      Copper Contributor
      Feb 02, 2023
      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.

Resources