Modifying the MCode for first sheet instead of named sheet

[rocaleon]

Hello.
I followed this blog post to pull data using power query from multiple excel sheets, but am running into an issue.
It works perfectly except when the sheets have different names. All the files i am using use a date stamp for the sheet name, I am looking for help figuring out how to reference the active worksheet instead of a specified sheet. I tried implementing solutions i found in the comments and other forums but haven't been able to get it to work. And help would be greatly appreciated.

This is the code i was using that worked for the specific sheet that i got from following the blog post:

(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    #"01_13_1" = Source{[Name="01_13_2019"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"01_13_1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{XXXXXXX})
in
    #"Changed Type"

and this is what i tried to get to work but i don't know enough about coding yet to get it to work.

(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    #"A" = Source{[Name="Activeworkbook.ActiveSheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"A"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{XXXXXXX})
in
    #"Changed Type"

but it throws this error:

An error occurred in the ‘’ query. DataFormat.Error: External table is not in the expected format.
Details:
    NFB 01_13_2019 5001.xlsb

 

[horseyride]

There's no concept of Activeworkbook or ActiveSheet

You could reference a specific sheet # such as

Source = Excel.Workbook(File.Contents(filepath), null, true),
FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]

or you could reference a specific excel range that contains some formula that evaluates to what you want to reference, here the content of named range RangeName

RangeValue = Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]

 

[rocaleon]

I see. I applied your fix like this but its throwing an error based on Kind?

(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Firstsheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Firstsheet"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{XXXX}})
in
    #"Changed Type"

It Says:

An error occurred in the ‘’ query. Expression.Error: The field 'Kind' of the record wasn't found.
Details:
    Name=NFB 01_13_2019
    Data=Table

 

[horseyride]

this code works fine for me:

(filepath)=>
let Source = Excel.Workbook(File.Contents(filepath), null, true),
FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(FirstSheet , [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}})
in #"Changed Type"

invoked with

let Source = Function("C:\TEMP\blank.xlsx")
in Source

View attachment blank2.xlsx