Importing Excel Files from a Folder

[khawarameer]

Hello,

I am trying to import 05 excel files (.xlsx) from a folder in to power query. However when i use combined binaries it gives me error that said files are not in proper excel format. Further when it try to import individual file power query shows 3 sheets in the file where as i have made only one sheet in all these files with common name "Consolidated". can any one help however import all excel files.

Regards

KAM

 

[ImkeF]

Instead of expanding, you can add a custom column that retrieves the content differently: Excel.Workbook([Content])
The reason why you're seeing more objects than sheets when opening the file is because Power Query identifies Excel-objects separately: If a table sits in a sheet, you Power Query would identify 2 objects: 1 sheet and one table. If there is a named range as well somewhere in the sheet, this would be shown as a separate object as well.

 

[khawarameer]

Instead of expanding, you can add a custom column that retrieves the content differently: Excel.Workbook([Content])
The reason why you're seeing more objects than sheets when opening the file is because Power Query identifies Excel-objects separately: If a table sits in a sheet, you Power Query would identify 2 objects: 1 sheet and one table. If there is a named range as well somewhere in the sheet, this would be shown as a separate object as well.

Hello ImkeF,

This is the code that is giving me error can you guide me where should i add the custom column. The command you suggested is already in the code. Please Help.

let
    Source = Folder.Files("C:\Users\khawar\Desktop\TELLER_TR"),
    #"Combined Binaries" = Binary.Combine(Source[Content]),
    #"Imported Excel" = Excel.Workbook(#"Combined Binaries")
in
    #"Imported Excel"

 

[ImkeF]

like this:

let
    Source = Folder.Files("C:\Users\khawar\Desktop\TELLER_TR"),
    #"Imported Excel" =Table.AddColumn(Source, "New", each Excel.Workbook([Content]))
in
    #"Imported Excel"

 

[khawarameer]

like this:

let
    Source = Folder.Files("C:\Users\khawar\Desktop\TELLER_TR"),
    #"Imported Excel" =Table.AddColumn(Source, "New", each Excel.Workbook([Content]))
in
    #"Imported Excel"

Thanks ImkeF,

It worked fine.

Best Regards

KAM