dataflow

6 Topics

High volume Dataverse dataflow times out avoid NESTED FROMs and/or JOINs best approach to resolve?
Background We have a Gen1 Dataverse dataflow that calculates aggregate totals per record (yearly, prior-year, lifetime, and household rollups) and writes them to two output entities, Merge Account and Merge Contact. Each output entity is built from a chain of ~11 Table.NestedJoin (JoinKind.FullOuter) joins over ~12 upstream aggregate queries. The problem The dataflow refreshes without issue on smaller environments but times out on high-volume ones with: Microsoft SQL: "The updated two minutes (2) timeout period elapsed prior to completion of the operation. Please avoid using SELECT *, or NESTED FROMs and/or JOINs to execute the query with default five minutes (5) timeout." ErrorCode = 10478; Number = 40000 (Some environments show error 40197 instead.) It stops on the Merge step. What we've implemented so far We've built a monitoring solution: a cloud flow that detects dataflow refresh failures and logs the details (dataflow name, ID, and error message) into a custom Refresh Audit table, surfaced in a model-driven app so the team has visibility into failures. We've analyzed the failure across multiple environments and confirmed it correlates with data volume — the same dataflow completes on smaller datasets and times out on larger ones, with the filtered row volume and per-record density appearing to be the main drivers. We've identified that the full-outer joins don't appear to fold, so a large nested query is sent to the Dataverse TDS endpoint and hits the query timeout — i.e. it's a timeout, not a data/row error. Approaches we're evaluating — would these be effective? Increasing the query/command timeout for a Gen1 Dataverse dataflow — is that configurable, and where? Using Table.Buffer on the join inputs to force local evaluation and avoid pushing the nested query to the TDS endpoint — would this reliably help, or just shift the bottleneck? Staging the upstream queries into computed/staged entities (or a separate staging dataflow) — is this the recommended pattern, and does it need enhanced compute / premium? For very large datasets (1M+ rows), pushing the aggregation source-side (FetchXML aggregate / SQL view) — is this the more reliable long-term approach? Is there a supported way to get per-step timing / query diagnostics for a dataflow refresh, to pinpoint the heaviest step and enrich our monitoring? Any guidance on which of these works best for high-volume Dataverse dataflows with heavy full-outer join chains would be much appreciated. Thanks!
Solved
Tejakm123
1 month ago Place Power Query
347Views
0likes
5Comments
Data Flow Gen1 ODataService Errors
Hello, i use multiple Dataflows Gen1 to red Data from an OData Service of our local ERP System. Now i have a OData serivce where im not able to load the data. I don't know if the data of this ODataService is simply to big or there are other solutions. The Tables i want to load have around 60 to 80 thousand rows and 200 Collumns. I get errors in Datflow like: -Unable to complete the request. Please retry again later, and if this issue recurs, check your network, proxy, vpn, or firewall configuration or contact your network administrator. -Unable to read data from the transport connection: The connection was closed -Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. -Request failed: The remote server returned an error: (500) Internal Server Error. (Internal Server Error) One Table is aaccessible, but it has only 8 thousand rows and the 200 collumns. Interesting is: I can load the ODataServices Excel, takes up to 15 minute with 400-500 MB I can Preview data in Power BI Dektop Quety Editor, but can't load it into report (Error: No Data could be read from source: Connection was closed) Does someone has expierence with those issues or is my data just to big for ODataService in Dataflow Gen1?
Solved
LuITS
1 year ago Place Power Query
1.3KViews
0likes
4Comments
The refresh of the on-premise SQL connector is failing in Dataflow Gen1 within the Pro workspace
Dear Community Members, I am using Dataflow Gen1 in the Pro workspace of the Power BI service to connect to an on-premise SQL Server database. A few months ago, everything was working fine. However, for the past couple of months, the refresh has been failing. When I refresh the data from the M-Query of the dataflow it works but when I refresh the dataflow it's failing. In the error message export from the dataflow nothing is specified. it says ony Error : Request ID : and Activity ID. Here are the steps I have thoroughly checked: No premium transformations or features are used in the dataflow. I even tried using a basic table with 100 rows without any transformations, but it still failed. I checked the Gateway connection configuration. It is working fine, as the same Gateway connection is being used to refresh the dataset, and it works. The on-premises data gateway is up to date. I recreated the dataflow multiple times in different Pro workspaces with different users and still the issue is persists. I also checked that, the same dataflow is working perfectly fine when I use the Premium workspace. Has anyone experienced a similar issue or have any suggestions for a solution?
Iras
1 year ago Place Power Query
594Views
0likes
2Comments
dataflow
In the below scenario assume that D is the table I use in the report which is a append of 1, A and B. If I refresh DF1 and DF A then its obvious that the last dataflow would also refresh since it has 1 and A as linked entities. Questions: 1. Does the final dataflow refresh twice? 2. If I refresh 1 and A together will it cause the last one, and hence the entire chain to fail? Any best practices? 3. If I care most about A and expect 1 to fail often, how can I ensure that the final DF does not fail? And my append happens with A and B with the new data and 1 with the old data (in the current scenario the entire flow would fail)
Solved
mandy5118
1 year ago Place Power Query
1.3KViews
0likes
4Comments
Power query to find date present between two dates
I have a date field in my table i want to write a power query for new column to find date between two dates. if present it returns true else false
Solved
Ramakrishna7032
3 years ago Place Power Query
853Views
2likes
2Comments