Scrape Data from Multiple Web Pages with Power Query

[@robertbartlett3757]

That is absolutely brilliant!!! I have spent the last two days trying to figure out how the do it in Python and within 8 minutes you showed me a much easier straight forward way.

[@MyOnlineTrainingHub]

:-) so pleased it was helpful, Robert!

[@abhinandanaams2613]

@@MyOnlineTrainingHub can i download epaper into pdf without coding?

[@JayPatel-hc8dq]

lol... literally me too.. i got quite for until python was reading arabic webpages in hex and then i thew my laptop out the window!

[@obinnaduru3815]

Thank you so much for this video. Very practical for my Data Analyts journey. I followed the steps and didn't tun into any errors.

[@MyOnlineTrainingHub]

So pleased you found it helpful!

[@fentian]

Wow, what an astonishing concept and how wonderfully well you've explained it. I've just applied it in Excel PQ to call an API over and over with a number of variables including a date that changes for each iteration, returning JSON data that is then transformed and presented in a pivot table. Thank you Mynda, xxx

[@MyOnlineTrainingHub]

Thank you! So pleased this video was helpful.

[@abdulhaseeb8027]

It's like you have read my mind because I was looking to scrape data from web like this currently. Thanks for the tutorial it's really helpful.

[@MyOnlineTrainingHub]

My pleasure, Abdul!

[@prameelar1753]

I watched this video on this teachers day, and I believe you are one of the best teacher could help me on web scraping... 🤗

[@MyOnlineTrainingHub]

Wow, thank you!

[@Secret구이구이]

Thank you! It is hard to study in Korea because there is not much data about powerquery. Thanks to this, I integrated several post api into a single query.

[@MyOnlineTrainingHub]

Pleased I could help!

[@davegoodo3603]

A bit beyond me at this point Mynda, Power Query is on my "to learn" list. Well presented.

[@MyOnlineTrainingHub]

Thanks, Dave! Power Query is amazing...I'm confident you'll think so too :-)

[@malaniebanney1634]

I slightly adjusted this to scrape data from a folder full of PDF files. Excellent thanks!

[@MyOnlineTrainingHub]

Glad it helped!

[@jamessawyer8565]

I wasn't even aware that M/Power Query can be used to such extent. Thank you for the great insight!

[@MyOnlineTrainingHub]

Glad you enjoyed it, James!

[@awesh1986]

Thanks Mynda, there is no way that I would not like this video. It's awesome.

[@MyOnlineTrainingHub]

Thanks so much, Awesh! And thanks for sharing it on LinkedIn :-)

[@sushicatsan]

I knew this was possible, but ran into some errors while trying to do it on my own. Thank you very much for the great tutorial. Now to let Power Bi Spin!

[@MyOnlineTrainingHub]

Glad it helped!

[@omenaokoro4693]

spot on. I was only able to do the first page. This gives me the ability to do an entire site.

[@geoffreyzziwambazza7862]

To think I was doing this manually 🤦🏽‍♂️. Thank you, this is a huge time saver!

[@MyOnlineTrainingHub]

Great to hear, Geoffrey!

[@davidstevens4064]

Wow...Easily used this tutorial to query printer settings from every Zebra printer on my LAN. Very helpful!

[@MyOnlineTrainingHub]

Awesome to hear, David!

[@markhooper279]

That's remarkable; this is like the limit of most peoples Python learning, and most co-workers would consider them "dangerous" with those Python abilities. (in the most professional and excellent way of course!)

[@MyOnlineTrainingHub]

:-) Glad you liked it, Mark!

[@awesh1986]

This is an amazing way of working with web pages. I have seen people write lengthy macros and Python code for this.

[@MyOnlineTrainingHub]

Yes, Power Query is super easy to use. I wish more people knew of it's powers ;-)

[@naotoaguilarmorita7079]

Thanks a lot for this tutorial! I could get mutiple api call in single query, best solution ever!

[@MyOnlineTrainingHub]

Glad it’ll be useful!

[@biswajeetswaro7831]

