If Table does not exist, then do not append it.

Joined
Jan 11, 2018
Messages
2
Reaction score
0
Points
0
So here's the situation: I pull a bunch of data out of our database through various queries and mash them all together in Power Query. For one dataset in particular, there are days where the data I need is not FTP'd to our database by the service provider until the next day. To get around this, I go to the service provider's reporting front-end and download a copy of yesterday's data, and use a From Folder query to grab that file, perform the necessary transformations on it, and append it on the end of the dataset I grab using my SQL queries.

This works great, unless our service provider actually gets us the data on time. Then what I want to do is not download the file from their reporting front-end and just let the SQL take care of it. But my problem is that, because my query is looking for a file to append onto the end of the SQL dataset, if it doesn't find one, it throws an error and it scuttles the whole query.

So, what I want to find out is how to alter either my From Folder query so that it won't error out if there's no file to pull, or how to alter my SQL query so that if there's an error with the From Folder query, it won't try to grab it and append it.
 
Hey there,

So yes, this is possible. Here's what I would do:
-Set up a query for SQL
-Set up a query for Yesterday

Now set up a new query that does this:
-Reference SQL
-Append Yesterday

This is two distinct steps, don't start by just appending them in the Source step.

You've already done that I'm sure. But here's the trick. When you append the data, wrap it in the try function. This has a syntax of "try <action> otherwise <alternate>" and is very similar to Excel's IFERROR. So the M code would look like this:

Code:
let    Source = SQL,
    #"Appended Query" = [B]try[/B] Table.Combine({Source, Yesterday}) [B]otherwise Source[/B]
in
    #"Appended Query"

Source is the name of the step that contains the original (SQL) table.

Be aware that your query to retrieve the file may still show an error. If that stops things, do a try/otherwise at the end of it to return a null or something (try <previous step name> otherwise null). But this should allow the combination for you and handle that error.

Hope it helps,
 
Ah ha. I didn't know you could use try outside of an AddColumn's columnGenerator expression. That's a handy trick to know.

And with that simple addition, its working just fine now. Thank you much, Ken. :)
 
Fantastic!

I know Chris Webb blogged about this... a long time ago... actually it's where I discovered the try function in the first place. Maybe it's time for another blog post on the subject!
 
Back
Top