Forum Discussion
Why Am I Getting Two Different Results From One Constant Source?
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
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
Community ChampionTry 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.
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?
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=TypeI 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?