Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Note: YMMV!
This issue I had.
I had to produce a weekly 'run sheet' for installers from data mainly in the Orders table in dynamics 365. There are 8 sets of fields each set looking like this;
Installer name
Product category
Scheduled install time.
My first pass involved pulling the data directly in from dynamics in PBI Desktop.
I created sub-tables from each set of fields (8 in total) and did a union all on the data.
This worked however it took a long while on refresh because I had to load the entire order table in - and I could not find a way of directly using the dynamics connector to reduce the data returned.
So.......
Second try.
I wrote a flow with a trigger of 'When a HTTP request is received' - make a note of the generated URL!
The flow retreived the orders using a filter like...
C1_bookedinstalldate ne null or C2bookedinstalldate ne null or C3sbookedinstalldate ne null or C4bookedinstalldate ne null or C5bookedinstalldate ne null or C6sbookedinstalldate ne null or C7installdate ne null orC8bookedinstalldate ne null
I created an array variable to hold my processed data for each category and each array entry looks like
{
"Week": @{outputs('Week_2')},
"Product": "Product Category",
"ServiceDate": "@{items('AllOrders')?['C1bookedinstalldate']}",
"Fitters": "@{items('AllOrders')?['C1sallfitters']}",
"Order_Case": "@{items('AllOrders')?['new_quoteid']}",
"Customer": "@{items('AllOrders')?['_customerid_value@OData.Community.Display.V1.FormattedValue']}",
"Address": "@{items('AllOrders')?['billto_composite']}",
"Duration": @{variables('serviceTime')}
}
Depending on the populated BookedInstallDate there could be up to 8 array entries per order (1 per category)
Once all the data was in the array, the flow finished with a Response and returned the array.
In PBI Desktop, I got data from the web using the URL created in the flow.
Now the refresh takes seconds instead of five minutes.
Caveats. When developing the flow, ensure that you have all the data fields in the array - if you find you have missed one and add it, PBI does not seem to pick it up - so when I did this, I had to export the flow, re-import it as a new flow, then in PBI get the data from the new web endpoint for the new flow.
Stan
I have just discovered.....
That my PBI Query on the web was timing out.
See the following for the way to fix the timeout
Solved: Query Timeout on update - Microsoft Power BI Community
HI @stan_w_gifford,
I suppose the timeout may be due to the data source records amounts. For this scenario, you can also consider assigning them to premium capacity which allows more processing times.
Xiaoxin Sheng
All lI had to do was increase the timeout value in the query in PBI - per the referenced link.
HI @stan_w_gifford,
The timeout property can be configured on the data connector advanced setting, but on the power bi service side it has the limit on the maximum timeouts.
excel - How to set a max run time / timeout for Power Query? - Stack Overflow
Troubleshooting refresh scenarios - Power BI | Microsoft Docs
AFAIK, you can't increase the timeout limits except move them to premium capacity.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |