Going further back for Azure Consumption in PowerBi

When you connect PowerBi via Microsoft Azure Consumption Insights (Beta) connector you will get the last month of consumption data and you may want to report further back. There is a way to do it where you can report back up to a maximum of 36 months.

If you follow my article https://blog.myvmx.com/2020/12/using-azure-tags-in-your-powerbi.html up to step "On the menu bar select "Transform data > Transform data" first.

On the menu bar select "Transform data > Transform data"

 

 

Under the "Home" ribbon locate and select "Advanced Editor"

You should see the query that has been applied which is currently based on this format

let
    Source = MicrosoftAzureConsumptionInsights.Tables(EnrollmentNumber, []),
    usagedetails = Source{[Key="usagedetails"]}[Data]
in
    usagedetails

We would need to change it to

let
    enrollmentNumber = "EnrollmentNumber",
    optionalParameters = [ numberOfMonth = 3, dataType = "DetailCharges" ],
     usagedetails = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters)    
in
    usagedetails

Under "optionaParameters" for the "numberOfMonth" you can change that number up to a maximum of 36 months. 

Once you have made the changes then click "Done" and wait for the query to load up. At the menu of the Power Query Editor select "Close & Apply" which would start to apply the query changes

You should now be able query consumption data back up to the number of months that you have inputted as part of the query.

 

Happy querying!!