Load to dynamic Sheet / Table name when importing from files using Power Query

[DavePerks]

I have a reporting need each month to import data from a few csv files and store the data in a workbook with each new month of data on a new sheet and table within the current workbook, is this possible with Power Query? I have no issues importing and transforming the data using Power Query, but I need a new table/sheet each month rather than updating or overwriting the current table/sheet.

For example I need the import to create this

I need a dynamic version of load table new worksheet adding the name of the current month

[mussaenda]

Hi @DavePerks ,

You can always import the first sheet of the excel regardless of its name.

Check this: Import first sheet of an Excel workbook into Power Query, regardless of its name – SQL Ryan

[DavePerks]

Thanks for taking the time to respond, but this is not the issue/goal I'm trying to resolve. I have no issues importing and manipulating my external sheets and CSV files in PQ, but rather the final sheet/table name that is output to Excel at the very end. I was looking for a way to dynamically create a new sheet/table (the PQ Name) but I'm yet to find a way. I also need to stay away from Macro/VBA due to company limitations/policy. I'm starting to think it's not possible using PQ.

[BA_Pete]

Hi @DavePerks ,

No, this isn't a Power Query thing

You can certainly use PQ to import, combine, and transform your CSV files into a normalised table, but it can't dictate the spawning or naming of new tabs. This is probably something that would need to be done via VBA.

My recommendation would be to combine your data in PQ, send the whole table to a 'Data' tab or similar, then create a single 'Report' tab that uses that data as a source but contains a single-select month slicer that the end user can use to choose the month they want to view (assuming that each month tab is expected to show the same visuals/metrics per month).

Pete