I would like to replace existing functioning VBA code with Power Query to do the following:
Process all Excel files in a folder and import into pivot table format.
The Excel data has row labels in column A and values in columns B to M. The row headers in B to M are years 2000,2001,2002 etc. I currently unpivot this data via VBA and import it into pivot table format. In a hidden column to the right I have a unique identifier for the row so if future versions of the excel model have lines added or deleted, I can match them up in PowerPivot.
In PowerPivot, I compare the results of many different scenarios creaed in Excel.
I need to import the following:
1) Scenario Name (it is listed in a cell in the excel file)
2) Year,RowId and value for each row columns B to M
Also, the years need to be "unpivoted"
I am a relative novice in Power Query, but am familar with the unpivot function the import all files in a folder menu item.
Any ideas to get me started are appreciated.
Process all Excel files in a folder and import into pivot table format.
The Excel data has row labels in column A and values in columns B to M. The row headers in B to M are years 2000,2001,2002 etc. I currently unpivot this data via VBA and import it into pivot table format. In a hidden column to the right I have a unique identifier for the row so if future versions of the excel model have lines added or deleted, I can match them up in PowerPivot.
In PowerPivot, I compare the results of many different scenarios creaed in Excel.
I need to import the following:
1) Scenario Name (it is listed in a cell in the excel file)
2) Year,RowId and value for each row columns B to M
Also, the years need to be "unpivoted"
I am a relative novice in Power Query, but am familar with the unpivot function the import all files in a folder menu item.
Any ideas to get me started are appreciated.