Excel: Is there a way to update the table from power query even if a row is missing?

Question

Hope you have a great day. Recently I was trying to create a report in Excel and tried to get the data needed from an HTML file. The HTML file is basically the web page where all the issues are stored then filtered in little tables with what we need for the day. I don't have the option to get the data from web directly since the company does not allow add-ins to log in to the site and grab the data from there and the Get Data from Web does not work since the security of the database pops in and does not let you to get anything, so the workaround was to save the page as HTML every time I need to make the report and overwrite the old one that is connected to the Excel Workbook.

I managed to create the needed charts of the loaded tabled from the HTML file into excel, but I stumbled on an issue on the Power Query side. The tables from the page I save the HTML file are not the same, meaning sometimes a column is missing since there was no issues for it and the database will hide it automatically from the table, so when I refresh the query it will display the error "The Column X is missing from the table". I know it is missing, but I don't want to get the data every time one column is missing and redo everything again so the chart will update correctly.

Is there a way to make a code in Power Query advanced editor so the table will update anyway even if a column is missing without needing to code/get data every time? What I'm trying to do here is to automate a process so the least amount of work to get the data, the better for me.

Thanks in advance!

*Edit: This is the source M code of the query:

    let
    Source = Web.Page(File.Contents("D:\AUTO.html")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Customer Impact", type text}, {"Yes", Int64.Type}, {"No", Int64.Type}, {"WIP", Int64.Type}, {"T:", Int64.Type}})
in
    #"Changed Type"

Edited the question and added the source code. Sorry that I forgot to add it in the first place. — Str1ng1737

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-05T18:51:13

The problem is with the #"Changed Type" step since it's trying to transform non-existing columns.

The simplest solution would be to just eliminate that step entirely and let the data come through without assigning types. That is, replace your query with this:

let
    Source = Web.Page(File.Contents("D:\AUTO.html")),
    Data1 = Source{1}[Data]
in
    Data1

If the typing is important, you can write a more dynamic step to assign types that doesn't break. In this case, you'd need to provide details as to how that logic should work (e.g. "Customer Impact" is always present and should be text and the remainder should all be integers).

Excel: Is there a way to update the table from power query even if a row is missing?

1 Answers