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
