Azure consumption insights | get more than two month usage details

[Anonymous]

Hello,

Whenever I try to get usage details using Azure consumption insights, i get only current month and previous month.

From Power Bi portal, i'm able to get more than two month when use azure consumption content pack.

Is there any way to get more than two month usage details using Power Bi dekstop?

Objective: build a cost report to filter by tags (use transform json because it is not possible from Power Bi portal).

[v-shex-msft]

Hi @Anonymous,

You can try to modify below optional parameter 'number Of Month' to control date range if it works.

let    
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
    data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters)   
in     
    data

Reference links:

Connect to Azure Consumption Insights in Power BI Desktop (Beta)

Azure consumption API overview

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin,

I used advanced query editor adter connecting to azure consumption insights and added option parameter as below:

let
    Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentnumber, []),
    usagedetails = Source{[Key="usagedetails"]}[Data],
    optionalParameters = [ numberOfMonth = 4 ]
in
    usagedetails

put it did not work

[v-shex-msft]

Hi @Anonymous,

'Optional Parameters' is a optional parameter of 'azure consumption insights' connector, it should be defined before invoke connector. 

In your query formula,you haven't defined and used it in get data steps:

let
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 4, dataType="DetailCharges" ],
    Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentnumber, optionalParameters),
    usagedetails = Source{[Key="usagedetails"]}[Data]
in
    usagedetails

Regards,

Xiaoxin Sheng

[Grahamwm]

Hi I have tried this in many different forms now and cannot get more than two months, it will only show current monht and one month previous. If u se your example by the letter i get this error message

Expression.Error: The import enrollmentnumber matches no exports. Did you miss a module reference?

[danielntamasi]

@Grahamwm , there was a typo in the query. Power Query is case sensitive. I hope this helps.

let
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 4, dataType="DetailCharges" ],
    Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentNumber, optionalParameters),
    usagedetails = Source{[Key="usagedetails"]}[Data]
in
    usagedetails

[Anonymous]

Nice, this is what I was looking for.

However, It doesn't work for me.

it doesn't have any errors but the Data only loads from October 1st til today (November 21th)

Does this still work for you?

One question, what's the second parameter, DetailCharges ?

[Anonymous · Accepted solution]

Hi,

I used the following query and it is working now:

let   
          enrollmentNumber = "100",
          optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
          Source = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters),
    #"Parsed JSON" = Table.TransformColumns(Source,{{"Tags", Json.Document}}),
    #"Expanded Tags" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags", {"Tag1", "Tag2", "Tag3", "Tag4"}, {"Tags.Tag1", "Tags.Tag2", "Tags.Tag3", "Tags.Tag4"})  
      in    
          #"Expanded Tags"

Thanks.

[ganagy]

I'm sorry I know this is a newbie question but where do you put this query in order to work?

Thanks!

Edit: found it! data source->blank query->advanced editor