The Power Query (M) Builder is a new tool for the XrmToolBox.
It is designed to automate the basic Power Queries for getting data into Power BI from Dynamics 365. This includes selecting fields, renaming fields, querying option sets etc.
Updated: July 22 2018. This page is a work in progress and will continue to be updated.
Here is an intro video on how to use the tool, and some of its features, particularly using FetchXML in your Power Query so you can easily retrieve the option set labels, lookup names, etc.
Video is for Power Query Builder version 1.2018.7.20. (Latest version)
Like the Advanced Chart Editor, the tool was developed between Mohamed Rasheed and myself.
Features included in this tool:
- Support for FetchXML in your Power Query
- Show labels for Option Sets and Status fields
- Show name for Lookup fields
- Show name and type for Owner and Customer fields
- Support for OData retrieval (standard querying method for Dynamics 365)
- Auto-include GUID for current entity
- Use Dynamics 365 display name for all columns
- Automatically set correct data type
- Auto-generate a link back to the Dynamics 365
- Handle empty tables when no records are retrieved
- Use of all-attributes in FetchXML as a setting
Check back regularly for more information about the Power Query Builder for the XrmToolBox that helps accelerate getting data from Dynamics 365 into Power BI.
Some stuff that we are still working on:
- Include fields from related (parent) entities
Ideas, how to questions, feedback etc. please put that in the comments below.
Issues, bugs etc. please put that on GitHub: https://github.com/ITLec/PowerQueryBuilder
NuGet link: https://nuget.org/packages/PowerQueryBuilder/
A big thank you to Scott Sewell, who provided a lot of great feedback during the development of this tool. We still have items on the to-do list.
Also, we want to thank Keith Mescha and the former Sonoma Partners Power BI Accelerator. This tool showed us how to do FetchXML pagination in a Power Query. Without it, all the benefits of using FetchXML would have been limited to just 5,000 records.
As always, please sign up for my newsletter or follow me on Twitter for the latest details Follow @CRMChartGuy
Thanks Ulrik and Mohammed, this is awesome.
I just gave it a quick spin, looks good! Do you plan to support the new CDS for Apps connector? Data retrieval from D365 via Odata is fairly slow.
https://powerapps.microsoft.com/en-us/blog/cds-for-apps-powerbi/
Hi Olivier. We are adding support for more methods soon. We will definitely have a close look at CDS-A when it becomes fully available. Right now it looks like you’ll be able to use the same query string that is generated., but we’ll have to wait a little and see.
The Power Query builder is now used in production. I handed over a PowerBI report to a client today where the last extra data source I had added was made with your tool.
Besides the ideas you have already mentioned I would add: pre-filtering in the builder for instance: “only active accounts”.
And there is an issue when selecting the statuscodename field. I can export the query but it will error in Power Bi and say that there is no statusccodename. I just changed it to statuscode and got the usal codes. Just thought I could get the name now the field was on the list.
Hi Marianne. Thanks for the input. The name field issue you are referring to was because the tool included virtual fields. OData can’t support those. The latest version automatically excludes those from the list.
Will this work for me if I’m on prem and version 2015? I am getting errors and can’t find any version limitations listed on your docs. Thanks!
Good question. I have not been able to test that, but the tool automates the transformations based on the Dyn365 metadata. Those are the same between online and on-premise, so the only manual adjustment you may have to do for an on-premise environment, is the ServiceRootURL and make sure that is in the proper format.
[…] 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 […]
[…] have added some updates to the Power Query (M) Builder for the […]
[…] For querying the Dynamics 365 option set into Power BI, I use the Power Query (M) Builder tool for the XrmToolBox. […]
[…] “secret sauce” in this video is the Power Query (M) Builder for the […]