Power Query If Statement

[jc0r]

Hi all

I'm after a little help with regards to and if statement in Excel Power Query. I have 30 different queries called Race1, Race2 etc. The code for this simply gets information from a website that has been extracted from my worksheet, Below is an example of one of these queries, specifically, Race1.

let


RaceList = Excel.CurrentWorkbook(){[Name="RaceTable"]}[Content],


URL = RaceList{0}[RaceURL],


Source = Web.Page(Web.Contents(URL)),


Data = Source{1}[Data]


in


Data

I then have a compiled table of all 30 queries using the append feature in which i remove certain columns. This can be seen below.

let    Source = Table.Combine({#"Race 1", #"Race 2", #"Race 3", #"Race 4", #"Race 5", #"Race 6", #"Race 7", #"Race 8", #"Race 9", #"Race 10", #"Race 11", #"Race 12", #"Race 13", #"Race 14", #"Race 15", #"Race 16", #"Race 17", #"Race 18", #"Race 19", #"Race 20", #"Race 21", #"Race 22", #"Race 23", #"Race 24", #"Race 25", #"Race 26", #"Race 27", #"Race 28", #"Race 29", #"Race 30"}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26"})
in
    #"Removed Other Columns"

My problem is, the website i extract data from will remove certain tables throughout the day from some of the URLs. When this happens this causes the appened table to get the error:

Expression.Error: The column '3' of the table wasn't found.
Details:
3

This stops the remaining queries from executing. What i would like to do is have an if statement somewhere that says, eg, "if Race1 error, do nothing, move on to Race 2 etc."

Thanks in advance

 

[MarcelBeug]

You can add an extra argument missingField with Table.SelectColumns. This link refers to parameters that can be found here (almost at the bottom).

So the easiest solution would be:

#"Removed Other Columns" = Table.SelectColumns(Source,{"3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26"},MissingField.Ignore)

Otherwise I would just add an additional column to the RaceTable with the contents from the web as nested tables and expand that column, so you don't need 30 queries (unless you want a table for each race of course).

 

[jc0r]

Hi Marcel,

Many thanks for your help. I have tried what you have suggested but the initial refresh completely deletes the table, which puts all my formulas that rely on the table in #REF

I'm not sure whether it is because the table is basing its column off Race1 and if that fails, every subsequently has nowhere to display. To try and help, i've put a copy of my Worksheet on my Google Drive linked below. If you have time to take a look it would be very much appreciated.

Also, on the Race Lookup sheet, you will see i have a table named RaceURL. These are generated from the other sheet, however, from row 21 down you will see a default URL. I have had to put something in there because if there is nothing, my queries also fail. Its very inefficient as im refreshing webpages where i know information is not kept. If this can also be eliminated that would be fantastic.

Once again, many thanks for your time

https://drive.google.com/file/d/0B_1J8B9mLp7JV0JRemc0RmVoVEk/view?usp=sharing

 

[MarcelBeug]

Unfortunately, I'm not getting the data correctly from the websites as you can see in this video.

 

[jc0r]

Thanks ever so much for taking the time.


The reason you can not see anything from that table is because it was a horse race that has already ran. This is a good example of what i am trying to deal with though.
Basically, i want to load all the races for the day, but as the races finish, thoughout the day, every race will eventually end up with the response you have seen.
I will link a race that is currently active at the bottom of this post.


As you will see from the link, while the race is active, it does not contain as many columns as i allow for. Over time, the website will add the columns when more odds become available. This is why there are more columns in my table, to allow for these extra ones when they become available.


I guess what i am trying to do is, if the column exists, populate my query with data, any columns that don't exist, leave blank. Also, if the table does not exist at all, ignore the query and move on to the next race.

Bestbetting > Horse Racing > England > Cheltenham > 2017-03-14-2017-03-14 > Supreme Novices Hurdle > Antepost Betting

Many thanks

 

[MarcelBeug]

Based on the title of this topic, it looked like a simple question, but now I get the feeling it is turning into a project.
So I'm not going to spend more time on it.