An error occurred in the ‘’ query. Expression.Error: The column of the table wasn't found.

[powelmac]

Hi There,

Having some issues scraping financials from the web from multiple URLS.

The reason is that for some companies, they haven't yet reported their 06/2021 (FY21) numbers and some have. The query appears to error out as soon as it reaches a URL missing the 06/2021 column - is there a workaround for this?

let

Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"URLS", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "FetchSummary", each Summary([URLS])),
#"Expanded FetchSummary" = Table.ExpandTableColumn(#"Added Custom", "FetchSummary", {"", "06/2021", "06/2020", "06/2019", "06/2018", "06/2017", "06/2016", "06/2015", "06/2014", "06/2013", "06/2012", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21"}, {"Column1", "06/2021", "06/2020", "06/2019", "06/2018", "06/2017", "06/2016", "06/2015", "06/2014", "06/2013", "06/2012", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded FetchSummary",{"Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each true)
in
#"Filtered Rows"

Many Thanks

[v-yingjl]

Hi @powelmac ,

You can also try to split the query before 06/21 FY21, check the query result after it and replace error for them and merge the modified query result with the previous correct query.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ehren · Accepted solution]

One approach would be to run some M over each nested table before expanding, to ensure they all have the columns you're expecting. If an expected column is missing, you could dynamically add it and populate it with nulls.