cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mshehata Frequent Visitor
Frequent Visitor

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

Accepted Solutions
mshehata Frequent Visitor
Frequent Visitor

Re: Azure consumption insights | get more than two month usage details

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.

7 REPLIES 7
Community Support Team
Community Support Team

Re: Azure consumption insights | get more than two month usage details

Hi @mshehata,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
mshehata Frequent Visitor
Frequent Visitor

Re: Azure consumption insights | get more than two month usage details

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

Community Support Team
Community Support Team

Re: Azure consumption insights | get more than two month usage details

Hi @mshehata,

 

'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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mshehata Frequent Visitor
Frequent Visitor

Re: Azure consumption insights | get more than two month usage details

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 Frequent Visitor
Frequent Visitor

Re: Azure consumption insights | get more than two month usage details

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

Grahamwm Occasional Visitor
Occasional Visitor

Re: Azure consumption insights | get more than two month usage details

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 Frequent Visitor
Frequent Visitor

Re: Azure consumption insights | get more than two month usage details

@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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 191 members 2,541 guests
Please welcome our newest community members: