We have added some updates to the Power Query (M) Builder for the XrmToolBox.
Video is at the bottom of this post.
There are 3 main new features:
- Automatically generate links back to the Dynamics 365 record
- Generate an empty table when no records are retrieved
- Use all-attributes in the FetchXML
Automatically generate link to Dynamics 365
The tool now has the option to generate a column with the URL directly to the Dynamics 365 record, making it easier for users to navigate from their Power BI reports to Dynamics 365.
Generate an empty table when no records are retrieved
If your query retrieves no records, then the Power Query code will throw some errors as it will try to rename and change data type on fields that do not exist. There is now an if statement built-in to the Power Query code, which generates a table with all the same columns and data types, but no records. This means reports should continue to function even though some tables did not return any records. This is particularly useful when using the same report on a number of different environments.
I found this post by Avi Singh https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then-else-gt/bc-p/41383 very useful in building this feature.
Use all-attributes in the FetchXML
You now have an option to use when querying via FetchXML instead of defining each field individually. This is required when you are including more fields than what FetchXML allow. It can also be useful as it can allow you to go back to the expand step in the Power Query editor and includes any field from the selected entity. This makes the model a little more flexible for adding fields down the road.
Other updates:
- There are now two URLs used in the queries. The Dyn365CEBaseURL and the ServiceRootURL
- Searching entities now includes both schema and display name
- Searching fields now includes both schema and display name
- Owner type, user or team, is now included in query
- A few bug fixes and optimizations around the display name
Here’s the video introducing the tool and all the new features.
For more information and credits to everyone involved in this project, please check out the Power Query Builder page for full details. That page will also be continually updated with the latest updates on the tool.
Enjoy. As always, please sign up for my newsletter or follow me on Twitter Follow @CRMChartGuy
crm chart guy 
Can you add these to a current pbix file?
Yes, you can add the queries to an existing pbix file. Just make sure you also add the Dyn365CEBaseURL and the ServiceRootURL which the queries use.
I’m really excited by your blog and hope to be able to use links in my PBI reports! But I get this error when I tried? Expression.SyntaxError: Token Eof expected. I believe I’m following the steps outlined in the video as I am able to successfully do this when I am in a new pbix file.
Thanks!
Hi Barbara. Depending on how old your file is, and what version of Dynamics you are using, you could possible run into some issues. I think this may be an issue for files originating before updating to the June 2018 version of Power BI. Power BI introduced a few extra parameters in the query at that time.