Forum Discussion

a68tbird's avatar
a68tbird
Resolver II
8 years ago
Solved

Referencing an Excel Sheet Without Using Name

Hello All,

  I'm trying to summarize a folder of Excel invoices, and have done this quite a few times before with an invoked function call, but have run into this problem for the first time, hoping you can help me out.  The data that I want is always on Sheet 1 of the Excel document, but that particular sheet has a different name in each file. How can I refer to Sheet 1 without using the name? Here is the particular line in my code:

 

 

(myPath,myFile) =>
let
    Source = Excel.Workbook(File.Contents(myPath&myFile),null,true),
    #"April 18 Support _Sheet" = Source{[Item="April 18 Support ",Kind="Sheet"]}[Data],

Thanks for your help.

 

Need Help
  • edhans's avatar
    edhans
    8 years ago

    Add this line. It will get the first sheet regardless of its name.

     

    = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]

    Do that right after the SOURCE line that shows all objects in the file. You'll probably need to delete the default NAVIGATION step Power Query does.

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    8 years ago

    Add this line. It will get the first sheet regardless of its name.

     

    = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]

    Do that right after the SOURCE line that shows all objects in the file. You'll probably need to delete the default NAVIGATION step Power Query does.