Skip to main content

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!!

Comments

Popular posts from this blog

Rolling back a version of ESXi

There is an option in VMware where after you have performed an major upgrade of ESXi you can roll back to your previous version. The benefit of this is that you would not need to reinstall your ESXi and its configuration if you had issues with the new software. I had to do this on one occassion in my lab where I upgraded from 6.5 to 6.7 and my VMs would not run because the CPU was not supported in 6.7. Please remember if you are using ISO method to upgrade ESXi please ensure you select "Upgrade ESXi, preserve VMFS datastore". Selecting "Install ESXi, preserve VMFS datastore" does not mean preserving datastore means retaining ESXi as it will still do a clean install of ESXi. This method does not work for vSphere 7.0 as there are changes to the partitions on the boot device. Below are the steps to roll back to a previous version which is quite straight forward. As always perform an backup of your host configuration before you upgrade or rollback ( KB2042141 ). I have

Configuring ESXi 6 host to send logs to Syslog Server

In my previous post I talked about configuring VMware Syslog server for Windows which is installed and enabled by default on installation of vCenter 6 for Windows. I will now describe the basic configuration that is required on an ESXi 6 host to be able to send logs out to a syslog server using my vCenter as the example. 1) Navigate to your ESXi host within vCenter. Go to "Manage" tab and select "Settings" followed by "Advanced System Settings". Look for the settings "Syslog.global.loghost" and highlight this settings. Click the pencil icon to edit the configuration for this setting. 2) You can now add the host name or ip address of your syslog server/s. You can enter just hostname or IP address, use udp://hostname:514 or ssl://hostname:1514 to be more specific on the port and protocol to be used. If you have multiple hosts then you use the comma (,) to separate each server i.e. udp://192.168.0.1:514,udp://192.168.0.2:514 3)We n

Access vSphere Web client blank page in Chrome

Today I came across in my LAB where when I tried to access my vSphere Web Client in chrome and I just get a blank web page with no error message. At first I thought my vCenter Web client service was having issues and did a couple of restart. I then decided to use firefox and internet explorer to make sure it was not a browser problem and it appeared to work in the other browsers. Next I decided to clear all the cache in Chrome and it still didn't work at all !!!. Last resort for me was to try and reset the browser settings to their original defaults which worked. So to reset the browser settings to default you need to: Open chrome > Open Settings > Show Advanced Settings > Reset browser Settings > Reset Close Chrome and open again and if you was using self-signed certificates click "Proceed anyway" Just a word of warning of reset browser settings where you will lose all your settings. There is an article on VMware website but it is for issues with acc