Forum Discussion
Help on Combining Data from Multiple Worksheets into a Single Worksheet in Excel
To test please put 2-3 of your files in some folder, let say c:\test. Use this connector
Select the folder on next step
Next click Transform Data
Power Query editor will be opened. Click on this button
Here select sample file or keep first one by default, and select the sheet / table with your data - they have to have the same name for all files
Result collects data from all your files in one table. You may do other transformations if necessary. Click here on File->Close and Load to. In your case perhaps it's better to select loading to data model
After that you may insert PivotTable with data model as the source. For the testing you may load result into Excel Table as well.
Okay, I have tried to follow your steps but it did not work so im not sure if I am doing something wrong. I have attached my screenshots for you to look at. Can you aslo let me know if my ribbons has everything I should have?
Thank you!
SergeiBaklan okay so I did that but I keep getting nulls...I don't get why because those files don't have any empty fields. This is what my issue was before.
You skipped few steps. Here
click on Edit, not on Combine (in my version this button is Transform Data). After that approximately as in my previous post, perhaps your user interface is slightly different.