Forum Discussion

Chema_Ortega's avatar
Chema_Ortega
Helper III
4 years ago
Solved

Combining Excel files from a folder

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

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

7 Replies

    • Chema_Ortega's avatar
      Chema_Ortega
      Helper III
      4 years ago

      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's avatar
        bcdobbs
        Community Champion
        4 years ago

        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!

         

         

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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