Data Download from Website and connection with Power BI

[DanielPasalic]

Hi 
Anyone who can help me how to download data from one specific website and use it with Power BI?
Highly appreciated! 

[Gordonlilj]

Hi,

Is it one certain website you're having trouble with or are you asking in general how to get data from web pages?

If it's in general then you could check out the guides below that describes it pretty well.

Text: Tutorial: Analyze web page data using Power BI Desktop 

Text: Power Query Get Data from Web by Example 

Video: Power BI - How to Get Data from a Web Page 

[DanielPasalic]

Hi
Thanks a lot for your reply. It is a certain web page that I'm trying to cellect data from and establish a regular connection with Power Bi. 
It is following web page: https://www.systembolaget.se/
My attention is to go further and seach for certain product on this web-page and down load aditional informaitons: 

- number of stores that this product can be found in. 
For example, this product with article number 658108. 
Cameleon Selection Malbec 

https://www.systembolaget.se/dryck/roda-viner/cameleon-658108

[Gordonlilj]

In that case your best bet would be to try and pull data from a REST API. Doing some research it seems that they have some open APIs that you can use https://www.systembolaget.se/api/ 

[DanielPasalic]

Hi
First of all, I would like you to know that I highly appreciate your help and guidelines.  
I will definitely find a way how to thank you! 
Yes, Systembolaget does offer open API's but none of those supply information that is crucial for me, which is:
- the number of bottles for each article in a specific store. 
For example, when you click the following link:  https://www.systembolaget.se/dryck/roda-viner/cameleon-658108,
you will end up on the page for specific product, in this case, Cameleon Selection Malbec, Bag in Box (wine from Argentina). 
When you click on the link "Vilka butiker har drycken?" (green colour) you will be able to see all stores that have this particular article on the stock and how many. By clicking "Visa fler butiker - show additional stores", several times, you will get all the stores. If I could get this information in Power BI (by establishing connection with web-page), this would save me a lot of work and I will be able to present this in table format in Power BI desktop. 
What is REST API? I never work with that one. 
Highly appreciated if you could help me with this one 
 

[Gordonlilj]

Hi, Excuse the slow response, celebrating the new year takes a toll.

The problem with just using the web source is that in this case it would require an incredible
amount of manual input. So much so that it would be quicker just to copy the information
directly from the website into excel and use that as a source. So the best solution would be using an API.

Doing some more research i was able to find some other APIs. After some testing and such i was able to build up some queries that i believe contain such information you asked for.  

Check the image below and tell me if that is the kind of data you're asking for. You'll also find the report below (DropBox) if you want to take a deeper look. I haven't been able to spend much time with it so excuse the poor quality of the report.

DropBox: TestSystem2 

Please tell me if it's usable and i'm happy to explain how it works in the case that it's what you're after

[Anonymous]

How do you search for the API's for a specific website?  I have a website my company uses for Fleet information and maintenance - I would like to establish the connection but am not sure where to find the API for the website.  I'm using the Samsara application, and I have established a registration as an admin.  Just not sure where to find the API.  Any help would be appreciated.

[DanielPasalic]

This is exactly what I need 🙂 

[DanielPasalic]

I really appreciate your help!
Wishing you a great start in 2020! 

[DanielPasalic]

How can I apply this API to my Power Query Editor?

[Gordonlilj · Accepted solution]

Using the API http://vinomondoappar.se/kund/winepartners/wp_xml.php?product=7308501

First go into power query and crate a parameter and copy what the image show

Next add a new source "web". Go into advanced and in the first part add the URL but only up to the = sign.

In the second part, change from text to parameter by pressing the down pointing arrow and pick "parameter".

Expand the info column and choose what columns you want. Next right click on the table and go to "create function" and name it to whatever you want.

               

After this create a new blank query 

In the bar is where you will add the article numbers

type = {7308501,640501} and for every other article just add another delimiter and number. Press enter when you have typed in the numbers and then convert the list to a table. 

Make sure to change the datatype of the column to text. Right click the column and go to "change type"

For the last step go into the "add column" tab and press "invoke custom function".

Name it, add the function and change "parameter" from text to column name.

Expand the new column and it's done.

If afterwards you want to add more article numbers just go back to the the source step in “applied steps” and add the numbers like before then go back to the last step.

I hope this helps and it was what you were after

[DanielPasalic]

Hi
I have checked and basically, everything is working how it should. 
However, main problem is that I need similar things but for wine, not beer and this API is showing only information's about beer. 
Any possibility how to convert so it shoes wine instead. 
For example, I have a list for all id related to wine. How can I convert this API and connect to that list?
Sorry for bothering you, as you helped me a lot already. 

[DanielPasalic]

In this open API from Systembolaget.se: https://www.systembolaget.se/api/

I can collect all information's I need except for a number of bottles on the stock:

https://www.systembolaget.se/api/assortment/products/xml

Collecting information from this API is also very fast. 
The question is how possible is to implement similar API as json, but include even wines as well. 

https://systembevakningsagenten.se/api/json/1.0/inventoryForProduct.json?id=2307

[v-juanli-msft]

Hi @DanielPasalic 

In this API document, please pay attention to the searchProduct.json,

You could use this API to search for specific products.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanielPasalic]

@v-juanli-msft 

Hi Maggie
As I'm very begginer, I do not know what steps to take in order to apply this API to my Power BI. 
It is definitely what I need, but please if possible to help me step by step how to do it. 
I really apreciate @Gordonlilj inputs as they were so detailed. 
Thanks a lot

[Gordonlilj]

I missed the part where the API is unfortunately only about beer. So as it seems you can't get any other data out of it. The suggestion above does not show wine products but beer products with the word wine in the product name.