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

How to pass Dynamic Date Parameter into Source Connection in Power BI to extract Data for Multiple Years

Suppose we have the input files(.xlsx) of Sales Orders with Date Range in the file names as shown below:

Eg: Sales_Orders_StartDate-20200101 to EndDate-20201231.xlsx

Now we wants read the file names having Date range of different years. This can be done by the following way :

Step 1 :

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)

    in

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

in

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

    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}}),

    #"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"))

in

    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

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