combine multiple files into one-each file is same but needs clean up, then refresh daily

[electrobrit]

I have multiple daily files (example I'll call- "Daily-2016-1-1.xlsx") Daily File Example

 there are 2 sheets on each file, I only need Sheet1 or "data"  they live in a synced OneDrive for Bus folder (synced from sharepoint) (ex. Folder name: Help Desk Tickets) . This folder is refreshed with a daily file that is the same format day after day. 

I am new to Power BI and have tried a few things to consolidate like a months worth of daily files. 

I have tried to get data from folder combine binaries and I got error message:  The Input DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
Binary

I don't know if it's because they need formatting in order to combine or what.  However, after lots of research, I discovered to create the custom column 

"For Excel files from a folder you must Add a Custom Column and use the Excel.Workbook function.

=Excel.Workbook([Content])"

So I could now click on Table (on the top row which the first daily file) and clean up a file -remove the top rows, remove columns I don't need, promote headers, etc.

I was hoping all the other files in that folder will follow the steps in the query and voila.

However, it doesn't work. I am missing something.

I tried a query where it would sort the daily files by date, take the first one, apply the steps to format, then I can manually delete that one from the Onedrive folder and go back into the query and refresh. It again sorts, takes the top row (which is the next day because I've deleted the other one), then it does all the steps but ... replaces the first day's file not add to it. I really don't want to have to manually delete the file anyway. How does it find the new file?

I can not figure out how to combine all the files in a folder, apply the necessary steps to ormat each file or "clean them up", then do my visualizations, then every day refresh the folder with the new daily. It will come in the same format to the same folder.

Can someone help me with the steps and what I am doing incorrectly? 

Thank you in advance

[Anonymous · Accepted solution]

I thought this would be a simple 2-click exercise, but I also get similar issues combining multiple Excel (but not CSV?!) files.  There's a thorough discussion and workaround here: https://blog.crossjoin.co.uk/2016/09/19/loading-data-from-multiple-excel-workbooks-into-power-bi-and...

[electrobrit]

thank you @Anonymous this is exactly the help I needed. This was my first project in Power BI and after 2 days of trying to figure this out, your help was very helpful and intuitive.  
Thank you very much for leading me in the right direction!

[ImkeF]

You need to transform your cleanup-&transformation steps into a function that you then call from a column:

https://www.powerpivotpro.com/2015/07/consolidated-worksheets-with-power-query/

But if you just need to promote the headers, this might be simpler:

Table.PromoteHeaders(Excel.Workbook([Content]))"