Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
My query is:
let
// Define GetCostManagementData function
GetCostManagementData = (url as text, rows as list) as table =>
let
QueryHeaders = [#"Content-Type" = "application/json", #"Authorization" = "Bearer " & AccessToken],
response = Json.Document(Web.Contents(url, [Headers=QueryHeaders, Content=Text.ToBinary(QueryBody)])),
newRows = response[properties][rows],
next = response[properties][nextLink],
combinedRows = rows & newRows
in
if next <> null then
GetCostManagementData(next, combinedRows)
else
Table.FromRows(combinedRows, columns),
// Define variables
accesstoken = AccessToken,
queryScope = "/providers/Microsoft.Management/managementGroups/112",
baseUrl = "https://management.azure.com/"& queryScope &"/providers/Microsoft.CostManagement/",
apiVersion = "2022-10-01",
QueryUrl = baseUrl & "query?api-version=" & apiVersion,
QueryBody = "
{
""type"": ""AmortizedCost"",
""timeframe"": ""LastMonth"",
""dataSet"":
{
""granularity"": ""Monthly"",
""aggregation"":
{
""totalCost"":
{""name"": ""PreTaxCost"",""function"": ""Sum""}
},
""grouping"":
[
{""type"": ""Dimension"",""name"": ""SubscriptionName""},
{""type"": ""Dimension"",""name"": ""ResourceGroupName""},
{""type"": ""Dimension"",""name"": ""ServiceName""},
{""type"": ""Dimension"",""name"": ""ServiceTier""},
{""type"": ""Dimension"",""name"": ""Meter""},
{""type"": ""Dimension"",""name"": ""ResourceId""},
{""type"": ""Dimension"",""name"": ""ResourceLocation""}
],
""include"": [""Tags""]}}",
QueryHeaders = [#"Content-Type" = "application/json", #"Authorization" = "Bearer " & AccessToken],
// Get initial data
initialResponse = try Web.Contents(QueryUrl, [Headers=QueryHeaders, Content=Text.ToBinary(QueryBody)]) otherwise null,
initialJsonData = try Json.Document(initialResponse) otherwise null,
rows = try initialJsonData[properties][rows] otherwise null,
nextLink = try initialJsonData[properties][nextLink] otherwise null,
columns = try List.Transform(initialJsonData[properties][columns], each [name]) otherwise null,
// Call helper function to retrieve all data
data = if rows <> null and nextLink <> null and columns <> null then
GetCostManagementData(nextLink, rows)
else if rows <> null and columns <> null then
Table.FromRows(rows, columns)
else
error "Failed to retrieve data from Azure Cost Management API"
in
data
Here I am able to get the data, but then I get the error: Expression.Error: The import GetCostManagementData matches no exports. Did you miss a module reference? and that points to Expression.Error: The name 'GetCostManagementData' wasn't recognized. Make sure it's spelled correctly.
Not sure what to do, I can see the data, everything is there but somewhere it is getting failed. Can someone help?
Thanks in advance.
Hello, @Anonymous
recursion. So I would suggest to use @
in
if next <> null then
@GetCostManagementData(next, combinedRows)
else
Table.FromRows(combinedRows, columns)
Now, if I add @ at the begining as you mentioned, then it gives a different error: DataFormat.Error: Invalid URI: The hostname could not be parsed.
Looks like it can't read the nextLink or something wrong with the condition