Sunday, August 29, 2021

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog