By Veronique LengelleShort post today, but worth knowing about! Something you’ll want to use very often when you need to retrieve large sets of data from Dataverse. FetchXML allows you to query a single table to select only what you need like the filter query in a few actions, but in a more powerful way.
Scenario
In this fictious scenario, I have 3 clothing stores (in 3 different countries) which sell 3 products; t-shirts, jackets, and hoodies. In Dataverse, I have 2 years data for sales, and my ultimate goal is to analyse the past quarter’s sales with the same quarter the year before, so I can get some insights by email from an AI Prompt.
Why use FetchXML
This functionality offers several benefits.
- Flexibility: You can use groupby, select, order, filter, count rows, aggregate data, get distinct values, add conditions, and more.
- Performance: Can get your data really fast and with Power Automate, this is important!
- Savings: Kind of. In my case below, I don’t need to add more actions or formulas to calculate all the units sold, and it also provides quick results to whatever I need to do (i.e.: use values for the AI prompt to analyse the sales)
How to use FetchXML
To use FetchXML, you obviously need a Power Automate action that supports it. In this case, I need to get all the data (aka “List rows”) scoped to my timeframe (same quarter last year). Now, with the original action I’m allowed to Select Columns, Filter rows, and also use Sort By.
With the Fetch XML Query below, I’m able to do some maths with aggregate="sum" for my units sold. This will directly give me the total amount of t-shirts, jackets, and hoodies sold during that time.
The GroupBy is also very useful in my case because I can see clearly the revenue generated per product, and if a discount was applied (promoapplied).
Conclusion
If you didn’t know about FetchXML or not sure it was useful, well… it is. The flexibility and performance alone should get you interested. Here are some more helpful links:
Thanks for reading! 🙂