My project sends a daily file, which goes through a Power Automate process and saved to my departments SharePoint site. The Excel worksheet name of the file changes based on who sent the email. The file name, data, structure, etc... are identical, except for the worksheet name. Different worksheet tab names cause errors in the power bi - power query process.
My question: Is there a way to edit my query to account for different worksheet names dyanmically without manual intervention everytime it changes?
Is there a way to update the Excel Tab to Sheet1 if named something else in Power Query?
The normal comes as "Sheet1" and everything else is a variation of "28 DAY FORECAST." The variations include different spacing, or underscores.
= (Parameter1 as binary) => let
Source = Excel.Workbook(Parameter1, null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], // Item="Can change in automated process"
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"
