Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
To do this, I have a this function code for use to authenticate:
let AzureToken = () => let body = [ grant_type = "client_credentials", client_id = {{AzureClientId}}{0}{0}, // literal value client_secret = {{AzureClientSecret}}{0}{0}, // literal value resource = "https://management.azure.com/" ], oathUrl = Text.Combine({"https://login.microsoftonline.com/", {{AzureTenantId}}{0}{0}, "/oauth2/token"}), httpBody = Uri.BuildQueryString(body), token = Json.Document( Web.Contents(oathUrl, [ Headers=[ ContentType="application/x-www-form-urlencoded", Accept="application/vnd.api+json" ],
Content = Text.ToBinary(httpBody) ])) in token[access_token] in
AzureToken
This function can be invoked manually to request a token and it always works just fine using anonymous authentication.
However, I use this function in the actual data feed that returns (in the included example) usage costs. Although it was working previously with anonymous auth, it now refuses to. All I get is the error: We couldn't authenticate with the credentials provided. I have tried various authentication options, but to no avail 😞
I may have altered something to cause this, and have tried going back to earlier versions in my backup. No joy.
As things stand, I can save my attempts to fix the m code, but not apply changes to the Power BI project - which means it still returns the original feed without my later improvements.
Here is the code for the feed:
let BaseUrl = "https://management.azure.com/subscriptions/" & {{AzureSubscriptionId}}{0}{0} // Newbie coding? This is how I got literal blank queries to work :) & "/providers/Microsoft.Consumption/usageDetails" & "?$expand=properties/additionalProperties" & "&api-version=2021-10-01" & "&" & filter, ApiKey = AzureToken(), // function call EntitiesPerPage = 1000, GetJson = (Url) => let Options = [ Headers = [ #"accept" = "application/json", #"Authorization" = "Bearer " & ApiKey ] ],
RawData = Web.Contents(Url, Options), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Url = BaseUrl & "?$count=true&$top=0", Json = GetJson(Url), Count = Json[#"@odata.count"] in Count, GetPage = (Index as number) as list => let Skip = "?$skip=" & Text.From(Index * EntitiesPerPage), Top = "$top=" & Text.From(EntitiesPerPage), Url = BaseUrl & Skip, Json = GetJson(Url), Value = Json[#"value"] in Value, WaitAndRun = (seconds as number, Index as number) as list => let retVal = if (List.Count(List.Generate(() => DateTimeZone.LocalNow() + #duration(0,0,0,seconds), (x) => DateTimeZone.LocalNow() < x, (x) => x) ) = 0) then null else GetPage(Index) /**/ in retVal, EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
// I am using a literal value for testing since the real page count can be huge //RealPageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageCount = NumOfPages, // Generate list of numbers up to PageCount waitList = {0 .. (PageCount - 1)}, // Make all the requests Pages = List.Transform(waitList, each if Number.Mod(_ + 1, 5) = 0 then WaitAndRun(5, _) else GetPage(_) ), Entities = List.Union(Pages), TheTable = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(TheTable, "Column1", {"kind", "id", "name", "type", "tags", "properties"}, {"Column1.kind", "Column1.id", "Column1.name", "Column1.type", "Column1.tags", "Column1.properties"}), BetterHeaders = ReplaceColumnsWithFunction(#"Expanded Column1", each Text.Replace(_, "Column1.", "")), #"Expanded tags" = Table.ExpandRecordColumn(BetterHeaders, "tags", {"Prosjektnr"}, {"tags.Prosjektnr"}), #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded tags", "properties", {"additionalInfo", "billingAccountId", "billingAccountName", "billingCurrencyCode", "billingPeriodEndDate", "billingPeriodStartDate", "billingProfileId", "billingProfileName", "chargeType", "consumedService", "costInBillingCurrency", "costInPricingCurrency", "costCenter", "date", "exchangeRate", "exchangeRateDate", "instanceName", "invoiceId", "invoiceSectionId", "invoiceSectionName", "isAzureCreditEligible", "meterCategory", "meterId", "meterName", "meterRegion", "meterSubCategory", "previousInvoiceId", "pricingCurrencyCode", "product", "productIdentifier", "productOrderId", "productOrderName", "publisherName", "publisherType", "quantity", "resourceGroup", "resourceLocation", "resourceLocationNormalized", "serviceFamily", "serviceInfo1", "serviceInfo2", "servicePeriodEndDate", "servicePeriodStartDate", "subscriptionGuid", "subscriptionName", "unitOfMeasure", "unitPrice", "customerTenantId", "customerName", "partnerTenantId", "partnerName", "resellerMpnId", "resellerName", "publisherId", "reservationId", "reservationName", "frequency", "term", "payGPrice", "costInUSD", "paygCostInBillingCurrency", "paygCostInUSD", "exchangeRatePricingToBilling", "partnerEarnedCreditRate", "partnerEarnedCreditApplied"}, {"properties.additionalInfo", "properties.billingAccountId", "properties.billingAccountName", "properties.billingCurrencyCode", "properties.billingPeriodEndDate", "properties.billingPeriodStartDate", "properties.billingProfileId", "properties.billingProfileName", "properties.chargeType", "properties.consumedService", "properties.costInBillingCurrency", "properties.costInPricingCurrency", "properties.costCenter", "properties.date", "properties.exchangeRate", "properties.exchangeRateDate", "properties.instanceName", "properties.invoiceId", "properties.invoiceSectionId", "properties.invoiceSectionName", "properties.isAzureCreditEligible", "properties.meterCategory", "properties.meterId", "properties.meterName", "properties.meterRegion", "properties.meterSubCategory", "properties.previousInvoiceId", "properties.pricingCurrencyCode", "properties.product", "properties.productIdentifier", "properties.productOrderId", "properties.productOrderName", "properties.publisherName", "properties.publisherType", "properties.quantity", "properties.resourceGroup", "properties.resourceLocation", "properties.resourceLocationNormalized", "properties.serviceFamily", "properties.serviceInfo1", "properties.serviceInfo2", "properties.servicePeriodEndDate", "properties.servicePeriodStartDate", "properties.subscriptionGuid", "properties.subscriptionName", "properties.unitOfMeasure", "properties.unitPrice", "properties.customerTenantId", "properties.customerName", "properties.partnerTenantId", "properties.partnerName", "properties.resellerMpnId", "properties.resellerName", "properties.publisherId", "properties.reservationId", "properties.reservationName", "properties.frequency", "properties.term", "properties.payGPrice", "properties.costInUSD", "properties.paygCostInBillingCurrency", "properties.paygCostInUSD", "properties.exchangeRatePricingToBilling", "properties.partnerEarnedCreditRate", "properties.partnerEarnedCreditApplied"}), removedTagsInName = ReplaceColumnsWithFunction(#"Expanded properties", each Text.Replace(_, "tags.", "")), betterColumnNames = ReplaceColumnsWithFunction(removedTagsInName, each Text.Replace(_, "properties.", "")), expensesTable = Table.Sort(betterColumnNames,{{"date", Order.Descending}}) in expensesTable
Hoping someone can help me get this working again! 😄
Solved! Go to Solution.
Hi @eddiehartman ,
Try clearing the cache and updating your credentials.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @eddiehartman ,
Try clearing the cache and updating your credentials.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.