Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (2023)

  • Article
  • 7 minutes to read

You can use Power BI desktop to connect to Azure and get in-depth data about your organization's Azure service usage. With this data, you can create custom reports and measures to better understand and analyze your Azure spend.


There is limited support for the Microsoft Azure Consumption Insights (Beta). For new functionality, use the Azure Cost Management connector for Power BI.

Connect with Azure Consumption Insights

Azure Consumption Insights allows you to connect to Azure Enterprise Agreement billing accounts.

In this section, you learn how to get the data you need migrate using the Azure Enterprise Connector. You'll also find a usage details columns mapping available in the ACI (Azure Consumption Insights) API.

To successfully use the Azure Consumption Insights connector, you need access to the Azure portal Enterprise features.

To use the Azure Consumption Insights connector in Power BI Desktop:

  1. From the Home ribbon, select Get Data.

  2. From the categories on the left, select Online Services.

    (Video) How to Analyze Spending in Power BI with Azure Consumption Insights | Power BI Training

  3. Select Microsoft Azure Consumption Insights (Beta).

  4. Select Connect.

    Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (1)

    In the dialog that appears, enter your Azure Enrollment Number.

    Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (2)

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (3)

This connector version only supports enterprise enrollments from China enrollments aren't currently supported.

Next, provide your Access key to connect.

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (4)

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (5)

Once you provide your Access key and select Connect, a Navigator window appears and shows nine available tables:

BudgetsBudget details to view actual costs or usage against existing budget targets.
MarketPlaceUsage-based Azure Marketplace charges.
PriceSheetsApplicable rates by meter for an enrollment.
RIChargesCharges associated to your Reserved Instances over the last 24 months.
RIRecommendations_SingleReserved Instance purchase recommendations based on your single subscription usage trends over the last 7, 30, or 60 days.
RIRecommendations_SharedReserved Instance purchase recommendations based on your usage trends across all your subscriptions over the last 7, 30 or 60 days.
RIUsageConsumption details for your existing Reserved Instances over the last month.
SummariesA monthly summary for balances, new purchases, Azure Marketplace service charges, adjustments, and overage charges.
UsageDetailsA consumed quantities breakdown and estimated enrollment charges.

You can select a checkbox beside any table to see a preview. You can select one or more tables by checking the box beside their name, then select Load.

(Video) Part 2: Azure Application Insight/Azure Monitor and Power BI integration

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (6)


The Summary and PriceSheet tables are only available for the enrollment-level API Key. Also, the data in these tables has, by default, the current month's data for Usage and PriceSheet. The Summary and MarketPlace tables are not constrained to the current month.

When you select Load, the data is loaded into Power BI Desktop.

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (7)

Once the data you selected is loaded, the tables and fields you selected can be seen in the Fields pane.

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (8)

Using Azure Consumption Insights

To use the Azure Consumption Insights connector, you access to the Azure portal Enterprise features.

Once you successfully load data using the Azure Consumption Insights connector, you can create your own custom measures and columns using Power Query Editor. You can also create visuals, reports, and dashboards for sharing in the Power BI service.

With a blank query, you can retrieve a sample Azure custom queries collection. There are two ways you can do this retrieval:

In Power BI Desktop:

(Video) Using Power BI to Monitor your Azure Data Platform (with Dave Ruijter)

  1. Select the Home ribbon
  2. Select Get Data > Blank Query

Or, in Power Query Editor:

  1. Right-click in the left Queries pane
  2. Select New Query > Blank Query from the menu that appears

In the Formula bar, type: = MicrosoftAzureConsumptionInsights.Contents.

The following image shows a samples collection that appears.

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (9)

When working with reports and creating queries, you can:

  • To define the number of months starting from the current date, use numberOfMonth
    • Use a value between one and 36. Represent the number of months, from the current date, you want to import. We recommend getting no more than 12 months of data. This limit avoids Power BI query import constraints and data volume thresholds.
  • To define a period of months in a historical time window, use startBillingDataWindow and endBillingDataWindow
  • Don't use numberOfMonth together with startBillingDataWindow or endBillingDataWindow

Migrate from the Azure Enterprise Connector

Some customers created visuals using the Azure Enterprise Connector (Beta). Eventually, it will be replaced with the Azure Consumption Insights connector. The new connector has features and enhancements that include:

  • Additional data sources available for Balance Summary and Marketplace Purchases
  • New and advanced parameters, such as startBillingDataWindow and endBillingDataWindow
  • Better performance and responsiveness

The next steps show how to transition to the Azure Consumption Insights connector. These steps preserve the work you've already done in creating custom dashboards or reports.

Step 1: Connect to Azure using the new connector

The first step is use the Azure Consumption Insights connector described in detail earlier in this article. In this step, select Get Data > Blank Query from the Home ribbon in Power BI Desktop.

Step 2: Create a query in Advanced Editor

