PowerBi Azure Cost Management exposing Tag values to be used

I wrote an article about exposing tags to be used in PowerBi using the connector “Microsoft Azure Consumption Insights (Beta)” (Using Azure tags in your powerbi reporting). If you recall there is a warning message when you use the connector which states that it is still under development. It seems like there is no development for this connector as it has been in “beta” for a long time. I noticed that there is a new connector called “Azure Cost Management” which I will walk through which seems easier to configure. I googled around and decided to combine all my findings in to one document.

First as we are connecting via our Enterprise agreement, we need to ensure at minimum that we have “Enterprise Administrator (read only)” permissions within the EA portal. Once we have that we should be able login to https://ea.azure.com and see our enrollment number on the top left of the portal which we will need to note down.

When you start up PowerBi desktop if you get the start-up screen then select “Get data” otherwise on the menu bar select “Get Data > More”. A new window will open up select “Azure” then select “Azure Cost Management” and click “Connect”.

Under “Choose Scope, select “Enrollment Number”, input your enrollment number that we have gathered from earlier step, followed by the number of months we would like to report costing for then click “OK”.

If you haven’t signed in then click on “Sign in”.

If your account is not listed the select “Use another account” and follow the instructions. If your account is listed then select that particular account.

You will be taken back to the sign in screen for Azure Cost Management connector. You should see “You are currently signed in” and click “Connect”

Wait for it to load the tables and we will just select "Usage details" table for now and click "Load"

Once the table has loaded go to the menu bar and select “Transform data > Transform data”

Power query editor should load up. Head to the menu bar and select "Add Column" tab the select "custom column"

A new window will appear to create the new column. Give the new column a name which I have used "CustomTags" (You can name the column whatever you like). In the “Custom column formula” box after the "=" sign enter the following Text.Combine({"{ ", [Tags], " }"}) and click "OK". Please notice the "spaces" and lower/upper cases for the letters as they are important. This formula basically goes to the Tags column and for each row adds the curly brackets to the start and the end of the data so that it is in a JSON format.

The new column will appear at the far right of the table click anywhere on the header and select Transform > JSON

Once that is completed click on the expand icon on the column and wait for the values to load up

Once loaded select the tag names that you wish to expand in to columns to be used for reporting

You should now see the columns you have selected in your table. By default the column name format is <%CustomColumnName%.%TagName%>

On the home menu bar click on “Close and Apply” so that this query is saved. You should now be able to select your new “tag” columns to be used in your reporting.

Going forward I think Microsoft will be doing more development with this connector so it will be best to start using this or migrating to using this.