Power Query (M) Builder for the XrmToolBox (April 2019 update and fixes)

It has been a little while since the last update, but Mohamed Rasheed and I just added some bug fixes and improvements to the Power Query (M) Builder for the XrmToolBox.

If you are new to the Power Query (M) Builder and how it can accelerate Power BI report development from Dynamics 365, check out the intro page here.

Fixes and improvements described in this post apply to version 1.2019.423.8

All Entities Enabled

Now you can add all entities, including system entities such as “Auditing”, and system N:N entities like “opportunitycompetitors”.

This addition also means all FetchXML queries you create in the FetchXML Builder can be used in the Power Query (M) Builder. Either by using the integration or a simple copy and paste.

More entities and better integration with FetchXML Builder
More entities are now available which also improves the integration to the FetchXML Builder.

This update fixes the error that would prevent the tool from generating Power Queries when one of those entity types were used.

RelativePath in Web.Contents calls

We have added “RelativePath” to the Web.Contents call in the query. This change has little to no impact when working in the Power BI Desktop client, but there is a significant benefit when uploaded to the Power BI service. The previous method would create a connection for every query in Power BI. If you had added 10 entities, you would see 10 queries in the Power BI service that would need to be authorized. Using RelativePath, only one connection will need to be authorized and maintained.

RelativePath added to Web.Contents call
RelativePath added to the Web.Contents call in the query.

While I have not used this method extensively yet, there is definitely much less waiting time for the query validation when trying to schedule a refresh.

I was not aware of this option, so a HUGE thanks to hanksteen on GitHub for the suggestion. https://github.com/ITLec/PowerQueryBuilder/issues/26

I am even considering updating some of my existing reports to be using the RelativePath. I am definitely adding it during regular updates.

Dates now use formatted value

When using the tool on custom “date and time” fields, formatted as “date only”, the column in Power BI would generate an error. This was due to time zone details being passed in the field when Power BI was not expecting it.

We have changed all date fields to now only querying the formatted value from all types of date fields.

This fixes the error and comes with some additional benefits. This also means all values are now being pulled in the time zone of the logged in user instead of only retrieving UTC times.

  • Fields with the format “Date Only” will be set to “date” in Power BI.
  • Fields with the format “Date and Time” will be set to “datetime” in Power BI.
  • We are no longer setting any fields to “datetimezone”.

Not specifically related to the Power Query Builder, but this came up during testing. If querying date only fields from Dynamics 365/CDS and your computer date format is different from your setting in Dynamics 365 you will see an error. Always make sure the date format in your Dynamics 365 personal settings is the same as your computer running the Power BI desktop client.

Comments, ideas, etc. for the tool are always welcome. Either here on this blog or on GitHub https://github.com/ITLec/PowerQueryBuilder

Enjoy. As always, please sign up for my newsletter or follow me on Twitter 

3 thoughts on “Power Query (M) Builder for the XrmToolBox (April 2019 update and fixes)

  1. Hi Ulrik

    Thanks for the update and love your blog posts. I’ve got a quick question. Is it possible with the fetch to perform a aggregate before the import process of Power BI?

    I’m just thinking from a performance point of view if all I needed was a count of all activities, I don’t want to bring all the activity rows into Power BI and do a count there. Is it possible to perform the aggregate like how we can do it with fetch?

    Kind regards,
    Michael

      • Thanks for the quick reply. It is quite interesting. I got to thinking about this when I had a requirement to bring in data from a government open API on active companies. The API endpoint was quite simple and couldn’t retrieve more than 32k records before timing out but accepted SQL queries.

        I then used SQL to retrieve an aggregate record as well as well as a count aggregate grouped by a specific field type.

        This was ideal as the API had over 3 million rows of records and it was simply not viable to import all the rows in just to say for example get a count of active companies.

        I feel as we start working with larger datasets or if my Dynamics 365 data increases in size, we’d need to start considering performing aggregates to reduce the size of the reports and also streamline BI performance.

        A shame fetch has a 50k limit but understandable I guess so that it does not effect the performance of Dynamics.

        I also saw that Power BI released a new function for incremental refresh but to use it on Power BI service you would have to host your report on a premium workspace (it also doesn’t address the issue of report size which could blow out).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s