Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

HCLTech Microsoft Business Applications Practice Blog

for Dynamics 365, Power Platform and the rest of the Microsoft technology stack

Analyze that Data with Microsoft Power Query for Excel

Post Author: HCLTech |

Microsoft Power Query for Excel is not only one the most effective ways to view and analyze data from Dynamics CRM, but it is also a flexible and easy solution to turn Excel spreadsheets into exciting Business Intelligence (BI) tools for Dynamics CRM. In today's blog, we'll be showing you how to use this cool tool!

For today's example, we will be using Dynamics CRM 2016 Online and Excel 2016. Before you can use Excel to connect to Dynamics CRM, you'll need to download and install Microsoft Power Query for Excel. You can do that here. Be sure to check if your Microsoft Office is 32 or 64 bits to make sure you are downloading the correct version of the Power Query for Excel.

Find the Correct Service URL

To be able to connect to Dynamics CRM, first you need to find out the correct Service Root URL.

1. Click on Main > Settings > Customizations.

Microsoft Power Query for Excel

2. Select Developer Resources.

Microsoft Power Query for Excel

3. Copy the URL under Instance Web API. This URL will be used in the next steps to connect to CRM.    

Microsoft Power Query for Excel

Connect to Dynamics CRM Online

Now we need to open Excel and create a connection to Dynamics CRM Online as follows:

1. Click on Data > New Query > From Other Sources > From OData Feed.

Microsoft Power Query for Excel

2. On the OData Feed window, enter the URL you just copied from Developer Resources and click OK.

Microsoft Power Query for Excel

3. On the Access an OData feed window, click on Organization account, choose the first URL, and click on Sign-in.

Microsoft Power Query for Excel

4. On the login page, enter your email and password and click on Sign in.

Microsoft Power Query for Excel

5. Back on the Access an OData feed window, click on Connect.    

Microsoft Power Query for Excel

Now the Fun Begins!

Once the connection is established and all the metadata is loaded, you will see the Navigator window. In this window, you can choose which entities to pull data from. You can also choose which columns to display, apply filters to, etc.

1. On the left panel, click on Accounts. You'll notice that a few records are retrieved. Click on Edit.

Microsoft Power Query for Excel

Once the Query Editor opens, explore the Home, Transform, Add Column and View menu options. Now let's make sure we retrieve only a few columns to make sure it is more manageable once we load data from CRM into Excel.

2. Click on Choose Columns.

Microsoft Power Query for Excel

3. In the Choose Columns window, check (Select All Columns) to uncheck the columns initially selected.

Microsoft Power Query for Excel

4. Check the following columns (use the Search Columns field to make it easier to find the fields you want to include in the result):

  • accountnumber (Account Number)
  • name (Account Name)
  • telephone1 (Main Phone)
  • address1_city (Address 1: City)
  • emaildaddress1 (Email)
  • revenue (Annual Revenue)

5. Click Ok. Drag and drop the columns horizontally if you would like to reorder them.

6. Click on Close & Load to load the data into Excel.

Microsoft Power Query for Excel

Once the data is displayed in Excel, it should look like this:

Microsoft Power Query for Excel

At this point, you can use Excel functionalities, including filtering, ordering, formatting, etc. to slice and dice the data.

Microsoft Power Query for Excel

Refresh Your Data in Excel

One of the best features in Power Query is that the data doesn't have to be static. If any update is made in Dynamics CRM, the data can be easily refreshed in Excel.

For example, say you update the Annual Revenue for one of the Accounts in CRM. We updated the Annual Revenue from $10,000.00 to $50,000.00 for Coho Winery (sample).

In Excel, under Workbook Queries, click on the refresh button for Accounts (it can take some time to refresh). You'll notice that the Annual Revenue you updated in CRM is now updated in Excel as well. The chart based on Annual Revenue was also updated accordingly.











That's all for the blog today! Want to learn more about BI? Check out our Webinar on Demand "Power BI Showcase" to get introduced to Power BI by showing you various examples of how it can be used to surface and visualize critical business metrics–providing insights that help you run your business and make decisions.

Happy CRM'ing!

By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

2 comments on “Analyze that Data with Microsoft Power Query for Excel”

  1. Hi Nelsie,

    You can expand the record field using query editor or take the field as
    #"Expanded new_ProjectManager" = Table.ExpandRecordColumn(#"Expanded createdby", "new_ProjectManager", {"fullname"}, {"new_ProjectManager.fullname"}),

PowerObjects Recommends