Forum Discussion

iqb's avatar
iqb
Icon for Helper III rankHelper III
3 years ago
Solved

Reuse Variables in all data sources

I have around 40 data sources (direct queries). In each query I have at leats 4 varaibles like,

 

let
    SomeVar1 = " AND (ColumnName1 IN (" & if Gender = "All" then "1,2" else "1",
    SomeVar2 = " AND (ColumnName2 IN (" & if Gender = "All" then "1,2" else "1",
    SomeVar3 = " AND (ColumnName3 IN (" & if Gender = "All" then "1,2" else "1",
    SomeVar4 = " AND (ColumnName4 IN (" & if Gender = "All" then "1,2" else "1",        
    Source = Sql.Database("Use varaibles here")
in
    Source

 

Now I need to copy paste these varaibles in each data source(direct query). Any way it can be keep in a function/method and call this function/method from M Query (note parameters are used in varaibles)?

 

The problem is that any change in varaiables, need to adjusted in 40 places.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  iqb ,

     

    According to your requirements, I think you can try Power Query's Query parameter function, which was developed in Power Query for your ability to modify parameter variables in bulk, you can choose to create parameters and define the type of value:

    Then you can use and fill the parameters in these forty queries to apply, and then if you have any needs to modify variable values in batches, you can go to "Manage Parameter" to modify to, for more exploration of the application scenarios of this parameter function, you can check this official document:

    Parameters - Power Query | Microsoft Learn

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  iqb ,

     

    According to your requirements, I think you can try Power Query's Query parameter function, which was developed in Power Query for your ability to modify parameter variables in bulk, you can choose to create parameters and define the type of value:

    Then you can use and fill the parameters in these forty queries to apply, and then if you have any needs to modify variable values in batches, you can go to "Manage Parameter" to modify to, for more exploration of the application scenarios of this parameter function, you can check this official document:

    Parameters - Power Query | Microsoft Learn

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.