How to Read multiple Excel Files of Current and Previous Year from a Folder using Power Query

How to Read specific Excel Files of Current and Previous Years from a Folder using Power BI

Suppose we have the various files(.xlsx,.csv,.txt) in a Folder, from which we wants to read all the excel input files with name like "Sales_Orders_" of Current Year and Previous Year.

We can use the Following M-Query or Power Query to read all the Excel input files with name like "Sales_Orders_" of Current Year and Previous Year as follows :

let

    Source = Folder.Files("T:\T_Tech_Lab\PowerBI_Lab\DataSets\"),

    Filter_xlsx = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Name] <> "ds_Demo_Sample.xlsx" and [Name] <> "SalesDetails.xlsx" and [Name] <> "SampleOrdersDetails.xlsx.xlsx")),

    Select_Content_Name = Table.SelectColumns(Filter_xlsx,{"Content", "Name"}),

    cc_FileYear = Table.AddColumn(Select_Content_Name, "File_Year", each if Number.FromText(Text.Start(Text.AfterDelimiter([Name],"_",1),4))=Date.Year(DateTime.LocalNow()) then "CY" else if Number.FromText(Text.Start(Text.AfterDelimiter([Name],"_",1),4))=Date.Year(DateTime.LocalNow())-1 then "PY" else "Others"),

    Filtered_PY_CY_Files = Table.SelectRows(cc_FileYear, each ([File_Year] = "PY" or [File_Year] = "CY" )),

    Select_Content = Table.SelectColumns(Filtered_PY_CY_Files,{"Content"}),

    #"Filtered Hidden Files1" = Table.SelectRows(Select_Content, each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Order_Id", Int64.Type}, {"Order_Date", type date}, {"SalesCountry_Id", Int64.Type}, {"Supplier_Id", Int64.Type}, {"Cust_Id", Int64.Type}, {"Prod_Id", Int64.Type}})

in

    #"Changed Type"

Now the Query will generate a set of Helper Queries to read and process the excel Files as shown below :

When we want combine multiple files, we have to click on the double down arrows as shown below :

However, if you want to read only a Specific File, we need to click on the "Binary" as shown below :

Next you need to click on the "Table" as shown below :

Now the Power Query looks as follows :

let

    Source = Folder.Files("T:\T_Tech_Lab\PowerBI_Lab\DataSets\"),

    Filter_xlsx = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Name] <> "ds_Demo_Sample.xlsx" and [Name] <> "SalesDetails.xlsx" and [Name] <> "SampleOrdersDetails.xlsx.xlsx")),

    Select_Content_Name = Table.SelectColumns(Filter_xlsx,{"Content", "Name"}),

    cc_FileYear = Table.AddColumn(Select_Content_Name, "File_Year", each if Number.FromText(Text.Start(Text.AfterDelimiter([Name],"_",1),4))=Date.Year(DateTime.LocalNow()) then "CY" else if Number.FromText(Text.Start(Text.AfterDelimiter([Name],"_",1),4))=Date.Year(DateTime.LocalNow())-1 then "PY" else "Others"),

    Filtered_PY_CY_Files = Table.SelectRows(cc_FileYear, each ([File_Year] = "PY" or [File_Year] = "CY" )),

    Content = Filtered_PY_CY_Files{3}[Content],

    #"Imported Excel Workbook" = Excel.Workbook(Content),

    #"Filtered Rows" = Table.SelectRows(#"Imported Excel Workbook", each ([Item] = "Data")),

    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),

    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order_Id", Int64.Type}, {"Order_Date", type date}, {"SalesCountry_Id", Int64.Type}, {"Supplier_Id", Int64.Type}, {"Cust_Id", Int64.Type}, {"Prod_Id", Int64.Type}})

in

    #"Changed Type"

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------