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 :
However, if you want to read only a Specific File, we need to click on the "Binary" as shown below :
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.