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

Question

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

I have supplied an answer in the meantime. Let me know if you need it updating with your source table. — Davide Bacci, Dec 27 '22 at 22:03

score 1 · Answer 1 · answered Dec 27 '22 at 22:02

1

The following works for me. Paste into a blank query and it should be enough for you to copy for your own scenario.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsssKilNzFHwTUzOyMxLLVbSUXIxKlYoMwEyXEMVwlOLS5RidYhUFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [meterCategory = _t, meterName = _t, location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"meterCategory", type text}, {"meterName", type text}, {"location", 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"

answered Dec 27 '22 at 22:02

Davide Bacci

16,647
3
10
36

Hello, I don't get it David, Of course a blank query is returning me the same picture as you pasted. I don't understand why because there is no `D2s v4` in the code. – Francesco Mantovani Dec 30 '22 at 12:56
The D2s v4 is my sample data which is in the first line but compressed. You need to disregard the first line and use the rest of the code. – Davide Bacci Dec 30 '22 at 13:05
I'll add another answer with your sample data (which will again be compressed in that first line) – Davide Bacci Dec 30 '22 at 13:06
OK, I will try that. Also using the same code to add a column doesn't work: https://snipboard.io/zdMqsG.jpg it says: `Failed to resolve name 'Web.Contents'. It is not a valid table, variable, or function name.` – Francesco Mantovani Dec 30 '22 at 13:06

score 1 · Accepted Answer · answered Dec 30 '22 at 13:10

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

Hi David and thank you so much for your effort. The data are loading, Creating a column will make a query for each row we have so it's very slow. A table would have been better but I cannot make it. Tons of errors are loaded and that might be because lot of resources don't have Reservation so the API is probably returning a `null` I believe. Do you do consulting? I mean, could I pay you a few hours to get this done? I would like to submit you a CSV with a sample of the data. Let me know how much do you charge — Francesco Mantovani, Dec 30 '22 at 13:55
I don't think there is a way to speed this up BTW due to the nature of an API call per row. — Davide Bacci, Dec 30 '22 at 14:25
Yes, but maybe there is a way to create a table that takes all our resources once with the price and then reference the table. — Francesco Mantovani, Dec 30 '22 at 14:34
I would mark this as solved and ask a new question if I were you - then you can start fresh with your requirements. — Davide Bacci, Dec 30 '22 at 14:44

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

2 Answers2