How to pass Dynamic Year Parameter value into Source Connection in Power Query to extract Data

How to pass Dynamic Year Parameter into Source Connection in Power Query to extract Data for Multiple Years

Scenario :

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 a Parameter (Param_Year) for Year as text type which will be passed into to Source connection :

Step 3 :

Next pass the Year Parameter (Param_Year) 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_Year & "0101 to EndDate-"& Param_Year & "1231.xlsx"), null, true)

Step 4 :

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

dataset :

= (Param_Year as text) => let

    Source = Excel.Workbook(File.Contents("T:\T_Tech_Lab\PowerBI_Lab\DataSets\Orders\Sales_Orders_StartDate-" & Param_Year & "0101 to EndDate-"& Param_Year & "1231.xlsx"), null, true)

    in

        Source

Step 5 :

Next create a Table (ref_Data_Extractor) with Dynamic Years column (Data_Year) which will be passed (Invoked into the Function) in to the Parameter (Param_Year) 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_StartYear = Table.AddColumn(Source, "Data_Year", each if [YearName]="CY" then Date.Year(Date.From(DateTime.LocalNow())) else if [YearName]="PY1" then Date.Year(Date.AddYears(Date.From(DateTime.LocalNow()),-1))
else if [YearName]="PY2" then Date.Year(Date.AddYears(Date.From(DateTime.LocalNow()),-2))
else null),
#"Changed Type" = Table.TransformColumnTypes(cc_StartYear,{{"YearName", type text}, {"Data_Year", type text}})
in
#"Changed Type"

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

Now the result of the table is as per below :

= Table.AddColumn(#"Changed Type", "Inv_Fun", each fun_GetYearlyData([Data_Year]))

= 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 Years 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 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_StartYear = Table.AddColumn(Source, "Data_Year", each if [YearName]="CY" then Date.Year(Date.From(DateTime.LocalNow())) else if [YearName]="PY1" then Date.Year(Date.AddYears(Date.From(DateTime.LocalNow()),-1))

else if [YearName]="PY2" then Date.Year(Date.AddYears(Date.From(DateTime.LocalNow()),-2))

else null),

    #"Changed Type" = Table.TransformColumnTypes(cc_StartYear,{{"YearName", type text}, {"Data_Year", type text}}),

    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Inv_Fun", each fun_GetYearlyData([Data_Year])),

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

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