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

Guttural

New Member
Joined
Jan 21, 2015
Messages
9
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top