Power Query Auto Adding Sheets

Question

I have multiple excel sheets in a folder. I can easily reference this data and pull it all into one sheet using Power Query. However I would like to have a function (or something that will auto update when a new Excel file is added to the directory in File Explorer) that then takes the newly added Excel Workbook and adds it as a new sheet along with deleting some columns.

Answer 1

Hey Joe

I do understand +/- what you want to achieve. To set the correct expectations:

• I provided the link to help you but never used that technic (doesn't frighten me though)
• I don't have your data and a clear understanding of the end goal
• This is a specific dev. assignment/task that requires investment + time
• …

Hope you will understand it's up to you to do it if you believe the investment makes sense from a bus. point of view.

Although I'm not 100% but "However, I would want Column A, Column B, and Column C all on different sheets. For Example Column A on Sheet 2, Column B on Sheet 3, and Column C on Sheet 4" probably requires 3 separate (but similar) queries as you can't Load To… the same query output to different sheets

Hope this helps & makes sense

Answer 2

Thanks for the response Lz. Yes I am using Power Query in Excel 2016. I'll see if I can explain better using the example that you provided. In that example when both statements are turned to true I would like to create a new sheet. However, I would want Column A, Column B, and Column C all on different sheets. For Example Column A on Sheet 2, Column B on Sheet 3, and Column C on Sheet 4.

Answer 3

Hi Joe

There are implications in your request I hope you understand… Assuming you run 2016 see Use VBA to automate Power Query in Excel 2016