Great video mam!!! I was doing this before python then saved into csv then importing to PBI. Now I can do with PBI directly 👏👏👏

[@MyOnlineTrainingHub]

Wow, that's fantastic to hear :-)

[@StephanOnisick]

Awesome use of M for us tiptoeing into the M Script!

[@MyOnlineTrainingHub]

Glad you liked it!

[@prashantmanshrestha]

Clear Voice, Beautifully Explained Super-woman.

[@MyOnlineTrainingHub]

Thank you so much 🙂

[@LeonoraRolston]

This is fantastic! Had no idea it could be done. Explained so well - thanks!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@khalidessaadi8915]

Wonderful job ! So clear and perfectly explained, thank you so much !

[@MyOnlineTrainingHub]

Glad it was helpful!

[@Kingleer69]

Mynda- @ 1:18 - Instead of the 13 HTML Table options listed in your ‘Navigator’ dialog box, when I try to run the same Power BI query on my end, I am getting only 5 Tables (Table 0 through to Table 5; & an additional Document Table.) And these tables there is hardly any data to work with. Please advise.

[@MyOnlineTrainingHub]

I get that now too. I guess the website has changed it's layout. Table 3 is the one you want.

[@Kingleer69]

@@MyOnlineTrainingHub Thank you, Mynda.

[@MichaelBrown-lw9kz]

This is simply awesome, now I have to practice this technique.

[@MyOnlineTrainingHub]

Enjoy!

[@victorgabrielcamargo6384]

Wooww thank you so much, took me months to find this function. I will try it in a more complicated webpage. thank you

[@MyOnlineTrainingHub]

Glad you can make use of it! 😊

[@MichaelHendersonMHC]

Brilliantly framed and well communicated. Thank you again Mynda.

[@MyOnlineTrainingHub]

Thanks so much, Michael!

[@deepakd-w5h]

Merci Beaucoup madame. You made my work very easier

[@MyOnlineTrainingHub]

I'm so glad!

[@merbouni]

I have never tried this, but I frequently convert data from the csv file to the html Datatable, Thanks Mynda.

[@MyOnlineTrainingHub]

Hope you can make use of it, Reda!

[@vincasvosylius6045]

You are the legend! Helped me to solve this greyed out "change data source "button

[@MyOnlineTrainingHub]

Great to hear, Vincas!

[@abcapplesg]

thank you for your video! but Can i please ask is it possible to add multiple sources? like under 4:02 instead of just one link how can I add multiple sources/ links? For instance I would want to do what you have done (pulling out all book titles from page 1-20) but from multiple different sources. E.g. All listing names under books (page 1-20) + all listing names under Biscuits (page 1-20), hence i would want to add multiple sources

[@MyOnlineTrainingHub]

You would do this in separate queries and then append those queries together.

[@athilfaizaan8558]

Absolutely useful video! Thank you for this. Also I have a doubt, I have to scrape 560 pages and each page has 25 number of items that I need. I'm a little confused on the modulo part. In 5:28 of the video you say the starting number of pages that u need are 1, 11, 22 etc. But in my case the pages the I require are 1, 2, 3 etc and 25 records in each page. So do I use modulo same as you with 1, 26, 51 etc or avoid the modulo part and continue?

[@MyOnlineTrainingHub]

Great to hear! Yes, for the modulo enter the number of records in a page. Try it and see if you get the results you expect and adjust as necessary.

[@athilfaizaan8558]

@@MyOnlineTrainingHub Thank you so much for answering my question and yes it worked, except I got one small problem. Let's say I'm trying to get the rental data from a website and I require three columns; Address, Price and Area (sqft). I chose it correctly from the select from example option. But after seeing the preview of the tables, I see that it has gotten a different category of data (additional info like gym/swimming pool instead getting the address that I wanted). I thought I selected it wrongly and did it again but I got the same results. I'm tackling the problem by now getting only the address and merging with the two tables together. But do you know why this is happening or a workaround for it?

[@MyOnlineTrainingHub]

Not sure as I'm not familiar with the site. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@ramakumarguntamadugu1299]

