Last week one of my clients had an issue with timeouts in their dataflows in Power Apps. It had worked for a while, but then suddenly the dataflow was experiencing timeouts.
Dataflow query timeout issues
Table of Contents
In my case I found that my dataflows would fail in different ways. But Mainly I would get back the following errors.
Error Code: Mashup Exception Data Source Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Microsoft SQL: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> <title>502 - Web server received an invalid response while acting as a gateway or proxy server.</title> <style type="text/css"> <!-- body{margin:0;font-size:.7em;font-family:Verdana, Arial, Helvetica, sans-serif;background:#EEEEEE;} fieldset{padding:0 15px 10px 15px;} h1{font-size:2.4em;margin:0;color:#FFF;} h2{font-size:1.7em;margin:0;color:#CC0000;} h3{font-size:1.2em;margin:10px 0 0 0;color:#000000;} #header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:"trebuchet MS", Verdana, sans-serif;color:#FFF; background-color:#555555;} #content{margin:0 0 0 2%;position:relative;} .content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;} --> </style> </head> <body> <div id="header"><h1>Server Error</h1></div> <div id="content"> <div class="content-container"><fieldset> <h2>502 - Web server received an invalid response while acting as a gateway or proxy server.</h2> <h3>There is a problem with the page you are looking for, and it cannot be displayed. When the Web server (while acting as a gateway or proxy) contacted the upstream content server, it received an invalid response from the content server.</h3> </fieldset></div> </div> </body> </html> RequestId: TDS;d6eac48c-9c9b-4d3b-bd04-6aa038c4115d;26 Time: 2024-03-08T16:34:16.2315699Z Details: Reason = DataSource.Error;DataSourceKind = CommonDataService;DataSourcePath = siptracker.crm11.dynamics.com;Message = <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> <title>502 - Web server received an invalid response while acting as a gateway or proxy server.</title> <style type="text/css"> <!-- body{margin:0;font-size:.7em;font-family:Verdana, Arial, Helvetica, sans-serif;background:#EEEEEE;} fieldset{padding:0 15px 10px 15px;} h1{font-size:2.4em;margin:0;color:#FFF;} h2{font-size:1.7em;margin:0;color:#CC0000;} h3{font-size:1.2em;margin:10px 0 0 0;color:#000000;} #header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:"trebuchet MS", Verdana, sans-serif;color:#FFF; background-color:#555555;} #content{margin:0 0 0 2%;position:relative;} .content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;} --> </style> </head> <body> <div id="header"><h1>Server Error</h1></div> <d...;ErrorCode = -2146232060;Number = 40000;Class = 16;State = 1;Microsoft.Data.Mashup.Error.Context = User (Request ID: 6dfa2c2f-15be-4a96-9e99-82613dd8ff91).The important parts of the above errors are:
Error Code: Mashup Exception Data Source Error, Error Details: Couldn’t refresh the entity because of an issue with the mashup document
and
502 – Web server received an invalid response while acting as a gateway or proxy server.
These issue descriptions are use useful as a chocolate teapot..
So what is going on?
Query architecture in dataflows
Well there are all sorts of things that could be wrong.
Frist of all there could be something wrong in the queries in your dataflow. Typically dataflows have multiple queries and each query has multiple steps resulting in a table as output.
When you look at the advanced code for a query you will find the following pattern.
let
Source = ...
in
#"Whatever data"As mentioned this returns a data represented by the #”Whatever data” part.
Now the table of one query can be used as input for other queries.
So what happens if you have a lot of data and many levels of queries? You get complexity!
Fixing timeout issues in dataflows
When I reviewed the data structures there were no key fields defined on some of the tables. I ended up reviewing how each query is using the data returned by the queries higher up in the chain.
Then quite quickly I noticed the missing keys. All you have to do is select the properties in the table preview (in my case surveyid and propertyid) and then find the Mark as key option in the Transform tab. This will create an index on the table generated by the query.
Which queries/tables to focus on?
There are a few approaches to this. I would look either at the queries that give you your results (so these are at the end of your query chain) or look at the first ones first as they are to be used by the later queries. Your indexes are most likely needed in the queries earlier on in the chain of queries.
You might even find that those tables are relatively small. However the usage of those tables, by the later queries may be experiencing larger volumes.