Forum Discussion
Extend the Azure consupmtion data.
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
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
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.
HI,
The solution above doesn't seem to have worked. I still can only see the last 2 months of data.
Cheers