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
AzureTokenThis 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
expensesTableHoping someone can help me get this working again! 😄
