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.
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
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.
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!)
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!
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.
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.
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
@@MyOnlineTrainingHub thanks for the quick reply. I just joined the forum. Your forum is clean and organized. Looking forward to learning more. Thanks.
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!
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?
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.
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!
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?
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.
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.
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.
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?
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.
Dear Somthing that i would say iis that t is important to mention is that UsingPower Query in excel give a lots of problem since consumes a lot of Ram memory , i had a really bad experiance with Pq tool in excel ,even though i reduced to the minimun as i could the steps and the volumen of imported data.. so thta is something that MS got to improve for sure.
Hi Rene, occasionally you will come up against limits like you describe, but I would say this is the exception rather than the norm. Sometimes there are settings and alternate approaches to your query's structure that can alleviate performance issues like you describe. Sometimes it's an issue with your PC's specifications e.g. not enough RAM, not 64-bit Excel etc.
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"?
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
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.
@@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.
Thank you, Madam, for this useful info! But I need to know "How can I scrape pages which have infinite scrolling using power bi?". looking forward to your suggestion!
Hi Hari, if the pages are loading the data on the fly and the data isn't in an html table or visible on the page, then I'm not aware of a way to do that, sorry.
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.
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)?
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.
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?
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.
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?
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.
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.
Not sure what you mean by multiple tabs. If the URL follows a pattern then you can use the technique described in this video. If not, then each URL will have to be a separate query, which you can then append if required.
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!
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 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?
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
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?
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.
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!
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 - Hi, on this point only- i have a query when I tried doing this for 2000 pages, data is coming only for few pages and rest is showing null after the Invoke function where as data is there on the web for that page.
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?
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
Thank so much for your all playlist videos tutorial..awesome...please add post tutorial for making network or system monitoring with excel..many thks..:-)
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.😃👍
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 Good morning Professor, I have emailed you an excel file containing a question and comment on the question, thanks a lot for the help.
Hi! Nice video. I just want to know if there is a way to extract the paragraph that is inside some products using power query also. I was trying but power only extract information in the outside, that is visible, is there a code or a formula to do this? Thanks!
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!!
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
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.
:-) so pleased it was helpful, Robert!
@@MyOnlineTrainingHub can i download epaper into pdf without coding?
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!
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
Thank you! So pleased this video was helpful.
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.
So pleased you found it helpful!
I watched this video on this teachers day, and I believe you are one of the best teacher could help me on web scraping... 🤗
Wow, thank you!
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.
My pleasure, Abdul!
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.
Pleased I could help!
I slightly adjusted this to scrape data from a folder full of PDF files. Excellent thanks!
Glad it helped!
To think I was doing this manually 🤦🏽♂️. Thank you, this is a huge time saver!
Great to hear, Geoffrey!
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!)
:-) Glad you liked it, Mark!
This is an amazing way of working with web pages. I have seen people write lengthy macros and Python code for this.
Yes, Power Query is super easy to use. I wish more people knew of it's powers ;-)
Wow...Easily used this tutorial to query printer settings from every Zebra printer on my LAN. Very helpful!
Awesome to hear, David!
A bit beyond me at this point Mynda, Power Query is on my "to learn" list. Well presented.
Thanks, Dave! Power Query is amazing...I'm confident you'll think so too :-)
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!
Glad it helped!
spot on. I was only able to do the first page. This gives me the ability to do an entire site.
I wasn't even aware that M/Power Query can be used to such extent. Thank you for the great insight!
Glad you enjoyed it, James!
Thanks Mynda, there is no way that I would not like this video. It's awesome.
Thanks so much, Awesh! And thanks for sharing it on LinkedIn :-)
Great video mam!!! I was doing this before python then saved into csv then importing to PBI. Now I can do with PBI directly 👏👏👏
Wow, that's fantastic to hear :-)
Thanks a lot for this tutorial! I could get mutiple api call in single query, best solution ever!
Glad it’ll be useful!
Awesome use of M for us tiptoeing into the M Script!
Glad you liked it!
Clear Voice, Beautifully Explained Super-woman.
Thank you so much 🙂
I have never tried this, but I frequently convert data from the csv file to the html Datatable, Thanks Mynda.
Hope you can make use of it, Reda!
Wonderful job ! So clear and perfectly explained, thank you so much !
Glad it was helpful!
You are the legend! Helped me to solve this greyed out "change data source "button
Great to hear, Vincas!
This is simply awesome, now I have to practice this technique.
Enjoy!
Wooww thank you so much, took me months to find this function. I will try it in a more complicated webpage. thank you
Glad you can make use of it! 😊
Merci Beaucoup madame. You made my work very easier
I'm so glad!
Brilliantly framed and well communicated. Thank you again Mynda.
Thanks so much, Michael!
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.
Don't be sad that the challenges have gone...there are plenty of new challenges awaiting; M code, DAX, dynamic array functions :-)
Thank you very much. I'm from Turkey. Have a nice day.
Thank you! You too!
Amazing how it is easy to scrape web pages. Thanks for this excellent tutorial.
Glad you like it, Marcel!
I really appreciate your tutorial! monysaver! Most data extraction tools are costly.
Glad it was helpful!
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.
Brilliant! Many thanks, Mynda.
Cheers, Ian!
Wow, this is clever and exactly what I needed. My mind is blown !!
Awesome. Glad I could help 😊
Wonderfull tutorial! that was exactly what I Looking for. I was duplicating datasources for each week to scrap some web data. Thanks a lot!
So pleased it helped, Fabio!
YOU ARE THE BEST!! Saved me so much work!
So pleased I could help 😊
Great info, easy to understand. TYVM! I'd love to learn how to do all of this in Google Sheets. Power Query sounds cool!
Glad you liked it, Stephen! Sheets doesn't have Power Query.
Great video thanks this makes web scraping a lot easier. Thank you.
Great to hear!
thank a lot, that was really really useful. you solve my very big problem. 🙏🙏🙏🙏🙏🙏
Awesome to hear! 😊
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.
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
@@MyOnlineTrainingHub thanks for the quick reply. I just joined the forum. Your forum is clean and organized. Looking forward to learning more. Thanks.
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!
Excellent tutorial, super easy to follow. That’s brilliant 👍
Glad it was helpful! 🙏
Great Video... Thanks for the efforts and sharing it. this will be very useful for many tasks...
Great to hear!
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?
Thank you so much for this video. what if we have "read more" instead of page numbers ?
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.
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!
Beautiful. It's solved my actual problem. Thx. :)
Great to hear!
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?
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.
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
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.
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.
You got it 👍
Well Done Mynda
All Your Videos Are Useful
Cheers, Shakira!
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?
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.
@@MyOnlineTrainingHub Thank you very much for your prompt response.
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)
Only direct query datasets can refresh real time, however, you can schedule refreshes at set intervals.
As always, an excellent tutorial
Glad you liked it!
Web scrapping, oh I love it 😊
Great to hear :-)
I don´t know you, but I love you. thanks!
Glad it was helpful!
Marvelous ! You make it so easier, Thanks a lot
Thank you! Glad to hear that!
Super cool video, thanks Mynda
Cheers, Chris!
Wow amazing what you can do! Thanks a lot mynda I’m always learning from you!
So pleased to hear that, Maria!
Dear Somthing that i would say iis that t is important to mention is that UsingPower Query in excel give a lots of problem since consumes a lot of Ram memory , i had a really bad experiance with Pq tool in excel ,even though i reduced to the minimun as i could the steps and the volumen of imported data.. so thta is something that MS got to improve for sure.
Hi Rene, occasionally you will come up against limits like you describe, but I would say this is the exception rather than the norm. Sometimes there are settings and alternate approaches to your query's structure that can alleviate performance issues like you describe. Sometimes it's an issue with your PC's specifications e.g. not enough RAM, not 64-bit Excel etc.
Wow. Amazing video, thank you!
Thanks so much, Adam!
Hi Mynda!Great Tutorial,Just Learnt Something New So I Can Have More Fun With POWER BI..Thank You :)
Great to hear you found it useful, Darryl!
Mind blown. This is awesome. Thank you.
Glad you liked it 😊
Exactly what I was looking for, thanks ! great video
Glad you found it helpful 😊
Wow you're amazing! Can't believe this information is free! Thank you so much!
You're most welcome, Carlton!
Can do a video on how we can scrap the data from after login into portal with our credentials and then fetch the data
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"?
Never heard of that before, Peter. It sounds like you're trying to use Power Query online because there's reference to a browser.
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
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.
@@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.
Thank you, Madam, for this useful info! But I need to know "How can I scrape pages which have infinite scrolling using power bi?". looking forward to your suggestion!
Hi Hari, if the pages are loading the data on the fly and the data isn't in an html table or visible on the page, then I'm not aware of a way to do that, sorry.
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.
Glad it was helpful. Unfortunately, if the site's URL doesn't change, then you can't scrape the data with Power Query.
@@MyOnlineTrainingHub Understood. Could you recommend any other options that may be helpful? Thank you in advance.
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.
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)?
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.
@@MyOnlineTrainingHub Thanks for your prompt reply!
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?
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.
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?
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.
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.
You can use the actual URL if it's more suitable for your scenario.
This is great - can you show how to do this with multiple parameters though - cant find anything understandable on the web!!
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thanks - will do :)
Hi thanks for your video, may I know how to extract data from web pages in multiple tabs (same layout)?
Not sure what you mean by multiple tabs. If the URL follows a pattern then you can use the technique described in this video. If not, then each URL will have to be a separate query, which you can then append if required.
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!
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.
@@MyOnlineTrainingHub thank you so much!
@@rodrigomoro8047 Hi Rodrigo, could you please share the link with the reply to your question?
Can you get around captchas for more advanced stuff?
Not captchas, AFAIK.
Thank a lotu it was terrific, I'am from México.
Glad you enjoyed it, Arturo!
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?
I still need to watch this video a few times. Our entire organization dont know this i bet
Glad it's helpful!
The website is not anymore updated
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?
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.
@@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?
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
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?
I can't answer that question, Louie. I know nothing about creating custom software.
@@MyOnlineTrainingHub Thanks Anyway
Great example
Thank you!
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.
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!
MyOnlineTrainingHub thank you for explaining this, I’ll have another go toddy 😊
Is it possible to scrape the URL of each individual book? If yes, how can't it be done?
Thanks for this great video. What if you want to loop through dates?
You can use the same technique to loop through dates.
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?
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.
Thank you for the video, very informative...
How long does it take to scrape the data from the 21000 pages?
I never let it run to find out how long it takes. I'd expect the website would think it was a bot and throttle the query anyway.
@@MyOnlineTrainingHub - Hi, on this point only- i have a query when I tried doing this for 2000 pages, data is coming only for few pages and rest is showing null after the Invoke function where as data is there on the web for that page.
Scraping with PowerBI, hopefully it'll be fully enabled in Excel!
Fingers crossed, Doug!
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?
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
First comment. thanks for the video
Winner :-) hope you found it useful, Anwar.
Thank so much for your all playlist videos tutorial..awesome...please add post tutorial for making network or system monitoring with excel..many thks..:-)
My pleasure :-) I don't have any data I can use on network or system monitoring, sorry.
@@MyOnlineTrainingHub tysm for your feedback, nope with many thank..
Great video and so clear with the explanation! My researching will be much easier now!
So pleased it was helpful 😊
Thus is AWESOME!!
Glad you found it helpful!
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.😃👍
Thanks, Mohamed! I'm presuming the value in A1 is a number, in which case you can use this formula:
=TEXT(A1,"##.00,,\M")
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.😃👍
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@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.
Hi! Nice video. I just want to know if there is a way to extract the paragraph that is inside some products using power query also. I was trying but power only extract information in the outside, that is visible, is there a code or a formula to do this? Thanks!
Power Query can get data that's in a table. If it's outside the table, then you can try querying the 'document' rather than one of the tables.
Nicely explained, loved it.
Thanks so much, Rakesh!
Thanks in a million.
My pleasure 😊
works great, thanks!
Great to hear!
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!!
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