Importing an Excel Spreadsheet where the table name changes.

[asjam86]

Hi all,

Daily I recieve a spreadsheet which contains 1 sheet and only ever 1 sheet this will not change as it is coming from raw data inside a suppliers system. The sheet is called the date in YYYY-MM-DD format, with the previous days date on. (as this is historical reporting as to what went on yesterday), the actual file name remains the same each day IE "Daily Report.xls"

I use Flow to remove the attachment from my email and stick it into One Drive for Business (will be move to SharePoint ultimately). This works fine and it will overwrite the current file which is also OK. However the import it looking for data from a certain sheet IE 2016-08-21 (yesterdays report), todays being 2016-08-22, if I manually change the lookup it works fine again, but as I want this to just work without any manual intervention is there a way to get it to disregard what the sheet is called in the first instance and just carry on. Could I use a wildcard to just specify any sheet with 2016% ?

Thanks

Justin

[v-yuezhe-msft]

Hi @asjam86,

Based on my research and test, you can make some changes for the whole workflow. You can add the sheet of each day into a single Excel Workbook named "Daily Report.xls", then add a parameter of sheet name in Advanced Editor following the instructions in this similar blog . After that, you can import all the data of different days into Power BI Desktop and filter data of specific day to create reports.

Another method is to use VBA code to change the name for the current sheet to a fixed name, for more details, please review this similar article and video.

Thanks,
Lydia Zhang