Great Video... Thanks for the efforts and sharing it. this will be very useful for many tasks...

[@MyOnlineTrainingHub]

Great to hear!

[@michalvydrzel]

YOU ARE THE BEST!! Saved me so much work!

[@MyOnlineTrainingHub]

So pleased I could help 😊

[@CEYLAN64]

Thank you very much. I'm from Turkey. Have a nice day.

[@MyOnlineTrainingHub]

Thank you! You too!

[@stephencross4978]

Wow, this is clever and exactly what I needed. My mind is blown !!

[@MyOnlineTrainingHub]

Awesome. Glad I could help 😊

[@julianstarkey9301]

Very helpful, a lot less complicated excel formulas in my life now, shame that challenge has gone but I had to think a lot about my queries.

[@MyOnlineTrainingHub]

Don't be sad that the challenges have gone...there are plenty of new challenges awaiting; M code, DAX, dynamic array functions :-)

[@fabio.s.barbosa]

Wonderfull tutorial! that was exactly what I Looking for. I was duplicating datasources for each week to scrap some web data. Thanks a lot!

[@MyOnlineTrainingHub]

So pleased it helped, Fabio!

[@shakiraasfoor7599]

Well Done Mynda All Your Videos Are Useful

[@MyOnlineTrainingHub]

Cheers, Shakira!

[@bryandadiz5677]

The website is not anymore updated

[@valentecg8518]

I really appreciate your tutorial! monysaver! Most data extraction tools are costly.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@peimanhosseini37]

thank a lot, that was really really useful. you solve my very big problem. 🙏🙏🙏🙏🙏🙏

[@MyOnlineTrainingHub]

Awesome to hear! 😊

[@machadolopes]

Amazing how it is easy to scrape web pages. Thanks for this excellent tutorial.

[@MyOnlineTrainingHub]

Glad you like it, Marcel!

[@iankr]

Brilliant! Many thanks, Mynda.

[@MyOnlineTrainingHub]

Cheers, Ian!

[@nazaarshadir]

Another great lesson. I have a website with unstructured data for many items. I need specific values for each item from the site. Please, how may I do it automatically and quickly. cftc .gov/dea/futures/deacmesf . htm I only need LONG and SHORT value for each code. Thanks.

[@MyOnlineTrainingHub]

Great to hear, Nazaar! The URL provided isn't right. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@nazaarshadir]

@@MyOnlineTrainingHub thanks for the quick reply. I just joined the forum. Your forum is clean and organized. Looking forward to learning more. Thanks.

[@powerb_i]

Great video thanks this makes web scraping a lot easier. Thank you.

[@MyOnlineTrainingHub]

Great to hear!

[@eslamfahmy87]

Thank you, one more thing if my pages contain PDF files and I need to add another column which contains that PDF and I need to be accessible by link

[@NadeemShafiqueButt]

As always, an excellent tutorial

[@MyOnlineTrainingHub]

Glad you liked it!

[@jbjs5820]

Excellent work. just a question, when i try to refresh it in the system it doesn´t allow. indicates "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed", any workaround?

[@StephenMattison66]

Great info, easy to understand. TYVM! I'd love to learn how to do all of this in Google Sheets. Power Query sounds cool!

[@MyOnlineTrainingHub]

Glad you liked it, Stephen! Sheets doesn't have Power Query.

[@naveedkhowaja4089]

Excellent tutorial, super easy to follow. That’s brilliant 👍

[@MyOnlineTrainingHub]

Glad it was helpful! 🙏

[@mariaalcala5159]

Wow amazing what you can do! Thanks a lot mynda I’m always learning from you!

[@MyOnlineTrainingHub]

So pleased to hear that, Maria!

[@03mariadelmar]

Hi! Your tutorial is very clear. However, what if the web page you are trying to access needs your credentials first? Do you know how I can go around that? Thank you!

[@ritvikbolugudde8688]

