cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Azure consumption insights | get more than two month usage details

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).

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

@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
Not applicable

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
Not applicable

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.

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors