Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,202
    Articles
    57
    Blog Entries
    14
    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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Talking

    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,202
    Articles
    57
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •