Power Query Builder is a great tool inside of Power BI. In this post I’d like to discuss using the Power Query Builder to help leverage Power BI against your Power Apps databases, including Dynamics 365, both CRM and financials databases, or any data house in the Common Data Service (CDS). Here’s a brief demo (also see my video included at the end of this post):

  • The Power Query Editor is part of a suite of tools in XRM toolbox  which you can download for free at this address https://www.xrmtoolbox.com/. This product is a collection of over 150 tools created by independent developers to be used in what was Dynamics CRM and now known as Dynamics 365 CRM. Many of these tools can now be leverage against any data in the Common Data Service (CDS).
  • I can find the Power Query Builder tool here with the search bar. When I click on the tile for the Power Query Builder, a message will come up asking if I want to connect to an organization. I click on yes and I can create a new connection with the Connection Wizard if I don’t already have one established. Then just click connect.
  • From here a page will open for the Power Query Builder. The first step here is to load the entities from your data source. When you click on Load Entities in the tool bar you’ll get an alphabetized list of your entities. There’s also a search box with which you can search by the common name or the logical name.
  • In my example, I choose accounts. In the top section, you’ll see all the views that have been created against that entity. When you click a view, you will see all the fields in that view in the Selected Field box.
  • In the All Fields box you can pick and choose any field you want to add to your report from the list.
  • When you’re ready to create the M, you simply go to the tool bar and click Update Fetch Xml and then click Generate Fetch xML. This will give you M code that can be copy/pasted directly into the advanced editor on a blank query in Power BI.

The Power Query Builder is the fastest way to get a quick start on your Power BI reports, and will easily cut your development time in half. Once you have all this data connected in Power BI, you can use the Power Query Editor to do any additional manipulation with your data.

If you’d like to learn more about this tool or about leveraging Power BI in your business, our Power BI experts can help. Data and the cloud is what we’re all about and we are here to help with any Azure product or service.

Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or  [email protected].