Thanks a lott!! I was wondering if the web page is updated would the loaded data in power bi update too (so basically if it's real time or not)

[@MyOnlineTrainingHub]

Only direct query datasets can refresh real time, however, you can schedule refreshes at set intervals.

[@austinbright-j3o]

Can you get around captchas for more advanced stuff?

[@MyOnlineTrainingHub]

Not captchas, AFAIK.

[@AnonymousHunYaar]

Marvelous ! You make it so easier, Thanks a lot

[@MyOnlineTrainingHub]

Thank you! Glad to hear that!

[@WeKnowIt100]

Hi! I have encountered a login page before the page that i need to scrap. Anyway can i bypass the page or key in the credentials?

[@iliyatsekov6044]

Many thanks for the video! What if I have two variable names? My URL includes both a year and a quarter. I created the two variable names but how do I invoke the function to take all quarters from every year?

[@MyOnlineTrainingHub]

Make a table containing the string made up of the quarter and year components and whatever other characters form that section of the URL, and feed that into a single variable.

[@darrylmorgan]

Hi Mynda!Great Tutorial,Just Learnt Something New So I Can Have More Fun With POWER BI..Thank You :)

[@MyOnlineTrainingHub]

Great to hear you found it useful, Darryl!

[@ssomtom]

Beautiful. It's solved my actual problem. Thx. :)

[@MyOnlineTrainingHub]

Great to hear!

[@carltonquine9277]

Wow you're amazing! Can't believe this information is free! Thank you so much!

[@MyOnlineTrainingHub]

You're most welcome, Carlton!

[@m_shakes]

Amazing video and awesome ideas that I incorporated instantly! Quick question, how would you go about making each "page" into a separate query (each page a query on its own)?

[@MyOnlineTrainingHub]

Glad you liked it, Mohammed! To make each page a separate query, you'd have to create them one by one by pasting in the URL for each page, or copying the query and modifying the URL to point to a different page.

[@m_shakes]

@@MyOnlineTrainingHub Thanks for your prompt reply!

[@kulern98]

Is it possible to use requests or something in Power Query to get the string needed for the next page in order to get all Pages? The pagination is not linear or in a pattern.

[@kulern98]

The URL in itself is the same as for your URL, with “start=“.

[@MyOnlineTrainingHub]

You'd need to know what the next page is in order to provide it to Power Query. It's not something Power Query can request.

[@jayli3291]

Great resource! I am curious about replacing 1 with "&PageStart&". Can you explain why we use the double quotes coupled with the double ampersand? Which language/grammar are we following here, M or HTML or something else? I just wanted to learn more coding rules so I can crack the query more freely. I would appreciate any help you could provide.

[@jayli3291]

I guess I figured it out. We are just concatenating the opening " with PageStart and then with the closing "; the & works as the concatenation operator. And because PageStart is a text variable, we need to put it inside the double quotation marks.

[@MyOnlineTrainingHub]

You got it 👍

[@marosbrezovsky751]

When we assume that bookstore extend the number of pages in the time, how can I set it up so that query will check all pages available. I can not set it up so, because when it checks the urls which does not exists yet, it will stop sraping procedure. Is it possible to fix it somehow?

[@MyOnlineTrainingHub]

You can build in some error handling so that when it gets to a page that doesn't exist it doesn't break the code: kzbin.info/www/bejne/f6bSXpyKpZ2UfMU

[@仁です]

It's usefull. Thanks you. I am looking for silimilar data scraper software. Do you mind to show me how to work with power BI in the case with differences website please.

[@charlesmcdermott282]

Awesome! I managed to import a table for 1 page from a URL. It is a list of books unfortunately the number of books per web page varies. Is there a way to handle the issue of generating each page number in this case? As a backup is there a method of exporting all pages to a csv file and Load & Transform the csv back into PBI or PQ?

[@MyOnlineTrainingHub]

Glad it was useful, Charles. In terms of figuring out the number of items on a page, I'm not sure there's any way to do that in advance of accessing the pages. Whether there's a way to export the pages to a csv file would be down to that website and whether it offers that as an option. It's not something Power Query can do.

