Excel cell value as parameter in Power Pivot

[Tom83]

Hi guys,

I'm trying to set up a parameter in Power Query based on an Excel named cell called YTD_date. The reason I'm using a parameter is because this one can be used to add a conditional column to my dataset. This is what I've done up so far:

Added the cell to PowerQuery:

let
Source = Excel.CurrentWorkbook(){[Name="YTD_date"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"31-12-2021", type date}})
in
#"Changed Type"

Now I want to setup the Parameter but it doens't allow me to select the query, any idea why? Or would there be an easier solution to fix this? I just want to use an input cell to use in a conditional column to calculate the amounts YTD on record level, I don't want to filter rows based on this cell-value because I need the other rows as well.

Thx!

 

[GraH]

I'm thinking the query needs to return a list.

You can also use the query result (a value) as a variable in other queries, by simply using the Query name.