How to handle missing table in append

Power Query
1

Hello People,

I am trying to find ideas on how to handle the following situation in power query

Imagine there are 4 tables which are sourced from different Excel Workbooks and elaborated individually before being appended into a unique table

My problem is that, one of the tables is not always present, hence, I would like to have flexible code so that, when there’s a missing table, the flow goes on and appends only the available ones instead of stopping and returning an error

So far I have been keeping a file in the folder containing only the headers and no other rows but I assume there must be a better option for this

I am looking for general ideas and techniques about how to approach this kind of situations

I have just attached 4 dummy files with the table and the missing table problem file which contains the power query consolidation

Thanks a lot for your precious help

Antonio
Missing Table Problem.xlsx (17.8 KB)
Order1.xlsx (12.1 KB)
Order2.xlsx (12.1 KB)
Order3.xlsx (12.1 KB)
Order4.xlsx (12.1 KB)

2

Hi @Anferrig,

Here’s a way to deal with such a case.
I’ve added a query called: EmptyTable with this code:
image

Next I restructured the code in one of the samples provided.

IsMissing False
IsMissing False1289×302 21.3 KB

Changed “Sheet1” to “Sheet” to force an error

IsMissing True
IsMissing True1307×279 17.1 KB

and that fixes the Result
Result

You will have to apply this if-then-else structure in all of the pre- append queries.
I hope this is helpful.

4 Likes
3

Hello @Melissa

thanks a lot for your help

I will have the opportunity to work on this only tomorrow as I am off today

Will let you know if I manage to apply your solution soon

Have a nice day

Antonio

4

Thanks a lot for your help Melissa!

Your solution helps me a lot :slight_smile: