Power Query Combine Excel data files with different tab names

[Anonymous]

Hello, 

i found information on combining a folder of excel files into a single power query that refreshes as files are loaded and taken out of the folder by creating a funtion in powerquery and applying the function to the folder of files. 

the issue i am having is that i am not sure what syntax to use to make the function use the first sheet (the only sheet) in the file regardless of the workbook name, as the names are often different and manually changing it would deffeat the purpose. 

the data is not in a table and i need to convert  it into a table afterwords. I appologize if this is a simple thing, i know mmy way around excel however i have no experiancne with code. 

here is the funtion i have now which only works for files with a worksheet named "NFB_01_13_19":

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

[v-danhe-msft]

Hi @Anonymous ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below code in query editor:

(para as text)=>
let
    a = Folder.Files(para){0}
in
    a

Enter your file path as parameter and it will choose the first sheet automatically:

Hope it could help you.

Regards,

Daniel He

[Anonymous]

I Tried this (which i picked up from another topic) but it errored out on all the files:

(filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath), null, true),
FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
#"Sheet" = Source{[Name="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet"),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{XXXXXXXXX})
in
#"Changed Type"