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.
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.
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 Follow @CRMChartGuy
crm chart guy 
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
Haven’t tried, but I’m curious now. I don’t see why not. You would be subject to the 50k FetchXML limit though as we can’t take advantage of pagination.
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).
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
Hi Eric
Can you make sure you have the latest version. Just tried it out and it seems to work fine. I also do not see any reason why it shouldn’t work.
how we can use XRM tool box query for direct query? currently it is importing data.
The database, the Common Data Service does not support direct query. Import is only option. Otherwise you’ll need to look at the Data Export Service and then you can use direct query.
Hi Ulrich, Can we use CRM Security roles to restrict the the access to the Power BI reports in d365?
[…] See this post for more details on the Power Query Builder and the use of FetchXML. […]
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
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