Power BI desktop and getting multiple months in one row from the Azure billing API

[Salvolin]

Hi all,

I am new to power BI. U used to work with Business Objects 6 back in the old days so wow nice new features...

I want to load multiple months from the Azure billing API (https://ea.azure.com/res/[enrollment number]) in one table in Power BI desktop. I have been trying all night, but no luck. I seached the internet and this forum, but did not find an answer.

If I connect to the billing API from Power BI app (service) I can load up to 36 months in one datasource. If I do the same from Power BI desktop I can only load each month individually, which is not what I want.

The detailed connection for januari 2016 is:

let
    Source = AzureEnterprise.Tables("https://ea.azure.com/rest/[enrollment number]"),
    Detail = Source{[Key="Detail"]}[Data],
    #"rest/[enrollment number]/usage-report?month=2016-01&type=detail&fmt=Csv" = Detail{[Link="rest/[enrollment number]/usage-report?month=2016-01&type=detail&fmt=Csv"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"rest/[enrollment number]/usage-report?month=2016-01&type=detail&fmt=Csv",{{"AccountOwnerId", type text}, {"Account Name", type text}, {"ServiceAdministratorId", type text}, {"SubscriptionId", Int64.Type}, {"SubscriptionGuid", type text}, {"Subscription Name", type text}, {"Date", type text}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Year", Int64.Type}, {"Product", type text}, {"Meter ID", type text}, {"Meter Category", type text}, {"Meter Sub-Category", type text}, {"Meter Region", type text}, {"Meter Name", type text}, {"Consumed Quantity", Int64.Type}, {"ResourceRate", Int64.Type}, {"ExtendedCost", type number}, {"Resource Location", type text}, {"Consumed Service", type text}, {"Instance ID", type text}, {"ServiceInfo1", type text}, {"ServiceInfo2", type text}, {"AdditionalInfo", type text}, {"Tags", type text}, {"Store Service Identifier", type text}, {"Department Name", type text}, {"Cost Center", type text}, {"Unit Of Measure", type text}, {"Resource Group", type text}, {"", type text}})
in
    #"Changed Type"

Is there any way for me to edit this detailed connection so that it loads all months instead of only one.

(I don't want to load eacht month individually and merge them, because than I need to add each new month manually)

Regards,

 Bert

and thanks in advance for any help.

[Salvolin]

Any experience on this?

I am now merging these tables one by one, but that way I will need to adjust the loading process once each month.

Besides. Power BI desktop is doing a wrong conversion of the decimals in Consumed Quantity and Extended Cost (probably due to regional settings) so I now need to fix that in each table....

[Anonymous]

While the team integrates the possibility to query for all the months in one call, you can try to reuse this script (M script in PowerQuery): 

http://www.edmondek.com/Azure-Usage-Analytics-Report-Jan-2016-Update/

It looks for all the available months, and then loops them to bring the data of each month and merge them all. 

Fran

[gioorl]

Appending tables is simple but it would also be useful to query relative period of times rather than specific months and receive the data ina single table

i.e. load data source for the past 12 months as opposed to month=2016-08

This could be most usefull when refreshing data because at any time it would allow to load the past 12 months of data and not always 2016-08

I'm using Power BI desktop to load data from Azure Enterprise source and the idea is to edit it on Power BI desktop for then publishing it on Power BI Pro.

If I could reference a relative period of time instead of a specific month, I could publish it once and even i a year time I will see the last 12 months of data.

Referencing specific months will likely push me to update and publish the report every month.

Kind regards,

Gio

[gioorl · Accepted solution]

Working with Rupert Benbrook from Microsoft explained how to easily get all data from Azure Enterprise into a single table without appending multiple tables and this method should work for any other data source.
This method also addresses the need to get all latest data and not specific months.

When getting data from the data source
1. Chose 1 month under “Detail” and then click on "Edit" (not Load)
2. On the right hand side of the screen, under steps, select "Source"
3. Click on the entry in the table under "Detail" "Data" and then click on "Continue"
4. Select the Data column and then click on "Transform" -> "Expand"

This shows all the data which can be retrieved from the data source.

[Anonymous]

Screenshots by any chance? 

[Salvolin]

I was "away" from this subject for a few months. Just wanted to say "thanks this works as a charm".

Regards,

 Salvolin

[Anonymous]

While the team integrates the solution out of the box, maybe you can reuse this M script to look for all the available months (or its links) and the merge the contents. See this Excel PowerQuery script to get the idea.

http://www.edmondek.com/Azure-Usage-Analytics-Report-Jan-2016-Update/

Fran