PDA

View Full Version : Parameter to switch a query 'on' or 'off' without creating errors



JuanSombrero
2016-10-07, 08:00 AM
Hi all,
I have a ‘budget query’ existing of about 30 steps to shape my budget table for further analysis. There are a number of other queries in the sheet. In the end, they are all combined into one ‘master data’ table using the Table.Combine function (where I combine budget with actuals for instance).
In my Excel sheet, I have a parameter which says ‘include budget?’. When this is set to ‘Yes’, I want the budget figures to be included into the final data (so the budget query should be ran for the data to be included in the master data). When it’s ‘no’ I don’t. I did easily manage to capture the parameter into Power Query and prepare it for further use (using drill down), so no issues there.

So basically what I am looking for now is a way to start my budget query by saying:
If the “include budget” parameter is yes, do the following 30 steps, if it’s no, do nothing…without creating any errors. Any suggetions on how to achieve this?
Thx,
Juan

Comfy
2016-10-11, 11:49 AM
Create two steps one for the UNION of ALL Queries and another for ALL minus the Budget Query.

Then use an if statement to return the relevant step.



let
//Previous Steps
AllQueries = Table.Combine({#"Budget Query"}, {#"Actual Query"}, {#"Other Query"})
ExclBud = Table.Combine({#"Actual Query"}, {#"Other Query"})


Output = if Text.Lower(inclBudget) = "yes" then AllQueries else ExclBud
in
Output