Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need help fixing this query

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.

2 Replies

  • Hello, Anonymous 

    recursion. So I would suggest to use @ 

    in
    if next <> null then
    @GetCostManagementData(next, combinedRows)
    else
    Table.FromRows(combinedRows, columns)

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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