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 

11 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).

  2. Hi Ulrik,

    Question on how to handle queries that exclude related entities as I can’t seem to get it to work. For example, with the updates to v9 you can build queries to show all Open Opportunities that don’t have an associated Order. I see that when I generate the Fetch XML using the FetchXML builder that it has that condition but it doesn’t copy over to the Power Query editor. I’m assuming that this might not be possible.

    Thanks

    Eric

  3. Hi Ulrik,

    I have recently discovered Power Query (M) Builder. First impressions are that it is fantastic.

    A quick question. Is it supposed to be used with the Power Query engine in Excel as well? I have a project that needs to create an extract of D365 data as a table in Excel. I have used Power Query (M) Builder to extract the data and at first I thought it was working perfectly but then I noticed that two of the money columns had come through with all the rows showing null. Other money columns came through OK and, so far, I haven’t been able to spot anything odd about the columns that didn’t. On a hunch I pasted the M code from the Excel file into Power Query inside Power BI and the problem has gone away.

    It looks like the exact same code works in Power BI but sometimes doesn’t work in Excel. Is this a known limitation or is there something I can tweak to make it work in Excel.

    Thanks
    Ian

  4. Hi Ulrik,

    I have recently discovered Power Query (M) Code and it is a fantastic product. Thank you.

    Unfortunately, I have hit a problem. When I use FetchXML based M code, generated by Power Query (M) Code, in Power Query in Excel, the data that is returned does not contain all the expected records. There are no filters in the FetchXML and the exact same code brings back all the expected records when I use it in Power Query in Power BI. This happens for a number of tables. As far as I can tell it’s always the same records that are missed but, so far, I have been unable to establish what it is about these records that makes them different from the records that are imported successfully.

    Do you know of any issues to be aware of when using Power Query (M) Code with Excel rather than Power BI? Are there any workarounds?

    Thanks
    Ian

Leave a Reply to Use Icons from Power Apps Unified Interface in Power BI | crm chart guy Cancel 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