How to not append a file if it does not exist?

[Guttural]

Cross-posted from excelguru:

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.

 

[horseyride]

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

Sample code, which perhaps can be modified for your use:

let
    //This is the original code generated by Power Query
    Source = Csv.Document(File.Contents("C:\Users\Chris\Documents\SampleData.csv"),null,",",null,1252),
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"Month", type text}, {"Product", type text}, {"Sales", Int64.Type}}),
    //End of original code
    //Define the alternative table to return in case of error    
    AlternativeOutput=#table(type table [Month=text,Product=text,Sales=Int64.Type],{{"Error", "Error", 0}}),
    //Does the Source step return an error?
    TestForError= try Source,
    //If Source returns an error then return the alternative table output
    //else return the value of the #"Changed Type" step
    Output = if TestForError[HasError] then AlternativeOutput else #"Changed Type"
in
    Output

 

[Guttural]

From the man himself:

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" = try Table.Combine({Source, Yesterday}) otherwise Source
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,

Ken Puls