[@eo4922]

Incredible overview, thank you so much! Is it possible to do this if you have a site with multiple pages that uses the same URL? I'm trying to scrape data from a public site with multiple pages, but all of them use the same URL - there are no unique identifiers (e.g. page numbers). Any assistance would be greatly appreciated.

[@MyOnlineTrainingHub]

Glad it was helpful. Unfortunately, if the site's URL doesn't change, then you can't scrape the data with Power Query.

[@eo4922]

@@MyOnlineTrainingHub Understood. Could you recommend any other options that may be helpful? Thank you in advance.

[@MyOnlineTrainingHub]

Only to say that if you know JavaScript (I don't) you can write some code to change the 'page' displayed so you can get the data.

[@k0023382]

Hi, Following your example, if you want store the book title Hyperlink, how do you get that? At the moment it stores the title as text only. Regards

[@MyOnlineTrainingHub]

You can do this in Power BI: docs.microsoft.com/en-us/power-bi/create-reports/power-bi-hyperlinks-in-tables

[@youse3]

Thank you so much for this video. what if we have "read more" instead of page numbers ?

[@MyOnlineTrainingHub]

Power Query typically can't see 'read more' information unless it's already in the page HTML. If it's generated using JavaScript, then you can't scrape it.

[@webscrapingseniors]

Power Query can struggle with data generated by JavaScript after the initial page load. In such cases, consider using a web scraping tool like Selenium, which can handle JavaScript and interact with 'read more' buttons to load additional content. This way, you can extract all the necessary information from the page. Let me know if you need more guidance!

[@Chriiichriii]

Exactly what I was looking for, thanks ! great video

[@MyOnlineTrainingHub]

Glad you found it helpful 😊

[@calleranchero3212]

The webpage I am trying to query frequently changes. When refreshing the table, is it possible to maintain the historical data while also pulling in the new information?

[@MyOnlineTrainingHub]

You would have to use VBA to automate taking a copy of the data before refreshing the query.

[@manuelsanchezmiranda1458]

Is it possible to scrape the data within the listing page (visible in a separte URL once you click on each item), and not only the data listed in the search results?

[@MyOnlineTrainingHub]

You’d have to enter the URL of the listing pages to do this.

[@mohamedadjal8502]

Hi, Professor, you have provide in a lot of effort for these videos, thank you, I have a question in excel, if we have for example in cell "a1" the number 10.00 m, how to have this number with the same format in cell "b1 "using a text function or some other function, thank you very much.😃👍

[@MyOnlineTrainingHub]

Thanks, Mohamed! I'm presuming the value in A1 is a number, in which case you can use this formula: =TEXT(A1,"##.00,,\M")

[@mohamedadjal8502]

Good evening Professor, I thank you for the answers that you sent me on the Internet. May God protect you. Suppose we have in cell a1 = "excel", in cell a2 = "is", in cell a3 = "fun", in cell b1 = 12.00m, in cell b2 = 10.00gr, in cell b3 = 15.00kg, use the vlookup function: vlookup = ("is", $ a $ 1: $ b $ 3,2, false), the result is 10, which means that This function didn't give me the full format of the number in cell b2 (b2 = 10.00gr), but my goal is to get b2 = 10.00gr and not b2 = 10. thank you so much.😃👍

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@mohamedadjal8502]

@@MyOnlineTrainingHub Good morning Professor, I have emailed you an excel file containing a question and comment on the question, thanks a lot for the help.

[@wayneedmondson1065]

Hi Mynda.. another great example and technique. Thanks for sharing it :)) Thumbs up!! PS - Any idea when the Add Table Using Examples feature will come to Power Query in Excel in Microsoft 365?

[@MyOnlineTrainingHub]

Thanks, Wayne! No idea when Excel will get Add Table Using Examples :-( it has been available in Power BI for quite a while now, but that doesn't seem to mean anything.

[@DLHSuper]

Hi Mynda, you’re videos are teaching me so much... is there a way to scrape a website that only works in google chrome or Firefox? Unfortunately the website I need to scrape doesn’t work in IE.

[@MyOnlineTrainingHub]

Pleased to hear that! If the website doesn't work in IE then you should still be able to scrape it, but you might not be able to see the preview and use the 'from example' tool. Power Query is looking for HTML tags in the web page source code, so as long as your data is stored in these, Power Query can find it. If it's tables generated using JavaScript, then you can't easily get the data using Power Query unless you know how to write JavaScript!

[@DLHSuper]

MyOnlineTrainingHub thank you for explaining this, I’ll have another go toddy 😊

[@lindalai1406]

Thank you very much for bringing this brilliant video. I do have a question, if I am not used to using Power BI and still want to use excel to extract web data like you do in this video, how do I do that?

[@MyOnlineTrainingHub]

Hi Linda, Power Query in Excel doesn't have 'from web by example'. Your best option is to use 'From Web', but that will require the data in the web page to be stored in a HTML table. You'll know if it is, because you'll be able to see the table in the preview in Power Query.

[@lindalai1406]

@@MyOnlineTrainingHub Thank you very much for your prompt response.

[@StephenMattison66]

I need to scrape data from a map page that shows thousands of map-pins that each lead to the contact data that I need. Do you have a video already showing that? Any suggestions? TYVM!!

[@MyOnlineTrainingHub]

No examples of that. Unless the map data is stored in a table in the web page HTML then you won't be able to scrape it with Excel. You could try Power BI to get data by example: www.myonlinetraininghub.com/power-query-get-data-from-web-by-example

[@chrism9037]

Super cool video, thanks Mynda

[@MyOnlineTrainingHub]

Cheers, Chris!

[@petermcallister908]

Great tutorial! Helped me a lot. But do you have any idea, why "Add Table Using Examples" won't work and throws this message: "This Stencil app is disabled for this browser"?

[@MyOnlineTrainingHub]

Never heard of that before, Peter. It sounds like you're trying to use Power Query online because there's reference to a browser.

[@jamesflieder8164]

Great video and so clear with the explanation! My researching will be much easier now!

[@MyOnlineTrainingHub]

So pleased it was helpful 😊

[@wrandyrice5447]

Mind blown. This is awesome. Thank you.

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@PowerProd]

Hi ! Do you know if you can add a proxy list so your IP isn't blocked and rotates to avoid captcha ?

[@MyOnlineTrainingHub]

Not that I know of.

[@PowerProd]

Ok thanks ! Maybe you can retrieve data via python and include a proxy list in the process. Thoughts ?

[@TheJoshtheboss]

I was having a few difficulties and got a syntax error when adding the variable into the function and got stuck. The website I was working with does not have the string "=1" but instead uses a suffix 2017, 2018 and so on at the end of the web address. You mention a list of textstring variables can be imported. What exactly do you mean? Sorry I am a little confused and new to this. Been watching your vids, they are awesome. I am surprised how much better PowerBI is at grabbing web data via Power Query than Excel Power Query. Excel cannot even recognise the data on the page while PowerBI no problem. Also, can the finished query be then saved and imported to Excel to work with the data? Sorry, too many questions, my mind is too stimulated from your video :)

[@MyOnlineTrainingHub]

Hi Josh, what I meant by text string variables was if your URL had a variable for say, country, you could feed it a list of country names which are text strings. Yes, Power Query in Excel is a bit behind on functionality. New features come to Power BI first, then Excel later.

[@TheJoshtheboss]

@@MyOnlineTrainingHub I keep getting syntax error for some reason. I thought good data exercise was to scrape past lotto numbers. The suffix is consistent e.g. 2005, 2006. But I can't get to work :(. I am completely new to PowerBI syntax and code. Btw, do you have a donation link? Your online support and presence is really good. www.euro-millions.com/results-history-2004

[@MyOnlineTrainingHub]

Hi Josh, That page doesn't store the data in HTML tables, which is what Excel's Power Query requires in order to scrape the data, so while the URL has parameters, the page content isn't suitable. However, you can try using Power BI and the 'add table using examples' option to locate the data on the page. Thanks for the donation link request. We don't have one, but you can show your appreciation by sharing my channel on your social media.

[@TheJoshtheboss]

@@MyOnlineTrainingHub No problem at all. Will gladly share on LinkedIn. I think I needed to have extra double quote to fix the syntax error i.e. source = Web.BrowserContents("www.euro-millions.com/results-history-"&PageStart&"")

[@ayeshaibraheem4217]

which office version are you using? i am using office 365 and it doesn't give the option of add by example. Also the data i want is not available in tables as well as in web view.

[@MyOnlineTrainingHub]

In this video I'm using Power BI Desktop. The version of Power BI is not tied to your Office version. You may need to download the latest version of Power BI Desktop.

[@gzfraud]

I can't get PQ or BI to extract embedded URL in a webpage table. eg email is embedded in person's name? Any ideas?

[@MyOnlineTrainingHub]

Hard to say. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@adamsteele44]

Wow. Amazing video, thank you!

[@MyOnlineTrainingHub]

Thanks so much, Adam!

[@parvez301]

First comment. thanks for the video

[@MyOnlineTrainingHub]

Winner :-) hope you found it useful, Anwar.

[@rodrigomoro8047]

Dear! Thank you so much for this video. Could you please share with us how can we do the following: I have a web based database that is constantly fed. Today it has 300 itens and 15 itens per page, so: 20 pages. But next week, this database may have 600 itens, and due that, 40 pages. How can I automate the function to identify the total number of the pages each time it acesses the web data source? Thank you!

[@MyOnlineTrainingHub]

Good question, Rodrigo. I see you've also posted this question on our blog post. We'll answer it there as that will be more helpful to others.

[@rodrigomoro8047]

@@MyOnlineTrainingHub thank you so much!

[@iwcik]

@@rodrigomoro8047 Hi Rodrigo, could you please share the link with the reply to your question?

[@louielouie9502]

I'm currently new to this stuff. I see that you might be able to customize queries for specific data scraping tasks. I'm interested in learning ethical data scraping techniques. How would it be possible to create custom scraping software? What computing language would you recommend learning in that case?

[@MyOnlineTrainingHub]

I can't answer that question, Louie. I know nothing about creating custom software.

[@louielouie9502]

@@MyOnlineTrainingHub Thanks Anyway

[@ДенисДементьев-т3о]

Great video! Extremely useful It works in my case, but only for first 19 sheets out of 89. Starting from 20th sheet i get a blank page without any data, however i can see pages from range 20 to 89 via browser. I would appreciate if you show how many pages could be exported in your exact example

[@MyOnlineTrainingHub]

Sounds like the web site is throttling the feed so you can't get the data. Not much you can do about this, other than try splitting the task into multiple queries and run them one at a time.

[@gzfraud]

@@MyOnlineTrainingHub Goods News 1 ..... Solving the throttle problem. When PQ and BI won't work, I use Instant Data Scrapper. It's a free Chrome extension and works 95% of the time. It let's you set a time delay to go to next page. I usually start at 12 seconds then decrease the delay 1 second every 100 pages or so to about 4 or 5 seconds. Most I've ever done it scrapped more than 40,000 pages on a website. It scrapes only when the webpage is active. So if you navigate to a different webpage tab it pauses. To restart scrapping simply make that page active, ie displaying, and click Start Scrapping. To prevent pausing, simply drag the webpage to be stand alone before starting IDS. Goods News 2 ..... it does something that PQ and BI don't do. It extracts embedded URLs. Say email addresses are embedded in people's names. PQ and BI will import the names (as plain text) but I've never figured out how to get them to extract the embedded email address. IDS does extract the embedded URL. Bad News .... IDS doesn't connect to the website so you can "refresh" the query like you can with PQ and BI.

[@stevewilson1544]

Good afternoon, i followed your instructions however instead of producing the results from the subsequent URL pages it just mirrored the results from the first page. Any ideas? Thanks, Steve

[@MyOnlineTrainingHub]

Hi Steve, sounds like the page number isn't being changed with each iteration. Hard to say more without seeing the file.

[@stevewilson1544]

@@MyOnlineTrainingHub thanks for you help. All rectified now.

[@robertcameron4288]

Hey I have a question If I data scrape from a site that is a paid subscription when the subscription ended does the data still save in your excel or will it say #ref

[@MyOnlineTrainingHub]

Hi Robert, the data will stay in the file. Just be sure not to refresh the query because if it tries to get the data again it will presumably return an error and then the original data will be overwritten.

[@robertcameron4288]

@@MyOnlineTrainingHub awesome thank you!!

[@nurezzati9888]

Hi Mynda. Thank you for sharing it. Very useful. However, Is there any way to get the actual URL since the position keeps changing whenever I refresh data in Power BI.

[@MyOnlineTrainingHub]

You can use the actual URL if it's more suitable for your scenario.

[@Iamthatmike]

This is a great tutorial! I was able to connect to one table but my second attempt isn't working. The table's data isn't in a specific order so when I go from page 1, 2, 3 etc. it loads them randomly each time and because I'm not on the actual website it's treating each individual page as a completely new query. The order is random so an individual record might be randomly pulled for the page 1 query then again on page 3 leading to duplicates and records that never populate on the page. Any idea how to work around this issue? Thanks again!

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@Iamthatmike]

@@MyOnlineTrainingHub thank you so much! I posted the topic in the Power Query forum.

[@bali501]

Thank you soooo much! You changed my life this weekend. Been struggling with Excel's limitations for years, and lost countless hours of my life sometimes without even accomplishing my goal. I only discovered the existence of Power Query last night with your video, and you blew my mind. A brilliantly well presented and comprehensive video on it too! It got me partway through my current problem, but now I'm stuck again if you can help? I've created Query1 to gets multiple tables from each webpage with 10 records each , and includes a record ID. But each record has a link to a details page for more info for that record. The record ID is used within the URL string to get those details. Can I create a single query that collects the list of records and uses the ID to also collect the details for each record all in one go? Also, with 30,000 records in total, it takes hours to refresh. However, as the historic records don't change, and have a historic date of filing, is there any way for future updates to only get and append the latest records (with a filing date after the last date of the previous dataset, whilst removing any duplicates, and append it to the list? Finally, it would be great if a timestamp could be added in an additional column to denote the date when that query was run, so that I can easily see which data has been added and when. Is any of this possible with PowerQuery?

[@MyOnlineTrainingHub]

So pleased that my video was helpful! Please post your questions and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@miguelcalejo1770]

Hi Mynda, I'm trying to scrape data from a web page which displays the 10 top items and there's a button "View more" to see more items (till 100) which I open, but powerquery only scrap the to 10 and displays "View More" as a line. Is there a solution for this? Thanks

[@MyOnlineTrainingHub]

Hi Miguel, it sounds like the page is using JavaScript or similar to load more data and that data isn't stored in the web page HTML, which is why Power Query can't see it. I'm not sure there's a workaround for this, sorry.

[@miguelcalejo1770]

@@MyOnlineTrainingHub Thanks Mynda

[@MrOktovan]

What a great tutorial.. I've tried your tutorial and it works! However, when I upload the app to the Power Bi service and I set the automatic refresh schedule. There is a failure notification for automatic refresh for dynamic data. did you also experience this?

[@MyOnlineTrainingHub]

Currently scheduled refreshes for queries where the data source is part of a function aren't supported. You should be able to manually refresh though.

[@abcVegeBreads]

Is it possible to scrape the URL of each individual book? If yes, how can't it be done?

[@sadinenim5360]

Can do a video on how we can scrap the data from after login into portal with our credentials and then fetch the data

[@prameelar1753]

Hi, I ve followed your steps and tried downloading web data from multiple pages, however, I've got data only for 68 pages out of 83. I have no idea where I missed. Could you please help me in this?

[@rakkesh85]

Nicely explained, loved it.

[@MyOnlineTrainingHub]

Thanks so much, Rakesh!