I recently learned about a cool tool in XRMToolbox called Power Query M Builder (PQMB) by Mohamamed Rasheed (ITLec) and Ulrick “CRM Chart Guy” Carlsson (eLogic LLC) and I love the tool. It lets you generate Power Query code using Dynamics 365 views as your starting point, then you can tweek them using FetchXML Builder to get more complex queries, and then generate a full query that produces the field names using the labels from your CRM metadata. This process is much faster than starting from scratch using the Power Query tool to build up a query to find the right data, plus, it has the added advantage that you can easily change the connection URL for all the queries from a single place, which is really helpful if you have a solution you move from dev to production.
I left a mesage on Ulrick’s blog that there is one improvement I would like to see. I would like to have the ability to save/recover views more easily. For example, I have a report that I know needs several different CRM views or custom FetchXML queries. After I have created all the queries in Power Query and then start to relate them together in PBI, then I find that I am missing some columns, so I need to start from scratch on one or more of my views in PQMB.
To save some time, I started saving all my FetchXML queries embedded in comments at the end of the Power Query Advanced Editor. Here is what I do:
While in PQMB, I can open the FetchXML builder to modify a query
then copy it to the clipboard and paste it into the end of the Power Query using /* comment */ .
Now if I had to revise my query at a later date, then I can quickly get back to what I had without having to recreate a complex query from scratch.
No comments:
Post a Comment