Screenshot 1
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
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. HTTP request header parameters: type in Authorization and in the field next to it, type in Basic base64 encoded API key (for example Basic ZmM5Zjdm………OA==)
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
3. Once editor page is opened you are free to choose one of the following code samples:
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
- Replace the sample value in Json.FromValue(…) with your desired query matching the format shown in the sample above. You can skip to step 4 if you have chosen the first sample.
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
- If you are using the second sample replace the sample value with Json = *your desired query here*. You should also match the format shown. Proceed to step 4.
4. Finally, replace **base64 encoded API key here**” with your API key encoded in base64 format. If you don’t know how to do this, just go to https://www.base64encode.org/. Paste your Price2Spy API key and in the first text box and click on „Encode“. Copy and paste the result you got to Power BI editor.
If you did the above step correctly Power BI will show in the lower-left corner of the advanced query window (as seen in screenshots 5 and 6) “no syntax errors have been detected“.
Screenshot 5
5. Click on done.
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).