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

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