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! 

adeelaziz's avatar
adeelaziz
Frequent Visitor
3 years ago

I used this method and it worked beautifully for me.

ATrattLeit's avatar
ATrattLeit
New Member
2 years ago

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's avatar
    adeelaziz
    Frequent Visitor
    2 years ago
    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"