Combine excel sheets from multiple workbooks in Power BI

Question

My requirement is, I need to combine excel sheets from multiple workbooks using Power BI.

Folders structure is like this Jan, Feb, Mar, April are the folder name, which consist of workbook name as Jan1, Jan12, Jan15, Feb1, Feb12 and so on and type 1, type 2 and type 3 are the sheet name in each workbook.

I would like to combine all type 1 sheet from Jan, Feb, Mar folder and I need to write a M function to repeat the process for all other types (i.e type 2 and type 3).

Could you please help me on this requirement using Power Query?

So far I tried to create a function to get the data from folder after then i doing manual steps to combine the data from workbooks.

(filepath)=> 
let
    Source = Folder.Files(filepath),
    #"Filtered Rows1" = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Hidden Files1",{"Name", "Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Source Type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Source Type"})
in
    #"Removed Columns"

What have you tried so far, and what problem(s) are you experiencing? Please edit your question to show your code, and any relevant error messages. — Olly

mll mll · Accepted Answer · 2019-06-22T13:06:30

Well, getting what you want is quite straightforward with Get and transform > From file > From a folder. screenshot

In the process you will specify you want sheet "type 1", giving you this at the end of the wizard : screenshot

Then, you would just repeat this for type 2 and 3.

Edit: if for some reason you don't want to use the 'From a folder' wizard, this all in one query does the job:

let
    Source = Folder.Files("C:\temp"),
    #"Filtered Hidden Files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Added Workbook" = Table.AddColumn(#"Filtered Hidden Files", "Workbook", each Excel.Workbook([Content], null, true)),
    #"Added Sheet" = Table.AddColumn(#"Added Workbook", "Sheet", each Table.PromoteHeaders( [Workbook]{[Item="type 1",Kind="Sheet"]}[Data]  , [PromoteAllScalars=true]) ),
    #"Renamed Columns" = Table.RenameColumns(#"Added Sheet",{{"Name", "Source"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Source", "Sheet"}),
    #"Expanded Tables" = Table.ExpandTableColumn( #"Removed Other Columns" , "Sheet",  Table.ColumnNames( #"Removed Other Columns"[Sheet]{0} ) )
in
    #"Expanded Tables"

Just adapt the source folder, and then duplicate this query and replace type 1 with type 2 ortype 3 to have the two other combined sheets.

Combine excel sheets from multiple workbooks in Power BI

1 Answers