Conditionally creating queries

[JimJaggers]

Is it possible to create a set of queries from a universe of possible queries based on what is available in a data source?

That is, say I have a spreadsheet that might contain tabs labled A, B, C, & D.  I want to create a seperate query for each tab that is present, but not create the query if the tab is not present.

First time poster, so sorry if my question isn't very clear.  Please feel free to ask me questions.

[JimJaggers]

I'd like to add one change to the most excellent solution that @BA_Pete pointed me towards.

In the article by Chris Webb that Pete linked to the problem being addressed was a missing file.  When the file is missing the query will error out (at least I assume so, I'm still fairly new to PBI) and the final test to decide whether to output the intened table or an alternate version of it is:

Output = if TestForError[HasError] then AlternativeOutput else #"Changed Type"

However, in my particular problem, the Excel file will always be there, but some worksheets may not be.  So the above test does not work as the query does not error out in the absence of a the expected worksheet, it just generates an empty table.

I found this, very similar test works for my case:

Output = if Table.IsEmpty(#"Changed Type") then AlternativeOutput else #"Changed Type"

Merely changing the test condition from testing for an error to testing for an empty table.

I'm not sure yet how an empty table will affect my model.  I'll test that soon and post an update for any that are interested.

[JimJaggers]

Just in case anyone is following this thread, and interested.  So far using an empty table, as opposed to an alternative table with a single dummy record, seems to work fine.  The model relationship to other tables still seems to hold and any measures against the now empty table just return nothing.

I'm still in the early stages of exploring the application I have in mind (basically a generic reporting model for data from an application where different users of the application will be interested in different output).  And this exercise was definitely useful for what I learned from it.  Thank you guys for your generous help.

[BA_Pete]

Indeed, this was the "crucial adjustment" from Ken Puls that I mentioned.

Glad it's worked for you in the end.

Pete

[BA_Pete · Accepted solution]

Hi @JimJaggers ,

You can't control the actual creation of queries per se, but you can have all the queries already in place and conditionally populate them using error handling i.e. you would set up the query with the source as 'Tab C', but then put an evaluation into the query that says "if the Tab C source returns an error (i.e. tab not present) then just run as blank" or similar.

Generally accepted technique on Chris Webb's blog here, but make sure to read the comments as Ken Puls adds some crucial adjustments to the method:

https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/ 

Pete