Using Same Raw Data on Merged Queries with Multiple Queries

[jpalaci1]

I’m attempting to make an Excel file that utilizes Power Query. I previously completed this in Excel using lookups, replicating the data, and then Pivot Tables to it but the file grows to a size and uses way too much memory for some co-workers that are still on 32 bit versions of Excel.

1) I successfully merged the raw data (from Raw Data Example) to the mappings for Accounting Mapping and 2021 Mapping. I loaded my mappings and raw data as connection only and added to data model. This successfully creates my 2021 connections/scenario.

• Where I’m stuck is I need to replicate what I did for 2021 for 2020 and 2019 mappings then create a (I’m assuming) a Power Pivot across all to compare. I’d maybe even take it into Power BI to make visuals as well.
  • My ideal output would be the raw data going through the merged queries of the 2021 mapping, then 2020, and then 2019 (Accounting Mapping as well but it doesn't change). Then be able to compare if an Employee was moved from one department to the other how that affects different scenarios. Do I create something like I did in Excel that stacks the data of all merged queries on top of each other (do my VLOOKUPs in one tab each then took all three and put onto a new tab where the data is paste value) so I can make it into a single Pivot table?

• Power Pivot what I would need for this scenario to compare over multiple data sets? In Excel, I’d do my vlookups in each scenario in a tab, then since they all have the same standard layout, I’d paste into a table with all the data.

Thank you for any help!

Main File/Output: https://1drv.ms/x/s!AvgQCu95s84FbFggkg6CQb5xfqY?e=cEq4cN

Raw Data: https://1drv.ms/x/s!AvgQCu95s84Fa5Gn74kR8dt3yQU?e=8qFsgS

Mapping: https://1drv.ms/x/s!AvgQCu95s84Faqxe67IuAgNR3qE?e=NXVegm

[v-robertq-msft · Accepted solution]

Hi, @jpalaci1 

According to your description, I guess that you want to achieve the same table as the desired table you posted in the Power Query of Power BI, do you mean this?

If so, you can take a look at my steps to get the table and find if it’s useful:

1. Go to the Power query, click on the “Append query”, append the three tables:

Then go to the three tables, add a [Year] column for each, like this:

2. Click on “Merge query as a new table” and merge and expand table:

3. Do the Merge and append operation again, like this:

And you can get what you want.

You can download my test pbix file here

If this is not what you want, you can explain your requirement in detail with sample pictures so that we can help you in advance.

Thank you!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jpalaci1]

Thank you so much!