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! 

bfernandez's avatar
bfernandez
Resolver II
6 years ago

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"
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?