Extend the Azure consupmtion data.

[Anonymous]

Hi,

I am pretty new to Power BI and need some help on extending the time that Power BI keeps the data it collects, specifically from the Azure consuption API. I have read the article published below, but am still not sure where/how to add the number of months. If I create a new query as they sugest, I do not see where to add it. I can use advance edit to see the current query, but very unsure on how to add the numebr of months attribute.

Currently I ony see the last 2 months.

Amy advice appreciated.

https://docs.microsoft.com/en-us/power-bi/desktop-connect-azure-consumption-insights

Thanks,

[v-shex-msft]

Hi @Anonymous,

Actually, official document has shared the power query formula to modify load data with filters parameters.(bold part)

let    
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
    data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters)   
in     
    data

After finish get data steps, please enter to query editor and modify your query to as above, and modify bold part number to control have many months to load.

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

Hi! Just to be clear, what is the default number of months of data that Power BI desktop pulls if I did not configure this? 

[Anonymous]

Hi,

My query looks very different, maybe I am looking in the wrong place ?

Mine looks like

let
Source = MicrosoftAzureConsumptionInsights.Tables(100, []),
usagedetails = Source{[Key="usagedetails"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(usagedetails,{{"AccountOwnerId", type text}, {"Account Name", type text}, {"ServiceAdministratorId", type text}, {"SubscriptionId", Int64.Type}, {"SubscriptionGuid", type text}, {"Subscription Name", type text}, {"Month", type date}, {"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}, {"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}, {"Location", type text}, {"InstanceId", type text}, {"AccountId", Int64.Type}, {"DepartmentId", Int64.Type}, {"ProductId", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each if Date.IsInCurrentWeek([Date])
then "This Week"
else Text.From(Date.EndOfWeek([Date])))
in
#"Added Custom"

[v-shex-msft]

HI @Anonymous,

You can try to use below formula, I try to merge my formula with yours.

let    
    enrollmentNumber = "100",
    optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges"],
    Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentNumber , optionalParameters ), 
    usagedetails = Source{[Key="usagedetails"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(usagedetails,{{"AccountOwnerId", type text}, {"Account Name", type text}, {"ServiceAdministratorId", type text}, {"SubscriptionId", Int64.Type}, {"SubscriptionGuid", type text}, {"Subscription Name", type text}, {"Month", type date}, {"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}, {"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}, {"Location", type text}, {"InstanceId", type text}, {"AccountId", Int64.Type}, {"DepartmentId", Int64.Type}, {"ProductId", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each if Date.IsInCurrentWeek([Date]) then "This Week" else Text.From(Date.EndOfWeek([Date])))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[Anonymous]

HI,

The solution above doesn't seem to have worked. I still can only see the last 2 months of data.

Cheers

[Anonymous]

Thanks, I have pasted it in to Power BI and it didn't break anything, I will have to wait till next month to see if it keeps the extra data, but I do believe it will. i will of course come back and comment here to close it of.