Connect to Azure Cost Management data in Power BI Desktop - Power BI (2023)

You can use the Azure Cost Management connector for Power BI Desktop to create powerful, custom visualizations and reports that help you better understand your Azure spend. The Azure Cost Management Connector currently supports customers with a directMicrosoft Customer Agreementor aEnterprise Agreement (EA).

The Azure Cost Management connector does not support usage-based Microsoft Customer Agreements or indirect Microsoft Customer Agreements. Microsoft partner agreements are also not supported. If you have an unsupported agreement, you can use exports to save the cost data to a share and then connect to it using Power BI. For more information, seeTutorial - Create and manage exported data from Azure Cost Management.

The Azure Cost Management connector uses OAuth 2.0 to authenticate with Azure and identify users who will use the connector. Tokens generated in this way are valid for a certain period of time. Power BI keeps the token for the next login. OAuth 2.0 is a standard for the process that runs behind the scenes to ensure the secure handling of these permissions. To connect, you must use onecompany administratorHave or have an Enterprise Agreement accountappropriate permissionsat the billing account or billing profile level for Microsoft Customer Agreements.

Connect using Azure Cost Management

To use theAzure Cost Management-ConnectorIn Power BI Desktop, do the following:

1. ImHeimband, selectget data.

2. ChooseAzurblaufrom the list of data categories.

3. ChooseAzure cost management.

   

   (Video) Azure Cost Management - Power Bi

4. In the dialog that appears for theChoose areafall down, useEnter range manuallyProMicrosoft Customer Agreements, or useenrollment numberProEnterprise Agreements (EA).

Connect to a Microsoft Customer Agreement account

This section describes the steps required to connect to a Microsoft Customer Agreement account.

Connect to a billing account

To connect to a billing account, you need to get yoursBilling Account IDfrom the Azure portal:

1. ImAzure-Portal, navigate toCost Management + Billing.

2. Select your billing profile.

3. Undersettingsselect in the menucharacteristicsin the sidebar.

4. Underbilling profile, copy thatI WOULD.

5. ForChoose area, SelectEnter range manuallyand enter the connection string as shown in the example below and replace{billingAccountId}with the data copied from the previous steps./providers/Microsoft.Billing/billingAccounts/{billingAccountId}

   Alternativ zChoose area, Selectenrollment numberand enter the billing account ID string as copied from the previous steps.

6. Enter the number of months and selectOK.

   

   Alternatively, you can set if you want to download less than a month of datanumber of monthsto zero, and then use to specify a date rangestart dateandFinal termValues ​​equal to less than 31 days.

7. If prompted, sign in with your Azure user account and password. You must have access to the billing account section to successfully access the billing data.

   (Video) Azure Cost Reporting Template in Power BI

Connect to a billing profile

To connectbilling profile, you need to retrieve yourBilling Profile IDandBilling Account IDfrom the Azure portal:

1. ImAzure-Portal, navigate toCost Management + Billing.

2. Select your billing profile.

3. Undersettingsselect in the menucharacteristicsin the sidebar.

4. Underbilling profile, copy thatI WOULD.

5. UnderBilling & Account, copy thatI WOULD.

   

6. ForChoose area, SelectEnter range manuallyand enter the connection string as shown in the example below and replace{billingAccountId}and{billingProfileId}with the data copied from the previous steps.

   /providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}

7. Enter the number of months and selectOK.

8. If 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 to an Enterprise Agreement (EA) account, you can get your registration ID from the Azure portal:

1. ImAzure-Portal, navigate toCost Management + Billing.

   (Video) Power BI Integration With Azure | Power BI Azure Tutorial For Beginners | K21Academy

2. Select your billing account.

3. On theoverviewmenu, copy theBilling Account ID.

4. ForChoose area, Selectenrollment numberand paste the billing account ID from the previous step.

5. Enter the number of months, then selectOK.

   

6. If 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 have successfully authenticated, aNavigatorA window appears with the following available data tables:

You can select a table to display a preview dialog. You can select one or more tables by checking the boxes next to their name and then selectingburden.

If you chooseburden, the data is loaded into Power BI Desktop.

When the data you selected is loaded, the data tables and fields appear inFelderFeld.

Considerations and Limitations

The following considerations and limitations apply to the Azure Cost Management data connector:

