Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power Query Combine Excel data files with different tab names

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 appl...
Need Help
Tutorial Requests
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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"