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.