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

How to Read specific CSV 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 specific CSV [65001: Unicode (UTF-8)] input files with name like "Sales_Orders_" of Current Year and Previous Year as follows :

We can use the Following M-Query or Power Query to specific CSV 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_CSV = Table.SelectRows(Source, each ([Extension] = ".csv")),

    Select_Content_Name = Table.SelectColumns(Filter_CSV,{"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" )),

    #"Filtered Hidden Files1" = Table.SelectRows(Filtered_PY_CY_Files, 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 csv 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 :

Now the Power Query looks as follows :

let

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

    Filter_CSV = Table.SelectRows(Source, each ([Extension] = ".csv")),

    Select_Content_Name = Table.SelectColumns(Filter_CSV,{"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 CSV" = Csv.Document(Content,[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [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

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