Power Query Builder

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 

136 thoughts on “Power Query Builder”

    • 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.

      Reply

  3. 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.

    Reply

    • 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.

      Reply

  4. 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!

    Reply

    • 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.

      Reply

    • Hi Rajul. Yes, it works for 8.2 as well. However, it does require that you are using a recent version of the Power BI Desktop, so if that is more than 2-3 months old, you may have to update. Just as a note, future versions of the tool will have features that are only available for 9.0 and above, but the current/standard feature set will continue to work.

      Reply

      • Hello, thanks for the response. I need data for audits. It seems to be working for 9.0 but doesn’t work for 8.1 instance. Do you have any idea why it isn’t working?

      • I am having issues with a ‘DataFormat.Error: We found extra characters at the end of JSON input’ error when trying to pull in the fetch XML. I am on version 8.1 on premise. If there are any small fixes I can make that would be great!

  12. hi
    thanks for the great tool.
    please add a part to accept custom FetchXML queries and not only accept views
    i need to create a fetchXML Main query from a complex query i generated by FetchXML Query Builder
    Thanks,
    Hassan

    Reply

  13. Hi,
    This tool is fantastic, a huge THANK YOU to you and your contributors!!
    I pulled a FetchXML query that had “OR” Filters across two different Entities, the result pulled through multiple of same records, I couldn’t see a Distinct=True in the Generated Query so added it in front of Page code (

    Reply

  14. This is a game-changer for me and my clients. Much more elegant way to pull in Dynamics CRM data and transform data in Power BI. Really impressed and hope you keep developing this awesome tool!

    Reply

  15. Has anyone else experience issues with custom date fields not updating when using Power Query Builder?
    I have a bunch of custom date fields on my Opportunity entity and for some reason they are not updated in Power BI.
    Other standard date fields as est. close date etc. are updated as supposed.

    Reply

  16. Follow-up on my last post.

    Sometimes I get an error instead:

    DataFormat.Error: We couldn’t parse the input provided as a Date value.
    Details 2018-10-31T07:00:00Z

    I’ve tried messing around with the Date/time field settings in Dynamics and also the data type in Power BI without any luck.

    I’m not able to spot any patterns.

    Reply

    • If you open the advanced editor and find the #”Changed Type” step – you can see that your custom datetime field is changed to date. if you change it to datetime instead – it works.

      #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,
      {“Process Stage Last Changed”, type datetime},

      Reply

    • I get this error all the time. What I Do is find the last line in the applied steps where I don’t have an error for the date field and I insert a Powerbi command to extract before T and then everything is fixed.

      Reply

  17. Hi Ulrik,

    Loving the tool. however, when trying to use the new “Copy FetchXml” functionality I get and error when adding a query from the asyncoperation table (System Jobs).
    I have exported this query from CRM, used the “Open Fetch Xml Builder”, opened the query and ran successfully. When using the “Return FetchXML” function PowerQuery fails with the following message.

    “System.Exeption: Error while retrieving views: Object reference not set to an instance of an object……

    Reply

  18. Hi,
    We have working Queries in PBI Desktop (using Power Query Builder), however after Publishing to PBI Online and setting up a Scheduled Refresh (Using the On-Premise Data Gateway) we get an error:
    “We found extra characters at the end of JSON Input.”

    Is anyone else experiencing this? Does the Json query need to be different for Web version of PowerBI??

    Thanks
    Clayton

    Reply

    • Hey, I’ve heard this reported before, but haven’t seen it myself. Let me know if you locate the issue, and we’ll try to update the tool if possible. I do think it is specific to on-premise, as I have not seen issues with online. Will definitely share any findings on this if they come along.

      Reply

  19. Hi Ulrik

    I just updated to version 1.2018.10.29

    Custom Picklists are not being generated correctly through the “Generate OptionSets”. Status and StatusReason works and standard option sets – but not custom picklists.

    Error: Expression.Error: Vi kan ikke anvende feltadgang på typen Null.
    Detaljer:
    Value=
    Key=Options

    In the first step “source” the OptionSet is null

    For optionsets that work is says Record.

    Reply

  20. Hi Ulrik,

    Love the tool! I just updated to 1.2018.1014.5 and when I Generate FetchXml I no longer have the Filter pane to filter the data. Has this been removed or can be accomplished in another way?

    Thanks

    Eric

    Reply

      • Hi Ulrik, thank you for this fantastic tool! I still cannot see the filter from the view of the FetchXMLBuilder. I couldn’t find it either in the generated query. Am I missing anything here?

        Thank you!
        CK

      • Hi Carlos. The filter definitely should be in the generated query, although you may not see it in the interface earlier. Sometimes the formatting makes the filter difficult to see.

  21. Hi Ulrik

    How do you recommend getting the Process Stage on Opportunity into PowerBI – and sorted?

    If I use Stepname/Pipeline Phase – it is prefixed with a number – and the field does not always seem to be up to date in CRM. – Also the same phase can have different numbers and some phases can have the same number. It is a CRM system with many iterations of the BPF…

    If I use a view for Opportunity and include the Stage Category from the Process Strage – the view will have data in CRM, but not Power BI if I use Power Query Builder.

    So I include the PBF entity for opportunity as a table. This works fine.
    Then I include the BPF entity for lead as a table.
    Then I combine the two in a merged table so I can link to both lead and opportunity.
    Then I add a new coloum (from example) and add the sort numbers of the stages. And then I sort the Active Stage coloum by the new sort coloum.

    This works.

    But the question is – is there an easier way to do it? How do you get the Process Stage into Power BI?

    Reply

  22. Hi Ulrik,

    What a bloody brilliant tool 🙂 As a business person and not a data analyst I am challenged to find ways to improve the views and dashboards beyond what is natively possible in Dynamics. I have been using your guide to update the charts to make them really useful and now discovered this wonderful tool. But again, it would still be way beyond me if not for your incredible ‘tutorials’ that take things which are relatively sophisticated and make them seem much easier.

    Big thanks!

    Reply

  24. Hi, Ulrik. Fantastic tool. Quick question. Have you heard of users getting errors from D365 around having the connection forcibly closed? There is a fair amount of data in D365 prod (a few entities each with 100k – 200k records), but nothing crazy. I keep getting errors for different entities along the lines of “The request was aborted: the connection was closed unexpectedly” or another, “unable to read data from the transport connection: an existing connection was forcibly closed by the remote host.”

    These errors do not come up in the dev environments where there is less data, so this is leading me to think there might be a time out issue on the part of D365. I am wondering if you’ve talked to anyone else who has encountered this issue?

    Thanks! Scott

    Reply

  25. Wow! What an excellent tool! Not sure how I can survive Power BI without this tool now. 🙂 Thank you so much!!! You guys are magicians.

    Reply

  26. I have a question. Can we publish the charts to CRM online and the user can use power bi charts in MS CRM without locally running XRM toolbox and Power BI desktop client? Also is XRM toolbox compatible with the web version of power bi or does it need the local install of power bi desktop. Thanks in advance

    Reply

  27. This tool has really changed my relationship with Power BI. THANK YOU!!
    That said, I’m working on a new project and I am getting a weird error,
    DataFormat.Error: We found extra characters at the end of JSON input

    The generated query looks like this and looks like every successful use of this tool that I’ve ever done.
    When I put it in a JSON validator I get Error: Parse error on line 1: let GetResults=(z as ^ Expecting ‘STRING’, ‘NUMBER’, ‘NULL’, ‘TRUE’, ‘FALSE’, ‘{‘, ‘[‘, got ‘undefined’
    But, I put in a query that didn’t error out and got the same thing from this validator.

    ANY IDEAS??

    let
    GetResults = (z as text, x as number) =>
    let
    S = Json.Document(Web.Contents(ServiceRootURL & “/ipm_jobs”, [Headers=[Prefer=”odata.include-annotations=*”],Query=[fetchXml=”

    “]])),

    Reply

  28. I just read another comment about this error and YES, this is from an on prem installation. IS there any way to use your tool with on-prem CRM?

    Reply

  29. More info…the Service Root URL is correct based on the developer’s tools in CRM. I’m not sure I even remember how to load data the hard way! LOL!

    Reply

  30. Hi Ulrik and thanks for a great tool!

    Just a question, I’m not sure if I’m doing it wrong or if it’s at all possible, but I’d like to “replicate” a sales person dashboard in Power BI and set it up in a D365 Dashboard. The original dashboard, of course, is mainly using the “my open opportunities” fetch xml. I hoped maybe the fetch xml would run inside a PBI report created with the tool, but it appears to run as the user creating the dashboard (me). Is there any way of getting this to work, without going into row level security?

    Reply

    • Hi Jostein. Yes, the data is always queried in the context of the report owner. You can create DAX Measures with KPIs like My Est. Sales, Count of My Opportunities by using the USERPRINCIPALNAME function. That returns the domainname (emailaddress) of the logged in user, and you can match it against the user table. I.e. My Est. Revenue = CALCULATE(SUM(Opportunity[Est. Revenue]),Owner[Domainname]=USERPRINCIPALNAME())
      When it comes to showing a table, DAX won’t be able to filter based on the USERPRINCIPALNAME, so the only route there, that I know of, is using Row Level Security.

      Reply

      • Thanks for the feedback and tip, Ulrik, I’ll go play with the USERPRINCIPALNAME, didn’t think of that. Long term, I’m hoping Microsoft comes up with even tighter and improved integration with PBI, as the regular D365 charts and Dashboards seem to be slightly fading into the background of the power platform.

  32. […] As usual in these situations, the wonderful CRM/D365CE community has delivered a solution to address the first issue raised above. The Power Query (M) Builder tool is a handy plugin within the XrmToolBox that allows you to generate M query code snippets that you can use within Power BI Desktop. Most importantly, the tool incorporates a solution from Keith Mescha and the former Sonoma Partners Power BI Accelerator to get around the paging issue and allow you to return unlimited data from the application. You can find out more about the tool by checking out Ulrik “The CRM Chart Guy” Carlsson’s blog post dedicated to this very subject. […]

    Reply

  33. I do not see the possibility to pull in Audit data. I have a short string of FetchXML that works in the FetchXML tester, but I can’t figure out how to use it in conjunction with this tool.

    Is there a tool to inject FetchXML into PowerBI?

    Reply

    • Hi Paul. There’s an Edit FetchXML button where you can paste the FetchXML. You can also integrate with the FetchXML Builder. However, it currently won’t work with entities not shown in Advanced Find. We are working on this though. I am curious about the Audit data you are retrieving. I’ve been able to pull the data, but I have not been able to retrieve the before and after values. Did you manage to get those values too?

      Reply

      • Ulrik, thanks for your reply. I was able to finally figure it out after about 4 hours of reading and combining methods from various sites. Yours was a great help along with XRMToolbox.

        I was trying to build a user audit report so that we can see which users are not logging in and then send that to the managers to make a decision on keeping that license active or encouraging the users to use the tool.

        I was pulling the full audit data from Dynamics, but the logins were masked as user “SYSTEM”. So, my report was only able to show Create/Updates actually done in Dynamics. After Using the XML code I was able to swap in “audits” and then used that in Power Query Builder to pull the data I needed.

        My report is no longer overloaded with unnecessary information and I have just what I needed to get started!

        Example of the FetchXML I used for this:

        1
        2
        3
        4
        5
        44
        45
        61
        64
        65

        Thank you for your posts!

  34. This is a fantastic tool and it is saving me hours of work. I would love to have the ability to save/recover views more easily. For example, I have a report that I know needs several different CRM views or custom FetchXML queries. After I have created all the queries in Power Query and then start to relate them together in PBI, then I find that I am missing some columns, so I need to start from scratch on one or more of my views in PQMB. I started saving all my FetchXML queries in a file, but it would be super cool if you put the FetchXML into comments (or an expression) in the M code so that if I had to reverse engineer then I can quickly get back to what I had before to add the missing field.

    Reply

    • Hi Nelson. That’s a good idea. I know Mohamed has been thinking about implementing something similar. However, the query is constructed so it should be possible to go back and manually add more fields in the FetchXML part, and then modify the expand column step in the query editor. Modifying the fetch is not needed though if you are using the all-attributes setting. Then you can add all fields via the expand column step. You of course have go through each step and rename, change data type too.

      Reply

      • Once I have created a PBI report and delivered it to a client, I can teach them how to use FetchXML builder and copy /paste the source FetchXML. Many of my queries pull from multiple tables in CRM so I was struggling with having to start over again, or having to read the M Query to figure out what the original queries were. I created a blog post with my suggestion here: https://eccosystem.blogspot.com/2019/04/making-powerbi-easier.html

  35. Thank you for supplying this video! I am trying to create my first Power BI query. When I open the tool in the XRM Toolbox and copy the Service URL to the Power BI Desktop, I receive an error that says: Expression Error: The name ‘Dyn365CEBaseURL’ wasn’t recognized. Make sure it is spelled correctly.

    I am not sure with to do with this as I copied it form what was supplied on the XRM Toolbox on the Service Root URL tab. Any ideas? Thank you!

    Reply

      • Wow – thank you for your quick reply! I think i figured that out and now I need I receive an error and it tells me i need to create parameters?? This is the error a
        Expression.Error: The import ServiceRootURL matches no exports. Did you miss a module reference?

        When I click Go to Error:It says Enter parameters Z (Example: abc) x (Example 123) Invoke Clear and under that it says “function (z as text, x as numbers) as any.

        I am sure I am just a total rookie and have no idea what I am doing, but this is my first try at doing this!

  36. This is what shows in my ServiceRootURL on the XRM Toolbox – is this exactly how I enter it on the Query in Power Query Builder? =Dyn365CEBaseURL & “/api/data/v9.1”

    I did click on “Generate Service URLs” and on the Base URL tab it just provided our normal D365 URL.

    Reply

    • I figured it out! I did not name the queries exactly as you specified and I that seems to have corrected it! I am just trying to get my corporate credentials accepted now. sorry to have bothered you! A coworker and I met you in Phoenix at the ClickDimensions Happy Hour! See you again in Orlando?

      Reply

      • Hi Laura. Glad you figured it out. Yes, they have to be named precisely. The base url is needed separately because it is also used when generating the link back to the Dynamics record later.

  37. I got it! I did get my results and all looks good except a date field. We just use a normal date field (due date for the opportunity) and i receive this error:

    DataFormat.Error: We couldn’t parse the input provided as a Date value.
    Details:
    2018-12-05T06:00:00Z

    Reply

    • A few of the date field types don’t like our standard setting. In the Query Editor, you can select the change type step, and give it a different date formatting in the interface. You should be fine just selecting date, instead of date time.

      Reply

      • I am not sure where the query editor is, but on my opportunity date, we already have it set to DateOnly. So sorry I have been a pain and am so clueless on this stuff!

  39. Hi Ulrik,

    I’m wondering if you’ve seen this and can offer any assistance as it doesn’t pertain to the Power Query tool exactly. I use the Power Query editor, which is awesome btw, to obviously pull the data into Power BI, then publish to the Web version and then set the scheduled refresh so my reports are up to date. This works fine when I publish to My Workspace but I’ve also created some App Workspaces to easily share with different groups of people. The issue that I recently ran into is that my password expired so I need to re-enter my credentials but the Data Source Credentials option is grayed out for any datasets that I published to the App Workspaces. All of the others in My Workspaces allow me to re-enter my credentials.

    If you have any thoughts I’d appreciate it.

    Thanks

    Eric

    Reply

    • Hi Eric.
      Is this a recent development. I noticed something similar in the last week, but have otherwise had datasets in other workspaces many times in the past.
      Have you tried using the new version of the tool that uses RelativePath on the Query. I’ve seen some performance improvements with that specifically on that data scheduling screen.

      Reply

      • Thanks Ulrik, This ended up being an issue with some Excel files that I was connecting to in OneDrive. All is good now.

  42. Hi Ulrik,

    Have you had any success loading teammemberships into the tool? This entity obviously doesn’t show up in the dropdown but was able to use the FetchXml builder to create the FetchXml but when I try to load it into the Power Query builder I get an error.

    Thanks

    Eric

    Reply

  43. I followed the video step by step but keep running into the same issue listed above saying: DataFormat.Error: We found extra characters at the end of the JSON input. Details: Value= Position=1

    Any updates on what is causing this issue and how to resolve? It is an on prem IFD instance.

    Reply

  44. This is super exciting! Thank you for this! I am stumped though. I have a customized MS Dynamics CRM (CloudNine) and am trying to figure out how to create queries for the fields the customized site. Eg. Accounts is called Company so I can see that, but I can’t identify the custom entities from the list that pulls from clicking “Load Entities” in the Power Query Builder. Any ideas on how to load the custom entities?

    Reply

  46. This works great for my online CRM but It having big problems with on-prem.
    Doing everything the same, when I create a query to load the XML into I get an error.

    View post on imgur.com

    DataFormat,Error: We found extra characters at the end of the JSON input.
    I know you have noticed this as a problem, but i would like to attempt to fix this.
    Can you explain what “GetResults = (z as text, x as number) =>” is actually doing?

    Reply

    • Yes, they are a part of handling the pagination so it is not limited to 5k records. z is pagingCookie and x is pageNumber. Hope that helps. If you do find a solution, we’d be happy to implement it in the tool if possible.

      Reply

  47. This is great but I can’t get it to load after applying changes.
    I have the BaseURL, RootURL and 3 queries

    It takes a really long time, then gives me this error:
    Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [DataSource.Error] The request was aborted: The request was canceled..
    OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.
    The current operation was cancelled because another operation in the transaction failed.
    ‘.

    Reply

  48. I removed one query and got this:
    Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [DataFormat.Error] We reached the end of the buffer..
    The current operation was cancelled because another operation in the transaction failed.
    ‘.

    Then I removed another query and got this.
    Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [DataSource.Error] Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
    ‘.

    I removed the last query and checked if an error on URLs (they looked ok), confirmed when apply changes, no error. they are fine.

    So it’s all the queries.

    Thank you for any suggestions.

    Reply

  50. Something I noticed, we have multiple CRMs and this one is a new instance. I tried it on the other one and it worked ok. I used a small view to test it which may have had something to do with it but wondering, is there anything that needs to be turned “on” to make it work or could it be size?

    Reply

      • It’s a whole separate CRM so no crossover could occur. I created views and was using those. I did see your post today about other ways to bring in Dynamics 365 data to Power BI. I have always used the online connector until the data is in our EDW but will try CDS connector. I wish I could figure out why FetchXML won’t work, I was thrilled about this. Thank you for your instructions and time about this anyway!

Leave a reply to Britni Block Cancel reply