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.