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 

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