Azure Cost Management - Splitting Out Tags

[RyanBrantley]

I've connected PowerBI to our Azure tenant's cost management feed.  I'm wanting to split the various tag categories into their own columns.

I've done a few Google searches, and the handful of blog posts I found all said the same thing: "Right Click the column > Transform > JSON".  I suppose something has changed recently in the data feed that the tags are no longer JSON wrapped (see screen cap below) - so obviously a JSON transform of the Azure tags fails.

Splitting the columns by comma seperator just makes a useless mess.... so I'm at an impasse at the moment.  Any advice would be greatly appreciated.

[bfernandez · Accepted solution]

Sorry about that, I was using it on some older code.

Find the correct code here:

application = 

VAR tag = "application"

VAR textLength =

 FIND( ",",

 Sheet3[Tags],

 FIND(tag,Sheet3[Tags])+LEN(tag)+3)-(FIND(tag,Sheet3[Tags])+LEN(tag)+3)-1

RETURN

 MID(

 Sheet3[Tags],

 FIND(tag,Sheet3[Tags])+LEN(tag)+3,

 textLength)

I have also added the Variable tag. Now, you simply have to get whatever tag you're looking for and replace "application" with the tag.

This should solve your issue! Please mark this post as the solution to help others! 😁

[Anonymous]

Hi I have tried, but not sucessfull. I want my one big tags to be splitted into multiple columns with thir data in rows without impacting cost numbers /other columns. 

Currently its in this look :- 

"Environment": "AAA","ExpenditureClassification": "BBBB","CostType": "CCCC","OrganizationUnit": "DDDDD","TraversalComponent": "EEEE","System": "NotAssignedYet",Product": "NotAssignedYet","ProductCategory": "NotAssignedYet","BusinessUnit": "NotAssignedYet","ProjectCode": "NotAssignedYet","Customer": "LLLLL","DepartmentOwner": "EEEE","ImplementationDepartment": "KKKKK","ImplementationTeam": "PPPPPP","DataClassification": "DDDDD","BusinessCategory": "HHHHH","LegacyInfrastructure": "YYYYY"

======

I am trying to get this look :-  where Environment, ExpenditureClassification etc are columns 

"Environment": "AAA",
"ExpenditureClassification": "BBBB",
"CostType": "CCCC",
"OrganizationUnit": "DDDDD",
"TraversalComponent": "EEEE",
"System": "NotAssignedYet",
"Product": "NotAssignedYet",
"ProductCategory": "NotAssignedYet",
"BusinessUnit": "NotAssignedYet",
"ProjectCode": "NotAssignedYet",
"Customer": "LLLLL",
"DepartmentOwner": "EEEE",
"ImplementationDepartment": "KKKKK",
"ImplementationTeam": "PPPPPP",
"DataClassification": "DDDDD",
"BusinessCategory": "HHHHH",
"LegacyInfrastructure": "YYYYY"

[Grahamwm]

I found a way that works for me and may work for you. You can create a new column in your table and add the following code that is below. The code will be used to set the name of your new colum eg CostCentre and then search the tags table for anything that matches your search criteria around costcentre and add those values to the new table. You can use this over and over by changing out CostCenre in the search section for any tag key you are searching for. One new column for each tag key.

TagCostCentre =

VAR FindCostCentre =

IF (

    SEARCH ( """CostCentre""", [Tags],, 0 ) > 0,

    SEARCH ( """CostCentre""", [Tags],, 0 ),

    IF (

        SEARCH ("""CostCenter""", [Tags],, 0) > 0,

        SEARCH ("""CostCenter""", [Tags],, 0),

                0

    )

)

VAR FindColon =

IFERROR ( SEARCH (":", [tags], FindCostCentre, 0 ), BLANK () )

VAR FindOpeningQuote =

