Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Whenever I try to get usage details using Azure consumption insights, i get only current month and previous month.
From Power Bi portal, i'm able to get more than two month when use azure consumption content pack.
Is there any way to get more than two month usage details using Power Bi dekstop?
Objective: build a cost report to filter by tags (use transform json because it is not possible from Power Bi portal).
Solved! Go to Solution.
Hi,
I used the following query and it is working now:
let
enrollmentNumber = "100",
optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
Source = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters),
#"Parsed JSON" = Table.TransformColumns(Source,{{"Tags", Json.Document}}),
#"Expanded Tags" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags", {"Tag1", "Tag2", "Tag3", "Tag4"}, {"Tags.Tag1", "Tags.Tag2", "Tags.Tag3", "Tags.Tag4"})
in
#"Expanded Tags"
Thanks.
Hi @Anonymous,
You can try to modify below optional parameter 'number Of Month' to control date range if it works.
let enrollmentNumber = "100", optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ], data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters) in data
Reference links:
Connect to Azure Consumption Insights in Power BI Desktop (Beta)
Azure consumption API overview
Regards,
Xiaoxin Sheng
Hello Xiaoxin,
I used advanced query editor adter connecting to azure consumption insights and added option parameter as below:
let
Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentnumber, []),
usagedetails = Source{[Key="usagedetails"]}[Data],
optionalParameters = [ numberOfMonth = 4 ]
in
usagedetails
put it did not work
Hi @Anonymous,
'Optional Parameters' is a optional parameter of 'azure consumption insights' connector, it should be defined before invoke connector.
In your query formula,you haven't defined and used it in get data steps:
let enrollmentNumber = "100", optionalParameters = [ numberOfMonth = 4, dataType="DetailCharges" ], Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentnumber, optionalParameters), usagedetails = Source{[Key="usagedetails"]}[Data] in usagedetails
Regards,
Xiaoxin Sheng
Hi I have tried this in many different forms now and cannot get more than two months, it will only show current monht and one month previous. If u se your example by the letter i get this error message
Expression.Error: The import enrollmentnumber matches no exports. Did you miss a module reference?
@Grahamwm , there was a typo in the query. Power Query is case sensitive. I hope this helps.
let enrollmentNumber = "100", optionalParameters = [ numberOfMonth = 4, dataType="DetailCharges" ], Source = MicrosoftAzureConsumptionInsights.Tables(enrollmentNumber, optionalParameters), usagedetails = Source{[Key="usagedetails"]}[Data] in usagedetails
Nice, this is what I was looking for.
However, It doesn't work for me.
it doesn't have any errors but the Data only loads from October 1st til today (November 21th)
Does this still work for you?
One question, what's the second parameter, DetailCharges ?
Hi,
I used the following query and it is working now:
let
enrollmentNumber = "100",
optionalParameters = [ numberOfMonth = 6, dataType="DetailCharges" ],
Source = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters),
#"Parsed JSON" = Table.TransformColumns(Source,{{"Tags", Json.Document}}),
#"Expanded Tags" = Table.ExpandRecordColumn(#"Parsed JSON", "Tags", {"Tag1", "Tag2", "Tag3", "Tag4"}, {"Tags.Tag1", "Tags.Tag2", "Tags.Tag3", "Tags.Tag4"})
in
#"Expanded Tags"
Thanks.
I'm sorry I know this is a newbie question but where do you put this query in order to work?
Thanks!
Edit: found it! data source->blank query->advanced editor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |