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

[Anonymous]

Hi there. New to power query so hoping someone can help me with the above error. I've had a look round but can't find the exact thing I'm looking for. Have also had a play about with MissingField.Ignore but just can't quite get it to work but I suspect it's a simple fix?

I have built a function which scrapes a few different web pages. The below code is for one url rather than the function. These web pages are constantly updating before they are taken off the website. At this point the table becomes blank so therefore column1 obviously cannot be found. Please see the below code. It has been compiled through the power query interface so assume it's a bit messy. However, the error pops up when I try to expand column1. Is there a simple way to just say if column1 does not exist stop? Or in the step before if table is blank stop? Because it is part of a function it stops everything running whereas I would prefer for it to just skip that individual query. Or change all values in the table to null which I can filter at a later stage?

Thanks in advance for any help!

let
    Source = Json.Document(Web.Contents("https://www.spreadex.com/sports/mobile/api/api/Model/SubscribeModel?modelRef=sports/page/all/spr/672401")),
    model = Source[model],
    panels = model[panels],
    #"Converted to Table" = Table.FromList(panels, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"panelItems", "model", "name"}, {"Column1.panelItems", "Column1.model", "Column1.name"}),
    #"Expanded Column1.model" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.model", {"selections"}, {"Column1.model.selections"}),
    #"Expanded Column1.model.selections" = Table.ExpandListColumn(#"Expanded Column1.model", "Column1.model.selections"),
    #"Expanded Column1.model.selections1" = Table.ExpandRecordColumn(#"Expanded Column1.model.selections", "Column1.model.selections", {"name", "price"}, {"Column1.model.selections.name", "Column1.model.selections.price"}),
    #"Expanded Column1.panelItems" = Table.ExpandListColumn(#"Expanded Column1.model.selections1", "Column1.panelItems"),
    #"Expanded Column1.model.selections.price" = Table.ExpandRecordColumn(#"Expanded Column1.panelItems", "Column1.model.selections.price", {"buy", "sell", "status"}, {"Column1.model.selections.price.buy", "Column1.model.selections.price.sell", "Column1.model.selections.price.status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.model.selections.price",{"Column1.panelItems"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.name", "Column1.model.selections.name", "Column1.model.selections.price.sell", "Column1.model.selections.price.buy"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.model.selections.name", "Market"}, {"Column1.model.selections.price.buy", "Buy"}, {"Column1.model.selections.price.sell", "Sell"}, {"Column1.name", "Section"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Section", "Market", "Sell", "Buy", "Column1.model.selections.price.status"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column1.model.selections.price.status", "Status"}})
in
    #"Renamed Columns1"

[KumarDarmesh]

@ImkeF

[ImkeF · Accepted solution]

Hi there,

easiest is to add a line of code at the end:

let
    Source = Json.Document(Web.Contents("https://www.spreadex.com/sports/mobile/api/api/Model/SubscribeModel?modelRef=sports/page/all/spr/672401")),
    model = Source[model],
    panels = model[panels],
    #"Converted to Table" = Table.FromList(panels, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"panelItems", "model", "name"}, {"Column1.panelItems", "Column1.model", "Column1.name"}),
    #"Expanded Column1.model" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.model", {"selections"}, {"Column1.model.selections"}),
    #"Expanded Column1.model.selections" = Table.ExpandListColumn(#"Expanded Column1.model", "Column1.model.selections"),
    #"Expanded Column1.model.selections1" = Table.ExpandRecordColumn(#"Expanded Column1.model.selections", "Column1.model.selections", {"name", "price"}, {"Column1.model.selections.name", "Column1.model.selections.price"}),
    #"Expanded Column1.panelItems" = Table.ExpandListColumn(#"Expanded Column1.model.selections1", "Column1.panelItems"),
    #"Expanded Column1.model.selections.price" = Table.ExpandRecordColumn(#"Expanded Column1.panelItems", "Column1.model.selections.price", {"buy", "sell", "status"}, {"Column1.model.selections.price.buy", "Column1.model.selections.price.sell", "Column1.model.selections.price.status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.model.selections.price",{"Column1.panelItems"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.name", "Column1.model.selections.name", "Column1.model.selections.price.sell", "Column1.model.selections.price.buy"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.model.selections.name", "Market"}, {"Column1.model.selections.price.buy", "Buy"}, {"Column1.model.selections.price.sell", "Sell"}, {"Column1.name", "Section"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Section", "Market", "Sell", "Buy", "Column1.model.selections.price.status"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column1.model.selections.price.status", "Status"}}),
    ErrorHandling = try #"Expanded Column1" otherwise null
in
    ErrorHandling

"try #"Expanded Column1" otherwise null" will check the step which can cause the error and returns null (or anything else that you replace that with) in case of error.

Because M is partially lazy, there is a good chance that in case of error all the subsequent steps are not executed and performance will not be affected negatively.

[Anonymous]

Great thanks