Power query - ref cell for year

Question

I have inherited a power query sheet from someone that has to be done manually. I would like to automate the year in the query from a cell e.g if i put 2020 or 2021 it will update the year in the query (fyi there are loads i need to update at once)

e.g need to change 2020 to 2021 the cell is on range page cell A1

 #"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Sum of Pd 2 Pd 3", each if[PERIOD]="02 2020" or [PERIOD]="03 2020" then[VAL] else 0)

and the same on other queries that have this line all from 2020 to 2021

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"01 2020", type number}, {"02 2020", type number}, {"03 2020", type number}, {"04 2020", type number}, {"05 2020", type number}, {"06 2020", type number}, {"07 2020", type number}, {"08 2020", type number}, {"09 2020", type number}, {"10 2020", type number}, {"11 2020", type number}, {"12 2020", type number}}),

Thank you for any help you can give

horseyride horseyride · Accepted Answer · 2020-07-08T15:51:59

Give cell A1 a range name, like XVariable in excel and put your year in that range

Then in powerquery, in home ... advanced editor ... add a row that refers to that range name, similar to this

XVar = Text.From(Excel.CurrentWorkbook(){[Name="XVariable"]}[Content]{0}[Column1]),

Then change the hard coded years to use the variable instead, similar to:

#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Sum of Pd 2 Pd 3", each if[PERIOD]="02 "&XVar or [PERIOD]="03 "&XVar then[VAL] else 0)

Power query - ref cell for year

1 Answers