Forum Discussion
Can I automatically change the Excel sheet name on Power Query?
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,
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 !
3 Replies
- serpiva64
Solution Sage
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 !
Thanks, serpiva64
Your suggestion to change it to a generic one is ingenious and, of course, it worked!
Thank you,