Combining Excel files from a folder

[Chema_Ortega]

Hello everybody,

I hope you are well!!

I am trying to combine some Excel files from a folder. Each Excel file has an indetermined number of Excel sheet tabs. Each Excel is a Project, and in the sheet Tabs we find the Subprojects... Is there anyway to combine these files with Projects and Sub-projects in Power BI?

Should I better convert each tab of each excel file in another excel file? Would that be a wiser solution?

Thanks a lot! Best regards,

Chema Ortega

[v-kkf-msft]

Hi @Chema_Ortega ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

Hi @Chema_Ortega ,

Suppose you have a table containing projects and subprojects information, where the project names are the same as the excel file names\workbook names, like this. 

Then you can try to create a custom column and import the corresponding table.

Custom = let
    Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\Project Files\" &[Projects] & ".xlsx"), null, true),
    #"Subproject A1_Sheet" = Source{[Item=[Subprojects],Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Subproject A1_Sheet", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Then you can extend the column.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chema_Ortega]

Thanks soooo much!!!!!!! Great helpppp!!!

[Daryl-Lynch-Bzy]

Hi @Chema_Ortega  - please watch the following video help you consider your options:

Importing Multiple Excel - Part 1 

Importing Multiple Excel - Part 2 

The trick is to filter to the Tables or Sheets that need to be imported.

Another consideration is to use an Custom Function to import each file.

[Chema_Ortega]

Hi Daryl... But what I am trying to do is much more complex that what Ruth explains... I want to combine some Excel files that each one has some tabs with information. Anyway, thanks a lot!

[bcdobbs]

I've literally just watched this which might help. https://lnkd.in/gi4CVABu

You'd have to combine it with the from folder function power query builds for you.

If you have some demo files I'd give it a go!

[Chema_Ortega]

Thanks a lot, Ben. I saw the video, but it does not helps to fnd the solution. Thanks a lot anyway. 

How can I send the files?