• Power BI doesn't support data row requests that exceed one million rows. Instead you can try to use the export function described inCreate and manage exported data in Azure Cost Management.

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

• The Azure Cost Management data connector does not work with Office 365 GCC customer accounts.

• Data update:Cost and usage data is typically updated within 8-24 hours and is available in the Azure portal and supporting APIs. Therefore, we recommend limiting scheduled Power BI refreshes to once or twice a day.

• Data source reuse:If you have multiple reports that retrieve the same data and do not require additional report-specific data transformations, consider reusing the same data source, which would reduce the time required to retrieve usage details.

  For more information on data source reuse, see:

  • Introduction to cross-workspace datasets
  • Create reports based on datasets from different workspaces

You may receive one400 bad requestof theRI usage detailswhen you try to update the data when you have selected a date parameter that is greater than three months. Follow the steps below to fix the error:

1. In Power BI, select DesktopHome > Transform Data.

2. In the Power Query Editor, select theRI usage detailsrecord and selectAdvanced editor.

3. Update the Power Query code as shown in the following paragraphs, which will split the calls into three-month chunks. Make sure to write down and keep your registration number or billing account/billing profile ID.

   ForEAUse the following code update:

   let enrollmentNumber = „<<Enrollment Number>>“, optionalParameters1 = [startBillingDataWindow = „-9“, endBillingDataWindow = „-6“], source1 = AzureCostManagement.Tables(“Enrollment Number“, enrollmentNumber, 5, optionalParameters1), riusagedetails1 = source1{[Key="riusagedetails"]}[Data], optionalParameters2 = [startBillingDataWindow = "-6", endBillingDataWindow = "-3"], source2 = AzureCostManagement.Tables("Enrollment Number", enrollmentNumber, 5, optionalParameters2), riusagedetails2 = source2{[Key="riusagedetails"]}[Daten], riusagedetails = Table.Combine({riusagedetails1, riusagedetails2})in riusagedetails

   ForMicrosoft Customer AgreementsUse the following fix:

   let billingProfileId = "<<billing profile id>>", optionalParameters1 = [startBillingDataWindow = "-9", endBillingDataWindow = "-6"], source1 = AzureCostManagement.Tables("billing profile id", billingProfileId, 5, optionalParameters1), riusagedetails1 = source1{[Key="riusagedetails"]}[Data], optionalParameters2 = [startBillingDataWindow = "-6", endBillingDataWindow = "-3"], source2 = AzureCostManagement.Tables("BillingProfileID", billingProfileId, 5, optionalParameters2), riusagedetails2 = source2{[Key="riusagedetails"]}[Data], riusagedetails = Table.Combine({riusagedetails1, riusagedetails2})in riusagedetails

4. After updating the code with the appropriate update from the previous step, selectDidand then selectClose & apply.

Sometimes tags in the usage details don't work or the tags column can't be converted to JSON. This issue stems from the current UCDD API that returns the tags column by trimming the start and end brackets, resulting in Power BI not being able to transform the column as it is returned as a string. Follow the steps below to mitigate this situation.

1. Navigate toQuery Editor.
2. ChooseUsage DetailsTisch.
3. In the right pane thecharacteristicsarea shows theApplied Steps. You must add a custom column following the steps belowNavigationStep.
4. Select from the menuAdd Column>Add custom column
5. Name the column, for example you could name the columnTagsInJsonor whatever you prefer, and then enter the following text into the query:

   ```= "{"& [Tags] & "}"

6. Performing the previous steps creates a new column ofkeywordsim JSON-Format
7. You can now transfer and expand the column as needed.

Authentication issues with Azure Active Directory guest accounts:You may have appropriate permissions to access the registration or billing account, but receive an authentication error similar to one of the following:

• Access to the resource is prohibited
• We could not authenticate with the provided credentials. Please try again.

These errors can be caused by having a user account in another Azure Active Directory domain that was added as a guest user.

For guest accounts: Use the following settings or options when promptedauthentication dialogwhen connecting to the Cost Management Power BI connector:

1. Chooselog in
2. ChooseUse a different account(below in dialogue)
3. ChooseLogin Options(bottom of dialog)
4. ChooseJoin an organization
5. Fordomain name, provide the fully qualified domain name (FQDN) of the Azure Active Directory domain to which you were added as a guest.
6. Then forChoose an accountSelect the user account that you previously authenticated.

Next Steps

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

• 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