Append query only if previous queries find their data source

[Bartoli92]

Hello,

I would like to have your help on a problem that I've progressed on without managing to fully solve it.

My model uses a structure of queries to update.

To update, the model has lets say 5 sources of data (to simplify the problem to its core).

For each data source, there is :

1. a csv file : i type its name and path in the Excel model
2. a query preparing the data based on the csv file name, path and content

Then, there is a query "Append" that appends all 5 previous ones before matching it to another query and finally exporting the result in Excel.

Currently, the query "Append" is coded with a simple Append applied to all 5 queries above. To run, it requires all previous ones to run...

I would like though to be able to update any number/subset of queries from 1 to 5 (query 1, or query 3, or query 1 and 2, or query 1 to 4, etc.) without having to update them all. For this, i would only type the source file name and path of the queries i want to update and leave the other ones empty.

This leads to query errors that cascade to the final query : therefore I can only update them all at once.

I have found so far leads about using "TRY - OTHERWISE" functions in this forum but cannot seem to use it properly. Other topics talk about handling errors within the tables, but here, the error is at the query level. If a query does not find its source, I would like it to not be appended at all.

Any idea how to code a Table.Combine function with Query1 to Query5, and QueryAppend ?

Table.Combine(
   {
      try(#"Query1") otherwise null,
      try(#"Query2") otherwise null,
      try(#"Query3") otherwise null,
      try(#"Query4") otherwise null,
      try(#"Query5") otherwise null
   }
)

Thank you for your help,

[BA_Pete]

Hi @Bartoli92 ,

I think you probably need to create empty tables in each of your source queries to use as an alternative when a connection can't be made. Chris Webb writes about it here:

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

Make sure to read the comments from Ken Puls as well. He adds a critical update to this method.

TL;DR:

Create your Source step as usual.

Create an alternate source step that generates an empty tablewith the same columns as Source:

AlternativeOutput = #table(type table [Month = text, Product = text, Sales = Int64.Type]

You should then be able to create a 'try Source otherwise Alternative' step.

Pete