Modifying the MCode for first sheet instead of named sheet

rocaleon

New member
Joined
Feb 8, 2019
Messages
3
Reaction score
0
Points
0
Location
California
Excel Version(s)
2013 x64
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:

Code:
(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.


Code:
(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:

Code:
An error occurred in the ‘’ query. DataFormat.Error: External table is not in the expected format.
Details:
    NFB 01_13_2019 5001.xlsb
 
There's no concept of Activeworkbook or ActiveSheet

You could reference a specific sheet # such as
Code:
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

Code:
RangeValue = Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]
 
I see. I applied your fix like this but its throwing an error based on Kind?

Code:
(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:
Code:
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
 
this code works fine for me:

Code:
(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

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

View attachment blank2.xlsx
 
Last edited:
Back
Top