Microsoft Power BI – connecting with Price2Spy API
24046
page-template,page-template-full_width,page-template-full_width-php,page,page-id-24046,strata-core-1.0.4,strata-child-theme-ver-1.0.0,strata-theme-ver-3.0.6,ajax_fade,page_not_loaded,wpb-js-composer js-comp-ver-5.7,vc_responsive

Microsoft Power BI – connecting with Price2Spy API

Microsoft Power BI is a popular business analytics tool that enables you to do API Queries and Database analytics with very little IT effort. In other words, it allows you to visualize your data from various sources and share insights across your organization with the help of live dashboards and reports.

 

Since more and more Price2Spy clients want to connect their Microsoft Power BI via Price2Spy API, we decided to share this with you in a simple manual.

 

There are 2 methods that you can use in order to establish a connection between your Power BI and Price2Spy API.

1. Connecting Power BI to Price2Spy API – GET methods

For all API methods using HTTP GET (such as getBrands(), getCategories(), etc. – the full list is available at https://api.price2spy.com/rest/swagger-ui.html#/rest-api

 

To connect your Power BI with Price2Spy API using GET methods, please follow these steps shown below (click on the image to get it enlarged):

 

1. From Power BI, choose Get Data => Web menu (see screenshot 1)

 

2. Click on Advanced

Screenshot 1

3. Fill in URL parts = https://api.price2spy.com/rest/v1/get-brands (this is the URL of (for example) an endpoint showing Brands in your Price2Spy account

 

4. Request Header: Authorization = Basic base64 encoded API key here (see screenshot 2)

 

Screenshot 2

Important: Please note that this method does not work for the getCurrentPricingData() method because it uses the HTTP POST method.

Then, you might wonder – why does getCurrentPricingData() use the POST method when it makes no alterations to Price2Spy data (ie: it only reads data)?

Because, when specifying filters/criteria for getCurrentPricingData(), we need to submit data to Price2Spy API – and that is possible only via JSON sent in the POST method.

 

2. Connecting Power BI to Price2Spy API – POST methods

To connect your Power BI with Price2Spy using POST methods, please follow these steps (click on the image to get it enlarged):

 

1. On Get Data, please click Blank Query (see screenshot 3)

 

 

 

Screenshot 3

2. Then, please click on Advanced Editor (see screenshot 4)

 

 

 

Screenshot 4

Here’s the example in Json.FromValue() – (see screenshot 5):

[active=”true”, productName=”Royal”, searchMode=”LIKE”]

 

results with this JSON sent to Price2Spy API:

{“active”: true, “productName”: “Royal”, “searchMode”: “LIKE”}

 

which is then converted with Json.fromValue() to binary code and sent in Content parameter of the query. (Content=Json) This is how we have achieved the POST method.

 

let

Json = Json.FromValue([active=”true”, productName=”Royal”, searchMode=”LIKE”]),

Source = Json.Document(Web.Contents(“https://api.price2spy.com/rest/v1/get-current-pricing-data”, [Headers=[Authorization=”Basic **base64 encoded API key here**”,

#”Content-Type”=”application/json”, #”Accept”=”application/json”], Content=Json]))

in

Source

Screenshot 5

Important: In case you are sending a JSON object in the Json.FromValue() method you should not send an actual JSON representation. An object here would start and end with square brackets [ ] while the values inside need to use equals sign =.

One more way is to escape a JSON string with double quotes in the editor (see screenshot 6):

 

let

Json = “{“”active””:””true””, “”productName””:””Royal””, “”searchMode””:””LIKE””}”,

Source = Json.Document(Web.Contents(“https://api.price2spy.com/rest/v1/get-current-pricing-data”, [Headers=[Authorization=”Basic **base64 encoded API key here**”,

#”Content-Type”=”application/json”, #”Accept”=”application/json”], Content=Text.ToBinary(Json)]))

in

Source

 

After that, please send this variable JSON to a Content parameter with Text.ToBinary() method. Both work the same way.

Screenshot 6

Once this is done the request will be successful and you will get a high-level JSON representation that you can drill down or create tables of data from.

 

A quick example of resulting data in Power BI (see screenshot 7)

 

Screenshot 7

For any questions you might have, please feel free to contact us at support@price2spy.com – we would be happy to assist you.

 

In case you are new to Price2Spy, you are welcome to start your free 30-day trial to establish a Power BI connection via Price2Spy API, (using any kind of API method you need).