Forum Discussion

RyanBrantley's avatar
RyanBrantley
Frequent Visitor
6 years ago
Solved

Azure Cost Management - Splitting Out Tags

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 blo...
Need Help
  • bfernandez's avatar
    bfernandez
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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! 

RyanBrantley's avatar
RyanBrantley
Frequent Visitor
6 years ago

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's avatar
bfernandez
Resolver II
6 years ago

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! 😁

  • RyanBrantley's avatar
    RyanBrantley
    Frequent Visitor
    6 years ago

    Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?