Forum Discussion
Why Am I Getting Two Different Results From One Constant Source?
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.
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 ShowDatav-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
11 Replies
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.
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 TypeTry 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.
