How to pass Dynamic Date Parameter values into Source Connection in Power Query to extract Data

First create a source dataset(ref_Data_Connector) connection to the one of the source file:

Step 2 :

Next create two Parameters for Start Date(Param_StartDT) and End Date(Param_EndDT) as text type which will be passed into to Date Range in Source connection :

Next pass the sample values to Parameters as 20190101 for Param_StartDT and 20191231 for Param_EndDT.

Step 3 :

Next pass the Parameters (Param_StartDT; Param_EndDT) into to Date Range part of the Connection string :

Source=Excel.Workbook(File.Contents(“T:\T_Tech_Lab\PowerBI_Lab\DataSets\Orders\Sales_Orders_StartDate-” & Param_StartDT & ” to EndDate-“& Param_EndDT & “.xlsx”), null, true)

Step 4 :

Next create a Function (fun_GetDateRange) by right clicking on Source (ref_Data_Connector)

dataset :

= (Param_StartDT as text, Param_EndDT as text) => let

Source = Excel.Workbook(File.Contents(“T:\T_Tech_Lab\PowerBI_Lab\DataSets\Orders\Sales_Orders_StartDate-” & Param_StartDT & ” to EndDate-“& Param_EndDT & “.xlsx”), null, true)

Source

Step 5 :

Next create a Table (ref_Data_Extractor) with Dynamic Date Range columns (Start_Date ; End_Date) which will be passed (Invoked into the Function) in to Parameters in the Source Connection.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45Wco5UitWJVgqINITSRkqxsQA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearName = _t]),

cc_StartDate = Table.AddColumn(Source, “Start_Date”, each if [YearName]=”CY” then Date.ToText(Date.StartOfYear(Date.From(DateTime.LocalNow())),”yyyyMMdd”) else if [YearName]=”PY1″ then Date.ToText(Date.AddYears(Date.StartOfYear(Date.From(DateTime.LocalNow())),-1),”yyyyMMdd”)

else if [YearName]=”PY2″ then Date.ToText(Date.AddYears(Date.StartOfYear(Date.From(DateTime.LocalNow())),-2),”yyyyMMdd”)

else null),

cc_EndDate = Table.AddColumn(cc_StartDate, “End_Date”, each if [YearName]=”CY” then Date.ToText(Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),”yyyyMMdd”) else if [YearName]=”PY1″ then Date.ToText(Date.AddYears(Date.EndOfYear(Date.From(DateTime.LocalNow())),-1),”yyyyMMdd”)

else if [YearName]=”PY2″ then Date.ToText(Date.AddYears(Date.EndOfYear(Date.From(DateTime.LocalNow())),-2),”yyyyMMdd”)

else null),

#”Changed Type” = Table.TransformColumnTypes(cc_EndDate,{{“YearName”, type text}, {“Start_Date”, type text}, {“End_Date”, type text}})

#”Changed Type”

Step 6 :

Next we need to Invoke the Function (fun_GetDateRange) on this table ref_Data_Extractor.

Now the result of the table is as per below :

= Table.AddColumn(#”Changed Type”, “Inv_Fun”, each fun_GetDateRange([Start_Date], [End_Date]))

= Table.ExpandTableColumn(#”Invoked Custom Function”, “Inv_Fun”, {“Data”, “Item”, “Kind”}, {“Inv_Fun.Data”, “Inv_Fun.Item”, “Inv_Fun.Kind”})

When we invoke the Function, it will Invoke all the Date Ranges and Pass it to the Parameter in the Source connection and extracts all the Files.

Next you can extract all the Years data Tables and do the required Transformations. The following are all the Power Query steps involved in the dataset ref_Data_Extractor.

let

else if [YearName]=”PY2″ then Date.ToText(Date.AddYears(Date.StartOfYear(Date.From(DateTime.LocalNow())),-2),”yyyyMMdd”)

else null),

else if [YearName]=”PY2″ then Date.ToText(Date.AddYears(Date.EndOfYear(Date.From(DateTime.LocalNow())),-2),”yyyyMMdd”)

else null),

#”Changed Type” = Table.TransformColumnTypes(cc_EndDate,{{“YearName”, type text}, {“Start_Date”, type text}, {“End_Date”, type text}}),

#”Invoked Custom Function” = Table.AddColumn(#”Changed Type”, “Inv_Fun”, each fun_GetDateRange([Start_Date], [End_Date])),

#”Expanded Inv_Fun” = Table.ExpandTableColumn(#”Invoked Custom Function”, “Inv_Fun”, {“Data”, “Item”, “Kind”}, {“Inv_Fun.Data”, “Inv_Fun.Item”, “Inv_Fun.Kind”}),

#”Filtered Rows” = Table.SelectRows(#”Expanded Inv_Fun”, each ([Inv_Fun.Kind] = “Sheet”) and ([Inv_Fun.Item] = “Data”)),

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

#”Expanded Inv_Fun.Data” = Table.ExpandTableColumn(#”Removed Other Columns”, “Inv_Fun.Data”, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”}, {“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”}),

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

#”Changed Type1″ = Table.TransformColumnTypes(#”Promoted Headers”,{{“Order_Id”, type any}, {“Order_Date”, type any}, {“Shipping_Date”, type any}, {“SalesCountry_Id”, type any}, {“Supplier_Id”, type any}, {“Cust_Id”, type any}, {“Prod_Id”, type any}, {“Regon_Name”, type text}, {“Country_Name”, type text}, {“Product_Name”, type text}, {“Customer_Name”, type text}}),

FilterOutHeadings = Table.SelectRows(#”Changed Type1″, each ([Order_Id] <> “Order_Id”))

FilterOutHeadings

The last step FilterOutHeadings will filter out the headings of the subsequent files loaded.

Final Result :

Once we extract the Data from all the Input files and did the Transformation, next we can reference that dataset in other Dataset “Sales Orders Data” and can perform other required operations.

Notes :

Please note that, you can use the logic explained in this article, to dynamically pass the Year or Date Range values into Parameters via Function to make the Connection Strings dynamic, when we are extracting the data from OData Feeds or API Connection strings.

——————————————————————————————————–

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

——————————————————————————————————–

Source link

How to pass Dynamic Date Parameter values into Source Connection in Power Query to extract Data | Power BI Blog

Be the first to comment

Leave a Reply Cancel reply