powerquery - Combine Same Tab from Multiple Workbooks in One Power Query

Question

I have 32 Spreadsheets which all have the same tab "DashboardQuery". On the tab is a table set up with Metrics and values. The Table name is "QueryData". Below is an image of the 'QueryData' table.

I would like to create a power query that combines the data from each of these spreadsheets into one table which has the metrics as headers and values as rows. Below is desired output of power query.

The query currently is combining the multiple spreadsheets/tables into one and I have so far tried Transpose which gives me the correct layout (metrics as columns) but as there are 32 spreadsheets I end up with 32 of the same column headings.

The Query will pull files from a Folder and each fortnight further spreadsheets will be added from the same named areas eg. There will be multiple rows of the same name.

Answer 1

If all your files are in the same folder, you can load that folder using Home > New Source > File > Folder and choose the folder path where they are saved. Your query should look like this at this point (except you'll have more than three files):

At this point, select the double down arrow on the Content column to Combine Binaries and choose the worksheet DashboardQuery in the dialogue box.

Now all your data should be loaded in and labeled according to the file it came from:

The trick to getting it into the shape you want now is to pivot on the Metrics column. You'll want to use the Values column for the Values Column and select Don't Aggregate under Advanced options.

The result should now be in the format you want (you can remove the Source.Name column at this point if you want).