Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Bartoli92
New Member

Append query only if previous queries find their data source

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,

 

1 REPLY 1
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors