powerbi dax powerquery powerbi-desktop azure-rest-api

Power BI :: Create column in table that queries REST API based on elements

I'm using the Azure Cost Management connector in Power BI Desktop to query our Azure Portal and pull data from the Cost Management.

GOAL: I would like to create a column with the 3 year Reservation so we can forecast what we could spend. The data must come from the official Azure Retail Prices overview.

REST API Query Example: If I want to see what is the cost for a Virtual Machine D2s v4 I can query the REST API through this query:

https://prices.azure.com/api/retail/prices?$filter=serviceName eq 'Virtual Machines' and skuName eq 'D2s v4' and location eq 'EU West' and reservationTerm eq '3 Years'

If you try to paste that into Postman or a simple web browser the result will look like this:

{
    "BillingCurrency": "USD",
    "CustomerEntityId": "Default",
    "CustomerEntityType": "Retail",
    "Items": [
        {
            "currencyCode": "USD",
            "tierMinimumUnits": 0.0,
            "reservationTerm": "3 Years",
            "retailPrice": 1148.0,
            "unitPrice": 1148.0,
            "armRegionName": "westeurope",
            "location": "EU West",
            "effectiveStartDate": "2020-08-01T00:00:00Z",
            "meterId": "ee6e2e65-b1d2-5863-a209-c584a58606a8",
            "meterName": "D2s v4",
            "productId": "DZH318Z0CSHJ",
            "skuId": "DZH318Z0CSHJ/01DB",
            "availabilityId": null,
            "productName": "Virtual Machines Dsv4 Series",
            "skuName": "D2s v4",
            "serviceName": "Virtual Machines",
            "serviceId": "DZH313Z7MMC8",
            "serviceFamily": "Compute",
            "unitOfMeasure": "1 Hour",
            "type": "Reservation",
            "isPrimaryMeterRegion": true,
            "armSkuName": "Standard_D2s_v4"
        }
    ],
    "NextPageLink": null,
    "Count": 1
}

Very Good! What I need is a column with the value "unitPrice": 1148.0,

I found a few examples about how to do this: Example1, Example2.

But when I try to create a new column with this code:

Column = Web.Contents("https://prices.azure.com/api/retail/prices?$filter=serviceName eq 'Virtual Machines' and skuName eq 'D2s v4' and location eq 'EU West' and reservationTerm eq '3 Years'")

Power BI returns the error Failed to resolve name "Web.Contents". It is not a valid table, variable, or function name.

IDEAL SOLUTION: The ideal solution should be to query every single resource we have based on the Usage details table so ideally the final query should look like:

Column = Web.Contents("https://prices.azure.com/api/retail/prices?$filter=serviceName eq ''Usage details'[meterCategory]'' and skuName eq ''Usage details'[meterName]'' and location eq ''Usage details'[location]'' and reservationTerm eq '3 Years'")

because I want to take from the table Usage details:

serviceName from the column ''Usage details'[meterCategory]''
skuName from the column ''Usage details'[meterName]''
location from the column ''Usage details'[location]''

So how to create a column that query a REST API for each row?

If it's to hard to do that in a new column I can also accept a new table as answer.

EDIT: Let me add here the Unit price table:

subscriptionName	resourceGroupName	ResourceName	meterCategory	meterName	meterSubCategory	product	productId	consumedService	serviceFamily	Sum of costInBillingCurrency	Year	Month	location	meterId
infra - Contoso	My-Resource-Group	VM-1	Virtual Machines	D4ds v4	Virtual Machines Ddsv4 Series Windows	Virtual Machines Ddsv4 Series Windows - D4ds v4 - EU West	DZH318Z0CSHH006Q	Microsoft.Compute	Compute	9.28 CHF	2022	September	EU West	48889cbd-650c-56c1-a66b-c5ca1d84fbd9
infra - Contoso	My-Resource-Group	VM-10	Virtual Machines	D2s v4	Virtual Machines Dsv4 Series	Virtual Machines Dsv4 Series - D2s v4 - US East	DZH318Z0CSHJ0051	Microsoft.Compute	Compute	1.95 CHF	2022	September	US East	c04e5d97-2de2-5244-b91a-1e57a0a49a61
infra - Contoso	My-Resource-Group	VM-11	Virtual Machines	D2s v4	Virtual Machines Dsv4 Series	Virtual Machines Dsv4 Series - D2s v4 - US East	DZH318Z0CSHJ0051	Microsoft.Compute	Compute	1.95 CHF	2022	September	US East	c04e5d97-2de2-5244-b91a-1e57a0a49a61
infra - Contoso	My-Resource-Group	VM-12	Virtual Machines	D2s v4	Virtual Machines Dsv4 Series Windows	Virtual Machines Dsv4 Series Windows - D2s v4 - DE West Central	DZH318Z0CP0700SP	Microsoft.Compute	Compute	4.21 CHF	2022	September	DE West Central	0fa07af6-1ac7-5027-8ab7-ee9997c4a9fe
infra - Contoso	My-Resource-Group	VM-13	Virtual Machines	D2s v5	Virtual Machines Dsv5 Series	Virtual Machines Dsv5 Series - D2s v5 - US East	DZH318Z08M9W0061	Microsoft.Compute	Compute	1.95 CHF	2022	September	US East	36cc2454-47d7-517f-884f-be4d6b7efa55
infra - Contoso	My-Resource-Group	VM-14	Virtual Machines	D2s v5	Virtual Machines Dsv5 Series	Virtual Machines Dsv5 Series - D2s v5 - US East	DZH318Z08M9W0061	Microsoft.Compute	Compute	1.95 CHF	2022	September	US East	36cc2454-47d7-517f-884f-be4d6b7efa55

Here you can find the meterCategory, the meterName and the location

Solution

Here you go. FYI, your last two rows produce errors so I think there is a problem with the sample data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dRfS8MwEADwrxL27I0ky9/nbjqEwbTowOFDmlyxoO1ou4nf3lQnTO2mQ9/0KZdLcw0/LlkuB0WZ144ASaqyrZqKDE4Gsye4xKZa1x7hrK7Wqy55PQP2MhZ1u3b3ZOb8XVFiE1NjERqyET2LZByajSAp1kWcLIoyVI9NX5X+D4FsS8dockUW2LTd5vHNdMTMDU3S6ZRSdfFy5sLX8fh5O0yqh9W6xS65E9ohNySZnnYxp5x3Y4qrFh8yrLvJtn6MhDHG+iyAktSDVJ6BUyoDL71jwYg8C3Zwe3IcHd1nx/fS7YB8tR6h+NbpKiUT98npnFLJvuPEhlYedNrWj5GnAmWwGnhADpILAZllDhhK7agT1il2tNPeHvt3eufEf+R08Cb2X8Q3t/Hk9R4mWLZ13LfrN6ea0nT+HT8x5Oyg34f/xAzNHdUuV8Cc1yAp12BcpgHRWqu9cDbHox1HhxzlHh/5Rb/JD/0m+/vNzOwivl+/3W8j5T0XUoDQIToxnYOJrxZkKILKNOZOyqOdxF91un0G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [subscriptionName = _t, resourceGroupName = _t, ResourceName = _t, meterCategory = _t, meterName = _t, meterSubCategory = _t, product = _t, productId = _t, consumedService = _t, serviceFamily = _t, #"Sum of costInBillingCurrency" = _t, Year = _t, Month = _t, location = _t, meterId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"subscriptionName", type text}, {"resourceGroupName", type text}, {"ResourceName", type text}, {"meterCategory", type text}, {"meterName", type text}, {"meterSubCategory", type text}, {"product", type text}, {"productId", type text}, {"consumedService", type text}, {"serviceFamily", type text}, {"Sum of costInBillingCurrency", type text}, {"Year", Int64.Type}, {"Month", type text}, {"location", type text}, {"meterId", type text}}),

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    Source = Json.Document(Web.Contents("https://prices.azure.com/api/retail/prices?$filter=serviceName eq '"&[meterCategory]&"' and skuName eq '"&[meterName]&"' and location eq '"&[location]&"' and reservationTerm eq '3 Years'")),
    Items = Source[Items],
    Items1 = Items{0}[unitPrice]
in
    Items1)
in
    #"Added Custom"

Here is the code for just the custom column:

let
    Source = Json.Document(Web.Contents("https://prices.azure.com/api/retail/prices?$filter=serviceName eq '"&[meterCategory]&"' and skuName eq '"&[meterName]&"' and location eq '"&[location]&"' and reservationTerm eq '3 Years'")),
    Items = Source[Items],
    Items1 = Items{0}[unitPrice]
in
    Items1