Forum Discussion
Extend the Azure consupmtion data.
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,
6 Replies
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 dataAfter 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
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"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