Power BI Variables

[hatstand]

Hi All,

I am looking to create a Query that holds multiple variables that are used in many Queries, rather than creating the variable within individual Querys. As an example, the Query could hold, Earliest and Latest Dates (to allow me to build a dynamic range calendar), Words that are replaced through all Querys etc etc

I have looked far and wide for the answer, so maybe it can't be done, or maybe it isn't a good idea. It would make administration easier, so before giving up I thought I would ask the experts.

Thanks.

 

[RoryA]

It sounds to me like a parameter table, although I wouldn't recommend mixing things like earliest/latest dates with lists of words to replace.

 

[hatstand]

Thanks for the reply, Rory. I take your point about mixing the data types in one column, I could split the data into columns. I have created a table with a few test variables, below. I can find the Row number in another Query using 'List.PositionOf(Var[Variable],"Month")'. In this example it returns '2'. What I am stuck on now is how would I use the returned Row number to give me the value in the variables tables. So give me Row 2 in the Variable Column.

Is this possible as it would make it far easier for staff to administer all the variables in one Query.

let
Var = {2}[Variable],
Source = #table(
type table[ Variable = Text.Type, Value = Text.Type ],
{
{ "Earliest Date", List.Min(Sales[Dates]) },
{ "Latest Date", List.Max(Sales[Dates])},
{ "Month", "January"}
}
),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)
in
#"Added Index"

 

[RoryA]

Have a look at Ken's article here: Building a Parameter Table for Power Query

I use it regularly for things like file paths and so on.

 

[hatstand]

I think that what would be best, instead of me trying to re-invent the wheel. Thanks for the helps, it's very much appreciated.