Forum Discussion

danextian's avatar
danextian
Super User
4 years ago

Hi ethanlsaul ,

 

If the sheet tab is always in the same order - like first in the order among all sheets and tables the workbook has, you can  use the sheet position instead of the sheet name with 0 being the first one in the order. Instead of the code below

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Book3.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

You can change it to

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Book3.xlsx"), null, true),
    Sheet1_Sheet = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

Source{0}[Data] tells power query to load the first table from Data column from the first step which is Source.

  • ethanlsaul's avatar
    ethanlsaul
    Helper I
    4 years ago

    Hi there,

     

    Here is what i have. The Orange Bold writing is the sheet name that will constantly change. This time it is 32, next week will be 33. it is the only sheet in the file. 

     

    let
    Source = SharePoint.Files("https://jnj.sharepoint.com/teams/OneStopShop", [ApiVersion = 15]),
    #"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/" = Source{[Name="J&J Kroger OvS.xlsx",#"Folder Path"="https://jnj.sharepoint.com/teams/OneStopShop/Shared Documents/Kroger/"]}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/"),
    #"UPC Matrix (32)_Sheet" = #"Imported Excel Workbook"{[Item="UPC Matrix (32)",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"UPC Matrix (32)_Sheet",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
    in
    #"Promoted Headers"