Forum Discussion

Tmk123's avatar
Tmk123
Helper II
3 years ago
Solved

Source (Excel) has dynamic sheet name

Hi All, 

 

My source is an Excel file.  There is only one sheet found within the Excel file and it is dynamic.  

 

File 1 has a sheet name of:  TRM215-1106217

File 2 has a sheet name of:  TRM215-1113231

 

 

I put a file into a folder daily, and Power BI takes the most recent file as its source.  I followed the steps here for reference:  https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711

(as you can see in the instructions found within the link, the source is a "folder").  

 

If I look at the Transform file section, this is what is listed there now:

 

= (Parameter3 as binary) => let
Source = Excel.Workbook(Parameter3, null, true),
#"TRM215-1" = Source{[Name="TRM215-1106217"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"TRM215-1", [PromoteAllScalars=true])
in
#"Promoted Headers"

 

The sheet name update is not allowing Power BI to refresh.   I see that it referencing:  [Name="TRM215-1106217"].   Is there a way to take the first sheet, regardless of the name?

 

I tried replicating previous solutions to this issue but have not succeeded.  Can someone please help me?  Obviously, I am new 🙄.  Thank you!!!

 

  • Replace the name reference

     

    #"TRM215-1" = Source{[Name="TRM215-1106217"]}[Data],

     

    with an index reference

     

    #"TRM215-1" = Source{0}[Data],

2 Replies

  • Replace the name reference

     

    #"TRM215-1" = Source{[Name="TRM215-1106217"]}[Data],

     

    with an index reference

     

    #"TRM215-1" = Source{0}[Data],