Create visuals and reports with the Microsoft Cost Management connector in Power BI Desktop
Article
You can use the Microsoft Cost Management connector for Power BI Desktop to make powerful, customized visualizations and reports that help you better understand your Azure spend.
The Microsoft Cost Management connector currently supports customers with:
The Microsoft Cost Management connector uses OAuth 2.0 for authentication with Azure and identifies users who are going to use the connector. Tokens generated in this process are valid for a specific period. Power BI preserves the token for the next sign-in. OAuth 2.0, is a standard for the process that goes on behind the scenes to ensure the secure handling of these permissions. To connect, you must use an Enterprise Administrator account for Enterprise Agreements, or have appropriate permissions at the billing account or billing profile levels for Microsoft Customer Agreements.
Connect using Microsoft Cost Management
To use the Microsoft Cost Management connector in Power BI Desktop, take the following steps:
In the Home ribbon, select Get Data.
Select Azure from the list of data categories.
Select Microsoft Cost Management.
In the dialog that appears, for the Choose Scope drop down, use Manually Input Scope for Microsoft Customer Agreements, or use Enrollment Number for Enterprise Agreements (EA).
Connect to a Microsoft Customer Agreement account
This section describes the steps necessary to connect to a Microsoft Customer Agreement account.
Connect to a billing account
To connect to a billing account, you need to retrieve your Billing account ID from the Azure portal:
In the Azure portal, navigate to Cost Management + Billing.
Select your Billing profile.
Under Settings in the menu, select Properties in the sidebar.
Under Billing profile, copy the ID.
For Choose Scope, select Manually Input Scope and input the connection string as shown in the following example, replacing {billingAccountId} with the data copied from the previous steps.
/providers/Microsoft.Billing/billingAccounts/{billingAccountId}
Alternatively, for Choose Scope, select Enrollment Number and input the Billing Account ID string as copied from the previous steps.
Enter the number of months and select OK.
Alternatively, if you want to download less than a month's worth of data you can set Number of months to zero, then specify a date range using Start Date and End Date values that equate to less than 31 days.
When prompted, sign in with your Azure user account and password. You must have access to the Billing account scope to successfully access the billing data.
Connect to a billing profile
To connect to a billing profile, you must retrieve your Billing profile ID and Billing account ID from the Azure portal:
In the Azure portal, navigate to Cost Management + Billing.
Select your Billing profile.
Under Settings in the menu, select Properties in the sidebar.
Under Billing profile, copy the ID.
Under Billing account, copy the ID.
For Choose Scope, select Manually Input Scope and input the connection string as shown in the following example, replacing {billingAccountId} and {billingProfileId} with the data copied from the previous steps.
When prompted, sign in with your Azure user account and password. You must have access to the Billing profile to successfully access the billing profile data.
Connect to an Enterprise Agreement account
To connect with an Enterprise Agreement (EA) account, you can get your enrollment ID from the Azure portal:
In the Azure portal, navigate to Cost Management + Billing.
Select your billing account.
From the Overview blade, copy the Billing account ID.
For Choose Scope, select Enrollment Number.
In Scope Identifier paste the billing account ID copied in the previous step.
Enter the number of months and then select OK.
When prompted, sign in with your Azure user account and password. You must use an Enterprise Administrator account for Enterprise Agreements.
Data available through the connector
Once you successfully authenticate, a Navigator window appears with the following available data tables:
Table
Account Type
Supported Scopes
Description
Balance summary
EA only
EA Enrollment
Summary of the balance for the current billing month for Enterprise Agreements (EA).
Billing events
MCA only
Billing Profile
Event log of new invoices, credit purchases, etc. Microsoft Customer Agreement only.
Budgets
EA, MCA
EA Enrollment, MCA Billing Account, MCA Billing Profile
Budget details to view actual costs or usage against existing budget targets.
Charges
MCA only
MCA Billing Profile
A month-level summary of Azure usage, Marketplace charges, and charges billed separately. Microsoft Customer Agreement only.
Credit lots
MCA only
MCA Billing Profile
Azure credit lot purchase details for the provided billing profile. Microsoft Customer Agreement only.
Pricesheets
EA, MCA
EA Enrollment, MCA Billing Profile
Applicable meter rates for the provided billing profile or EA enrollment.
RI charges
EA, MCA
EA Enrollment, MCA Billing Profile
Charges associated to your Reserved Instances over the last 24 months. This table is in the process of being deprecated, use RI transactions instead
RI recommendations (shared)
EA, MCA
EA Enrollment, MCA Billing Profile
Reserved Instance purchase recommendations based on all your subscription usage trends for the last 30 days.
RI recommendations (single)
EA, MCA
EA Enrollment, MCA Billing Profile
Reserved Instance purchase recommendations based on your single subscription usage trends for the last 30 days.
RI transactions
EA, MCA
EA Enrollment, MCA Billing Profile
List of transactions for reserved instances on billing account scope.
RI usage details
EA, MCA
EA Enrollment, MCA Billing Profile
Consumption details for your existing Reserved Instances over the last month.
RI usage summary
EA, MCA
EA Enrollment, MCA Billing Profile
Daily Azure reservation usage percentage.
Usage details
EA, MCA
EA Enrollment, MCA Billing Account,MCA Billing Profile
A breakdown of consumed quantities and estimated charges for the given billing profile on EA enrollment.
Usage details amortized
EA, MCA
EA Enrollment, MCA Billing Account,MCA Billing Profile
A breakdown of consumed quantities and estimated amortized charges for the given billing profile on EA enrollment.
You can select a table to see a preview dialog. You can select one or more tables by selecting the boxes beside their name and then select Load.
When you select Load, the data is loaded into Power BI Desktop.
When the data you selected is loaded, the data tables and fields are shown in the Fields pane.
Considerations and limitations
The following considerations and limitations apply to the Microsoft Cost Management data connector:
The Microsoft Cost Management data connector doesn't work with Office 365 GCC customer accounts.
Data refresh: The cost and usage data is typically updated and available in the Azure portal and supporting APIs within 8 to 24 hours, so we suggest you constrain Power BI scheduled refreshes to once or twice a day.
Data source reuse: If you have multiple reports that are pulling the same data, and don't need more report-specific data transformations, you should reuse the same data source, which would reduce the amount of time required to pull the Usage Details data.
For more information on reusing data sources, see the following:
You might receive a 400 bad request from the RI usage details when you try to refresh the data if you've chosen date parameter greater than three months. To mitigate the error, take the following steps:
In Power BI Desktop, select Home > Transform data.
In Power Query Editor, select the RI usage details semantic model and select Advanced Editor.
Update the Power Query code as shown in the following paragraphs, which split the calls into three-month chunks. Make sure you note and retain your enrollment number, or billing account/billing profile ID.
Once you've updated the code with the appropriate update from the previous step, select Done and then select Close & Apply.
You might run into a situation where tags aren't working in the usage details or the tags column can't be transformed to json. This issue stems from the current UCDD API returning the tags column by trimming the start and end brackets, which results in Power BI being unable to transform the column because it returns it as a string. To mitigate this situation, take the following steps.
Navigate to Query Editor.
Select the Usage Details table.
In the right pane, the Properties pane shows the Applied Steps. You need to add a custom column to the steps, after the Navigation step.
From the menu, select Add column > Add custom column
Name the column, for example you could name the column TagsInJson or whatever you prefer, and then enter the following text in the query:
```= "{"& [Tags] & "}"
Completing the previous steps creates a new column of tags in the json format
You can now transfer and expand the column as you need to.
Authentication issues encountered with Microsoft Entra guest accounts: You may have the appropriate permissions to access the enrollment or billing account, but receive an authentication error similar to one of the following:
Access to the resource is forbidden
We couldn’t authenticate with the credentials provided. Please try again.
These errors could be the result of having a user account in a different Microsoft Entra domain that has been added as a guest user.
For guest accounts: Use the following settings or options as you're prompted with the authentication dialog when connecting with the Cost Management Power BI connector:
Select Sign-in
Select the Use another account (bottom of the dialog)
Select Sign-in options (bottom of the dialog box)
Select Sign into an organization
For Domain name, provide the Fully Qualified Domain Name (FQDN) of the Microsoft Entra domain into which you've been added as a guest.
Then, for Pick an account select the user account that you’ve previously authenticated.
Related content
You can connect to many different data sources using Power BI Desktop. For more information, see the following articles:
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.