Why Am I Getting Two Different Results From One Constant Source?

[mpincince]

I am getting two different source results from one constant Source and can't figure out why.

I am using the following to get my source:

Source = Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP"))

Each time I refresh, I get one of two results. I either get this:

[Note that in this first result, if I click on the cell in the second row of the Data column, I get a table with many columns: Details, Request Number, Assigned Date, Assigned to, Priority, etc., as shown above.]

Or I get this:

[Note that in this second result, if I click on that same cell (the cell in the second row of the Data column) I only get four columns: Kind, Name, Children, and Text.]

I want to always get the first result...so that when I navigate to that Table in the cell of the second row of the Data column, by using Source{1}[Data], I see the many columns of the first result: Details, Request Number, etc.

Does anyone have any idea why I am often getting the second result and how to correct this so that I can only get the first result?

I had asked this same question on StackOverflow and received absolutely no response, so I deleted my post there and now I'm here.

[v-chuncz-msft · Accepted solution]

@mpincince,

You may try using the try expression in a recursive function.

[mpincince · Accepted solution]

Thanks again @v-chuncz-msft. This seems to be working okay:

let
  GetData = () => if List.Contains(Table.ColumnNames(Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")){1}[Data]), "Details") then 
    Table.RemoveColumns(Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")){1}[Data],{"Details"}) else @GetData(),    
  ShowData = GetData()
in
  ShowData

[Greg_Deckler]

I guess my question would be, when you get the first result and configure your query, do subsequent refreshes work or do you get an error. Really tough to troubleshoot without access to the web site.

[mpincince]

Thanks for responding and trying to help me out @Greg_Deckler. I appreciate the difficulty of troubleshooting without access to the site.

The problem occurs intermittently. One time, I'll get exactly what I expect, which is an output of the columns from the expected table; then the next time, I'll get an error indicating that the first column I try to reference doesn't exist. It doesn't matter which column I'm trying to use for what...I'll still get the error. The error is "Expression.Error: The column 'Column Name' of the table wasn't found."

As my query is currently written, the error refers to the Details column. The query doesn't see the Details column from the expected table during those error instances, apparently because, instead of getting the expected table, the Source gets the other table.

Again, this happens intermittently. I can refresh once and it will work, then I'll refresh again and it won't, then I'll refresh again and it will or won't, etc, with no apparent ryhme or reason.

Here's my M code, in case it helps any (really nothing special):

let
    Source = Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")),
    Data1 = Source{1}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data1,{"Details"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Request Amount", type number}, {"Assigned Date", type date}})
in
    #"Changed Type

[Greg_Deckler]

Try going into the advanced options and see if you can increase the time-out. What I am guessing is going on here from looking at the second result a little closer is that you are getting back a blank HTML page, it's essentially timing out. If I am reading the second result correctly, you are getting an HTML element that is essentially null. Seems like a timeout kind of problem to me.

[mpincince]

Unfortunately, the timeout didn't solve it.

[Greg_Deckler]

Well, I guess another question would be, if you go to the web page in a browser and click refresh a bunch of times, does it sometimes come back blank?

[mpincince]

I just tried refreshing the web page a bunch of times and it always came up just fine. Separately though, I did try something else that yielded a result I found interesting.

I refreshed my query, then I clicked on the table in the Data column of the Source and got the not desired resulting table of columns; but then I waited a moment and, without refreshing the query, I clicked on the table in the Data column of the Source again and got the desired resulting table of columns. So I'm thinking I need a delay before I navigate to the table of columns via Source{1}[Data]. I'm guessing I need to use Function.InvokeAfter(), but I'm not sure how to use it with Source{1}[Data]. I tried Function.InvokeAfter(Source{1}[Data], #duration(0,0,0,5)) but I got the error:

    Expression.Error: We cannot convert a value of type Table to type Function.
    Details:
        Value=Table
        Type=Type

I also attempted to use a function to get around the error, base on some of Chris Webb's blogs but I'm not sure I did it right, because I still got the intermittent problem.

Would you agree that I might need a delay, and if so, how might you add it?

[v-chuncz-msft · Accepted solution]

@mpincince,

You may try using the try expression in a recursive function.

[mpincince]

Thanks @v-chuncz-msft. I tried two different recursive functions. One uses try-otherwise and would theoretically keep refetching the table till it could find and and delete the column named Details, and the other uses if-then-else and would keep refetching the table till it could find the column named Details (if it's there, it's the right table). Both functions gave me the same error: Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

When I substituted Kind for Details, both functions worked fine, because they fetched the second result table (from my original question's examples)...probably on their first attempt...and that second result table has a column named Kind.

Any other ideas?

My function attempts' code are:

let
  Source = Web.Page(Web.Contents("https://MyDomain/path/req_search.cfm?page=AP")),
  GetData = (DataSource) =>
      let 
        DataSet = DataSource{1}[Data],
        RemoveIt = try Table.RemoveColumns(DataSet,{"Details"}) otherwise @GetData(DataSource)
      in
        RemoveIt,        
  ShowData = GetData(Source)
in
  ShowData

...and...

let
  Source = Web.Page(Web.Contents("https://MyDomain/path/req_search.cfm?page=AP")),
  GetData = (DataSource) =>
      let 
        DataSet = DataSource{1}[Data],
        CheckForColumn = if List.Contains(Table.ColumnNames(DataSet), "Details") then 
          Table.RemoveColumns(DataSet,{"Details"}) else @GetData(DataSource) 
      in
        CheckForColumn,
  ShowData = GetData(Source)
in
  ShowData

[v-chuncz-msft]

@mpincince,

Try putting Web.Contents in the recursive function.

[mpincince · Accepted solution]

@v-chuncz-msft, I went back and changed my query to use try-otherwise, with the recursive function structured as below, and it worked this way too. So I'm marking your original response as a solution. Thanks yet again for your help.

let
  GetData = () => try Table.RemoveColumns(Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")){1}[Data],{"Details"}) otherwise @GetData(),
  ShowData = GetData()
in
  ShowData

[mpincince · Accepted solution]

Thanks again @v-chuncz-msft. This seems to be working okay:

let
  GetData = () => if List.Contains(Table.ColumnNames(Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")){1}[Data]), "Details") then 
    Table.RemoveColumns(Web.Page(Web.Contents("https://webapps.MyDomain/path/req_search.cfm?page=AP")){1}[Data],{"Details"}) else @GetData(),    
  ShowData = GetData()
in
  ShowData