In Power Query Editor, select Advanced Editor from the Home ribbon's Query section. In the Advanced Editor window that appears, enter this query:

let enrollmentNumber = "100", optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ], data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters) in data

Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (10)

You'll need to replace the enrollmentNumber value with your enrollment number. You can get your number from the Azure Enterprise Portal. The numberOfMonth parameter is how many months of data you want going back, from the current dat. Use zero (0) for the current month.

Once you select Done in the Advanced Editor window, the preview refreshes and data from the specified month range appears in the table. Select Close & Apply and return.

(Video) Consume Azure Machine Learning models in Power BI

Step 3: Move measures and custom columns to the new report

Next, you'll need to move any custom columns or measures you created into the new details table. Here are the steps.

  1. Open Notepad (or another text editor).

  2. Select the measure you want to move, copy the text from the Formula field, and place it in Notepad.

    Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (11)

  3. Rename Query1 to the original details table name.

  4. To create new table measures and custom columns, right-click on your table, and choose New Measure. Then, cut and paste your stored measures and columns until they're all done.

Step 4: Relink tables that had relationships

Many dashboards have additional tables that are used for lookup or filtering, such as date tables or tables used for custom projects. Reestablishing those relationships resolves most remaining issues. Here's how to do it.

  • In the Modeling tab in Power BI Desktop, select Manage Relationships to bring up a window that lets you manage relationships within the model. Relink your tables, as needed.

    Connect to Azure Consumption Insights data in Power BI Desktop - Power BI (12)

Step 5: Verify your visuals, and adjust field formatting as needed

At this point, most of your original visuals, tables, and drill-down actions should be working as expected. Some minor tweaks may be necessary, however, to precisely format the look and feel. Take a bit of time to look over each of your dashboards and visuals to ensure they look how you want them.

Using the Azure Consumption and Insights (ACI) API to get consumption data

Azure also provides the Azure Consumption and Insights (ACI) API. You can create your own custom solutions to gathering, reporting, and visualizing Azure consumption information using the ACI API.

Mapping names and usage details between the portal, the connector, and the API

The Azure portal columns and details names are similar in the API and the connector, though not always identical. To help clarify, the following table provides a mapping. Also indicated is whether the column is obsolete. For more information, and term definitions, see the Azure billing data dictionary.

(Video) Log Analytics + Power BI - Better Together!!!

ACI Connector / ContentPack ColumnNameACI API Column NameEA Column NameObsolete / Present for backward compatibility
AccountNameaccountNameAccount NameNo
Consumed QuantityconsumedQuantityConsumed QuantityNo
Consumed ServiceconsumedServiceConsumed ServiceNo
Cost CentercostCenterCost CenterNo
DepartmentNamedepartmentNameDepartment NameNo
Instance IDYes
InstanceIdinstanceIdInstance IDNo
Meter CategorymeterCategoryMeter CategoryNo
Meter IDYes
Meter NamemeterNameMeter NameNo
Meter RegionmeterRegionMeter RegionNo
Meter Sub-CategorymeterSubCategoryMeter Sub-CategoryNo
MeterIdmeterIdMeter IDNo
Resource GroupresourceGroupResource GroupNo
Resource LocationresourceLocationResource LocationNo
Store Service IdentifierstoreServiceIdentifierStore Service IdentifierNo
Subscription NamesubscriptionNameSubscription NameNo
Unit Of MeasureunitOfMeasureUnit Of MeasureNo

Next steps

You can connect to many different data sources using Power BI Desktop. For more information, see the following articles:

  • Connect to Azure cost management data in Power BI desktop
  • What is Power BI Desktop?
  • Data Sources in Power BI Desktop
  • Shape and Combine Data with Power BI Desktop
  • Connect to Excel workbooks in Power BI Desktop
  • Enter data directly into Power BI Desktop


1. Power BI with Synapse
2. Azure DevOps Integration with Power BI | Azure DevOps Analytics to Power BI Dashboards
(Ravikanth FicusRoot - Tech Videos)
3. Microsoft Azure: Connecting to Power BI
4. Tracing with Log Analytics and Power BI
(Guy in a Cube)
5. Azure Cost Management - Power Bi
(Hello World)
6. Power BI and D365 Business Central: Connecting Your Data
(DynamicsCon hosted by Dynamics User Group)
Top Articles
Latest Posts
Article information

Author: Rubie Ullrich

Last Updated: 02/23/2023

Views: 5953

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Rubie Ullrich

Birthday: 1998-02-02

Address: 743 Stoltenberg Center, Genovevaville, NJ 59925-3119

Phone: +2202978377583

Job: Administration Engineer

Hobby: Surfing, Sailing, Listening to music, Web surfing, Kitesurfing, Geocaching, Backpacking

Introduction: My name is Rubie Ullrich, I am a enthusiastic, perfect, tender, vivacious, talented, famous, delightful person who loves writing and wants to share my knowledge and understanding with you.