Mois : avril 2023

The purpose of this article is to describe how to retrieve extended data in the Microsoft Power Query Editor. The main idea is to return, for each rows, information from a Web API.

Start with the Microsoft Power BI application, described in previous article in order to already have an application with a list of Crypto Currencies lodad. In this article we will retrieve, for each currencies, the corresponding prices through Web API provided by https://www.coingecko.com/en/api/documentation

https://powerbi.funspirit.ch/microsoft-power-bi-get-data-from-web-api/

Get Web API Link

In this example we will import all Crypto Currencies from https://www.coingecko.com/

The goal is, for each crypto currencies, to call a Web API to retrieve the price in CHF. Somehow, similar call will be performed

https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=chf

Each call will return similar value

{"bitcoin":{"chf":29499}}

For each crypto currency, the price will be loaded.
At the end of this article, you will have a Microsoft Power Bi Application, with a list of crypto currency with corresponding prices.

Get Data

In order to connect to Web API, Launch the Power Query Editor, select in « Get Data« , the « Web » option and click on the button « connect« 

Select the « Advanced » option and paste the URL https://api.coingecko.com/api/v3/simple/price in the first URL parts:

Add multiple parts, as illustrated below and click on the button « Ok« :

Right click on the query and click on « Advanced Editor« . Below formula had been created:

let
    Source = Json.Document(Web.Contents("https://api.coingecko.com/api/v3/simple/price" & "?ids=" & "bitcoin" & "&vs_currencies=" & "chf")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded bitcoin" = Table.ExpandRecordColumn(#"Converted to Table", "bitcoin", {"chf"}, {"bitcoin.chf"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded bitcoin",{{"bitcoin.chf", Int64.Type}})
in
    #"Changed Type"

Embed the code into a function, called GetPrice, as illustrated below:

Update the code to get dynamic parameters instead of hardcoded values:

Click on the button « Done« 

Test Web API

Enter corresponding test values for cryptoId and currencyId, like bitcoin and chf and click on the « Invoke » button:

Web API call will be performed and value will be shown

Rename your query, like « GetPrice » to easily integrate it:

Congratulation, we have now a function GetPrice which could be called in any queries.

Integrate Web API call for each items

This function could be used in any loader.