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 consists 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 an 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 the below function to get the data from folder after that, I do 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"
Finally, I got the solution, added below
let
Source = Folder.Files("C:/temp"),
#"Added Workbook" = Table.AddColumn(Source, "Workbook", each Excel.Workbook([Content], null, true)),
#"Added Sheet" = Table.AddColumn(#"Added Workbook", "Sheet", each Table.PromoteHeaders( [Workbook]{[Item="Aug Data",Kind="Sheet"]}[Data] , [PromoteAllScalars=true]) ),
#"Filtered Rows2" = Table.SelectRows(#"Added Sheet", each ([Extension] = ".xlsx")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.StartsWith([Name], "~$")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each ([Folder Path] = "C:/temp")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows4",{{"Name", "Source"}}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Renamed Columns", each [Attributes]?[Hidden]? <> true),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Hidden Files1",{"Source", "Sheet"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.EndsWith([Source], "xlsx")),
#"Expanded Tables" = Table.ExpandTableColumn( #"Filtered Rows" , "Sheet", Table.ColumnNames( #"Filtered Rows"[Sheet]{0} ) ),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Tables", each ([Chain] <> null)),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows1", each [Postcode] <> null)
in
#"Filtered Rows5"
