April 10, 2021
I've successfully used Power Query (with assistance) to consolidate all of my project schedule data. Each project has a tab and lists the staff and weeks they are scheduled. Also on each project tab is some information about each project that I would like to be able to pull into the consolidation. I also have a master staff table that lists their hourly rate which I also need to pull into the consolidation. How do I add these additional tables into the consolidation - how would I define the relationships so that power query recognizes that the Resource Name field in each project tab is the same as the Staff Name in the master staff table?
Thanks in advance for any help!
I am attaching an early version with test data before attempting PowerQuery.
July 16, 2010
Format each table that you want to import to Power Query as a proper Excel Table (CTRL+T or Insert tab > Table). You can then load those tables to Power Query and merge them with your consolidated table of data, if that's your desired end result.
April 10, 2021
Thank you so much - I'm getting very close to the needed solution. I was able to create 3 queries based on the 3 different table types in my workbook (Schedule, Resources, and ProjInfo). I was also able to Merge the Schedule and Resources query because they had the name in common. The last thing I need is to add the ProjInfo to this consolidation. The only thing in common between the Schedule and the Proj Info table is that they reside on the same tab which is the ProjectName. How do I bring / merge this data into the consolidation?