Can I automatically change the Excel sheet name on Power Query?

[Anonymous]

Hello all,

I'm receiving weekly reports in Excel format from a client, and I set up a Power Automate flow that copies the attachment to a SharePoint Library, after renaming it.  My dashboard then pulls this data, and we are all happy. However, I noticed today that because the sheet is named differently every week, Power BI fails in the navigation step. I can rename the Excel workbook on Power Automate, but I cannot rename the sheet. I am curious whether I can do that on Power Query instead. See below, screenshots that show the applied steps and what happens in the navigation step. What I want to do is to change Source to something generic like "Sheet1". Is that possible?

Thank you,

[serpiva64 · Accepted solution]

Hi,

if you have only 1 sheet in  your workbook you can try deleting the navigation step and then expand Data column

Otherwise you can also try to modify the navigation step 

= Source{[Item="Sheet1",Kind="Sheet"]}[Data]

to a generic one:

= Source{0}[Data]

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Anonymous]

This was just what I was looking for regarding an issue I had with dynamic worksheet names!

[Anonymous]

Thanks, @serpiva64 

Your suggestion to change it to a generic one is ingenious and, of course, it worked! 

Thank you,