IFERROR ( SEARCH ("""", [tags], FindColon, 0 ), BLANK () )

VAR FindClosingQuote =

IFERROR ( SEARCH ( """", [Tags], FindOpeningQuote + 1, 0 ), BLANK () )

VAR temp =

VALUE ( FindClosingQuote - FindOpeningQuote )

RETURN

IF (

FindCostCentre > 0,

IFERROR ( MID ( [Tags], VALUE ( FindOpeningQuote + 1 ), temp - 1) , BLANK () ))

[Anonymous · Accepted solution]

Hey all,

Not sure if anyone's still having trouble with this, but thought I'd share the method I used to work with tags.

The Tags field in Azure Cost Management is almost JSON format, but is missing the opening and closing curly braces.

By adding these back, I was then able to use Transform -> JSON without error.

Example:

    #"Added Custom" = Table.AddColumn(usagedetails, "Tags JSON", each Text.Combine({"{ ", [Tags], " }"})),
    #"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"Tags JSON", Json.Document}}),

Hope that helps someone! 

[adeelaziz]

I used this method and it worked beautifully for me.

[ATrattLeit]

I am not an expert in Bi but I have added the following lines as documented:

let
Source = Csv.Document(File.Contents("C:\Users\xxxxxx\Downloads\CostAnalysisDemo.csv"),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ResourceGroup", type text}, {"ResourceGroupId", type text}, {"SubscriptionName", type text}, {"SubscriptionId", type text}, {"Resource", type text}, {"ResourceId", type text}, {"ResourceType", type text}, {"ResourceLocation", type text}, {"Tags", type text}, {"Cost", Int64.Type}, {"CostUSD", type number}, {"Currency", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Tags JSON", each Text.Combine({"{ ", [Tags], " }"})),
#"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"Tags JSON", Json.Document}})
in
#"Parsed JSON"

Unfortunatly I get the following error:

DataFormat.Error: We found extra characters at the end of the JSON input.
Details:
Value=[
Position=2

Do you have any idea what I am missing?

[adeelaziz]

I'm no expert either however looking through my query I noticed that I have "Parsed JSON" before "Changed Typed" whereas you have it the otherway.  My query looks as follows, I've truncated some of it and also removed any identifiable info, however this gives you an idea of what I'm talking about.

let

    Source = AzureCostManagement.Tables("Enrollment Number", "xxxxxxxxxxxx", 12, [startDate=null, endDate=null]),

    usagedetails = Source{[Key="usagedetails"]}[Data],

    #"Added Custom" = Table.AddColumn(usagedetails, "JSONTags", each Text.Combine({"{ ", [Tags], " }"})),

    #"Parsed JSON" = Table.TransformColumns(#"Added Custom",{{"JSONTags", Json.Document}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Parsed JSON",{{"BillingAccountId", Int64.Type}, {"BillingAccountName", type text}, {"BillingProfileId", Int64.Type},"...etc}),

    #"Expanded JSONTags" = Table.ExpandRecordColumn(#"Changed Type", "JSONTags", {"aaa", "bbb", "ccc"}, {"JSONTags.aaa", "JSONTags.bbb", "JSONTags.ccc"})

in

    #"Expanded JSONTags"

[bfernandez]

I'm curious as to why the splitting of columns doesn't provide the solution you are looking for.

Here is what I did:

1. Split Column by Delimiter - ","
2. Renamed the columns: Application, Environment, Budget, Client
3. Extracted Text After Delimiter - ":"

This was my final result:

M Query:

let
Source = [Source],
#"Changed Type" = Table.TransformColumnTypes([Source],{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tags", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Tags", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Tags.1", "Tags.2", "Tags.3", "Tags.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tags.1", type text}, {"Tags.2", type text}, {"Tags.3", type text}, {"Tags.4", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Tags.1", "Application"}, {"Tags.2", "DataBricks Environment"}, {"Tags.3", "Budget"}, {"Tags.4", "Client"}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Application", each Text.AfterDelimiter(_, ":"), type text}, {"DataBricks Environment", each Text.AfterDelimiter(_, ":"), type text}, {"Budget", each Text.AfterDelimiter(_, ":"), type text}, {"Client", each Text.AfterDelimiter(_, ":"), type text}})
in
#"Extracted Text After Delimiter"

[Grahamwm]

This would be a great option you have shown but it doesnt work when tags may be in different orders or some rows don't have the same tags or if Tag names have different spellings.

Is there a way to use this when you have a wide range of tags and up to 14 different tag names but with them in different orders and not all the same?

[RyanBrantley]

Ah, ok.  Thanks - just realized that the screen cap makes it look like the tags are always structured in the same order.

The tags come in random order.  So I might see

• "client":"123","tag2:"foo"
• "tag2:foo","client":"abc"

And we usually have 5-10 tags, all randomly ordered, against 350k+ rows.

[bfernandez]

It's hard for me to undertsnad how the data does not come directly from Azure in the JSON format you are expecting. Be sure you are using the correct versions and are applying the correct Power Query steps.

If this does not solve your issue, you can apply the following workaround in DAX:

Hostname = 
VAR textLength =
    FIND (
        ",",
        Table1[Tags],
        FIND ( "Hostname", Table1[Tags] ) + LEN ( "Hostname" )
            + 3
    )
        - (
            FIND ( "Hostname", Table1[Tags] ) + LEN ( "Hostname" )
                + 3
        )
        - 1
RETURN
    MID (
        Table1[Tags],
        FIND ( "Hostname", Table1[Tags] ) + LEN ( "Hostname" )
            + 3,
        textLength
    )

It's not the neatest solution but it will get the job done. Of course, you would need to replace "Hostname" with whichever tags you are looking for.

Hopefully this helps!

[RyanBrantley]

Yea, not sure what is going on with them not being JSON, but I double checked that I'm using the Azure Cost Management connecter.

I think your idea would definitely work, because I'm only trying to extract 2 or 3 tags.  

I modified it for my tables, colums, and tags, but DAX is giving me an error return with the message "The search Text provided to function 'Find' could not be found in the given text.'

[bfernandez · Accepted solution]

Sorry about that, I was using it on some older code.

Find the correct code here:

application = 

VAR tag = "application"

VAR textLength =

 FIND( ",",

 Sheet3[Tags],

 FIND(tag,Sheet3[Tags])+LEN(tag)+3)-(FIND(tag,Sheet3[Tags])+LEN(tag)+3)-1

RETURN

 MID(

 Sheet3[Tags],

 FIND(tag,Sheet3[Tags])+LEN(tag)+3,

 textLength)

I have also added the Variable tag. Now, you simply have to get whatever tag you're looking for and replace "application" with the tag.

This should solve your issue! Please mark this post as the solution to help others! 😁

[Anonymous]

Hello,

been reading the whole post and tried it out. I am not very familiar with powerbi code but as I changed it to suit my setup, it came back with an error that was not mentioned in this thread:

Code:

dept=
VAR tag = "dept"
VAR textLength =
FIND( ",",
usagedetails[Tags],
FIND(tag,usagedetails[Tags])+LEN(tag)+3)-(FIND(tag,usagedetails[Tags])+LEN(tag)+3)-1

RETURN
MID(
usagedetails[Tags],
FIND(tag,usagedetails[Tags])+LEN(tag)+3,
textLength)

Error: 

A single value for column 'Tags' in table 'Usagedetails' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

It either does not seem to be performing the "find" in the column or it can't find the column name. 

It does not look like anyone else has encountered this issue?

[RyanBrantley]

Thanks!