The latest version of the Power Query (M) Builder for the XrmToolBox has been updated and it now supports FetchXML.
Used for creating data models for Dynamics 365 in Power BI, the Power Query (M) Builder accelerates the development of the Power Query (M) code, that retrieves the records, change column names and their data types. The tool automates the field selection and the initial data transformation steps that normally has to be done manually.
With the latest version supporting FetchXML, the tool can now import the labels for option sets and lookup fields directly. That means creating additional transformations or DAX formulas are no longer needed just to show the proper label of an option set.
Here’s the latest video showing the updated 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
14 thoughts on “Power Query Builder updated – now supports FetchXML to get your Dynamics 365 Views into Power BI”
Thanks a lot for this tool, very interesting to see this type of m script generation tools!
With regards to accessing dynamics data,
What is now the best/most optimal way to get dynamics data into power BI?
I was using the odata link in Power BI but that was very slow so went for the data export service using an azure sql database.
But looking now at this fetchXML access, what would you feel is the most optimal tool to use to create reports for CRM?
Depends on the amount of records and what you are doing. Using the FetchXML as above is definitely the easiest way to create a data model and significantly faster than working with the OData. But if you count your records in the hundreds of thousands, then the Data Export Service might be a better option.
Very cool tool! Would we still need to create security roles in Power BI for the intended end user or will the filter in the XML take care of it? For example: If Joe pulls up a report in Power BI that uses the data from My Accounts, will it only show his accounts in the Power BI report?
The data is still retrieved in context of the report owner. So using the “My Accounts” view, would be the accounts of the report owner. However, if someone else had the .pbix file and loaded data in their credentials, then it would their accounts.
Awesome, can’t explain how much this will help me.
Thanks so much!
This is a super cool tool – I will share it with everyone. Wouldn’t FetchXML be limited to only getting 50,000 records? or is there something built in to Power Query to handle paging?
Paging is incl. in the fetch part of the Power Query. I think the max I’ve pulled personally this way is a couple of million records. Although if that is your scale, the Data Export Service or Dataflows may be a better option.
Awesome, thank you so much, Ulrik for this tool.
It’s not working on Linked Entities, do I need to do anything else to make it work? Please confirm
Sorry mate, it worked like a charm. Please ignore my message.
Heartful thanks again Ulrik.
Glad it worked out. FYI we are trying to make this more apparent in the next version.
excellent tool, congratulations. and thanks. I make reports for users in 3 languages. It would be useful to be able to select the language in the optionsets.
Thanks Alex. The tool by default only expands the default language. However, you can backstep in the query editor and then expand the other languages as needed. So at least it is a point and click process of getting to them.
Is there any to get back the fetch xml used to generate the M Query.
The problem is I used a personal view to build a query and that personal view has been deleted.
There’s no automated way, but you could reconstruct it from the M query. You’ll need to remove a lot of “” quotation marks and other characters. Might be easier to just rebuild, but the FetchXML is essentially embedded within the Power Query script.