Forum Discussion

QZ's avatar
QZ
Helper I
2 years ago

(M code)How to extract multiple sheet data from Excel which in multiple folder by Power Query(noDAX)

The budget Excel file save in folder every month, the folder name named by year and month, for example: 202401, 202402, ......,
(refer bellow picture, for the purpose of simplification, only save the excel file in 202401 and 202405 folder)

In the Excel file, each department has each sheet, for example: HR, IT, ......, 

The budget amount be allocated by quarter, for example, Q1, Q2, Q3, Q4,

The budget be divided into category and sub category, for example: category: Fixed costs; sub category: variable costs, labor costs, fixed costs(except labor costs),

My purpose is extracting all sheets data in excels which under D:\DeptBudget folder by Power Query(not DAX), final get table data(pls refer bellow picture which in green rectangular frame), it would by much appreciated if anyone afford the accomplishing method.

* Refer bellow video: Combine Excel Worksheet Data , I can extract the raw data which under D:\DeptBudget folder excel files(just like get data from folder in Power BI Desktop), but that's not what I want. I can process the budget amount by catetory and sub category for individual department sheet, but have no idea to multiple department sheets.

 

 

Need Help

5 Replies

  • QZ's avatar
    QZ
    Helper I
    2 years ago

    I think if use method of getting data from folder, it's hard to process the raw data as request, is it?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi QZ ,
    Based on your description, you can try to perform the following steps:
    First, choose get data from folder

    Then,choose the target folder and open and choose the combine and transfrom data

    Click ok

    Open the advanced editor and paste the Mcode

     

    let
    Source = Folder.Files("C:\Users\yourcomputername\Desktop\DeptBudget"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Custom.Name", "Custom.Data"}),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6"})
in
    #"Expanded Custom.Data"

     

    Final output

    Note that you can format all the data before merging it, so that you can get the data into the desired format faster.
    How to Merge [Combine] Multiple Excel FILES into ONE WORKBOOK (excelchamps.com)

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

     

    • QZ's avatar
      QZ
      Helper I
      2 years ago

      thank for your reply!

      the table format of your final output is not what I want, my purpose is get bellow format table:

  • QZ's avatar
    QZ
    Helper I
    2 years ago

    is it possible to inside cell then directly update cell value?
    refer bellow pic, cell value is a table, can change value from red rectangular frame to green rectangular frame directly by Power Query?