As someone from Arizona, I can say we definitely love chocolate here. Also, don't let kids sell boxes of chocolate, as they tend to eat more than they sell.

@chandoo_

Жыл бұрын

Oh well, that explains the anomalies. 🤣

@tahirajabeen7175

Ай бұрын

​@@chandoo_sir, can we insert pictures in excel from folder in PC, using merge column feature in power query....

I don't know how far will @Chandoo go to educate others, every time new things to learn. Be Awesome

@chandoo_

Жыл бұрын

It is my mission to help one million people become awesome at their work. When I get there, I am just going to 10x it and make it my mission again.

Great Idea! Thanks Chandoo for sharing this! 👍

@chandoo_

Жыл бұрын

My pleasure 😊

Thank you, sir, for keeping us up to date with time and skill development. I'm becoming awesome. Once again Thanks.

@chandoo_

Жыл бұрын

You are welcome Azhar.

That was awesome, thanks Chandoo!

@chandoo_

Жыл бұрын

Glad you liked it!

Nice presentation and great!!

I love dark chocolate as much as I love your videos❤️

Great video!! Super cool tricks!!✌ For this particular case we can also use Data Types. For example: In H2 we write USA, with H2 selected, go to: Data tab, Data Types, Geography, select first result from side pane In I2: =H2.Subdivisions In J2: =H2.Subdivisions.Population There are discrepancies though. To visualize the differences, a formula: (Xlookup syntax technique when we deal with data types) =LET(s,pop[State or territory],cp,pop[Census population],cp-XLOOKUP(s,H2.Subdivisions.Name,H2.Subdivisions.Population))

@chandoo_

Жыл бұрын

Great suggestion EL. While data types are a good option, PQ is more versatile and universal.

@Excelambda

Жыл бұрын

@@chandoo_ PQ rules!!✌🙏

Awesome Literally ❤️

Thank you!

Hello, merci pour la vidéo ! Il existe une méthode pour les sites qui requiert une connexion ?

Thank you for your video. May I ask if you can do video that automate data entry to chrome browser? Excel data to chrome browers like that without Third Party App? Thank you so much for your videos

Can you please show videos about educational data like mark analysis or teacher analysis in power bi..

For this specific example, you dont actually need to use scrapping. I forget the function name but you can classify cells, one common classification is geography. Type in the state, a drop down box appears to indicate the state. New york state, usa for example. Then you can use other cells to reference the geographical cell to list characteristics like population, total area, area by land, area by water, all differnt types of discriptors pertaining to new york state. So generate all 50 states, create the columns of criteria you want, do all the referencing for the to row. Then just drop down to fill the other 49

Hi Chandoo, Thanks for the informative video once again. I have one question that how do big organization deal with their data for data visualization or daily analysis. Do they use any specific tools to scrap the data from their database. They are autogenerated or one person has to do it every day manually?

@chandoo_

Жыл бұрын

Normally big organizations have dedicated data teams which in-turn have ETL teams that do the data extraction, cleanup, automation and storage processes. They are also called Data Engineers. While these folks take care of 70% of data needs, the other 30% will fall on to the individuals/ departments for their specific needs. This is why learning a bit of Power Query, SQL, data clean-up techniques can help you in many aspects of professional life.

AWESOME! I tried to a web that cannot be scraped via requests library in Python and I can get the table from its page!

Great vide! Is it possible to scrap if the number of pages is not fixed? And number of pages is available by link

Very helpful video Chandoo G

Sir could you please guide me to create excel auto update table about bestselling books rating on specific category say personal finance. 🙏🏾

Hi Chandoo, thanks for so great video and sharing your knowledge. I was wondering about your webpage though. I was wondering if you close the registering option or have you left aside that part of your business. I love the way you explain Excel and Data Analysis so thank you very much for doing it possible. Cheers

@chandoo_

Жыл бұрын

Thanks Danilo. Feel free to email me about the courses so I can make an exception for you.

@dlo009

Жыл бұрын

Dear @@chandoo_. Thanks for your quick response, I really appreciate you are very considerate. At the moment I am in an awkward position, that is because I was recently a brain surgery. Thank God it went well But one of the things i have to life is that I quite slow in learning, that is because the lack of practice, high demand of energy needed to focus in a subject and my current financial situation. That makes me believe that this is not the appropriate time to participate in one of your courses, that I will have to wait a little more. My idea in subscribing to your website is to remain in contact with you and try to follow your steps. Also think that because the lack of a way to see a proper layout or organization in the videos people post in you tube, maybe registering in your website would give me the chance to see the videos in a more organized way. I do have a bachelor in CS and lot's of excel experience but because my sickness I am literary starting from 0 (not winning, I won't be the first nor the last to be in this kind of situation). But through my career it has been the projects in which I have used excel as UI and to create reports for the middle and upper management or creating interfaces with MSSQL, ADO-CSV, MYSQL, ACCESS, ... that I had the most fun and the ones in which I can see a quick problem modeling and solution response for the client. I would love to know where to find your email, as you suggested. I have been fan of your work since a long time, years BTW but for me things have been bumpy, so I really don't remember since when I have been following you in FB. Thanks again for everything I will be watching your videos, I just found you " How I made $100k as Excel Freelancer" , which is mostly the path I want to follow. Cheers.

Thank You Sir. one request if you can create video on Excel Addins and its uses and how can we automate using that addins

@chandoo_

Жыл бұрын

You are welcome TD. What specific add-ins you are talking about? I rarely use them for my work.

Hi Chandoo while i tried to scrape data from the website using power query the website first leads to Disclaimer (accept or reject), please guide how to skip it?

Hi could you help in how to connect with oracle 10g DB?

I need help!! I have one application which we normally used for filling information of working employees & that application generate UNIQUE ID. 2 Step :- I have to audit those case but at the same time wanna check multiple UNIQUE ID cases with different Name via excel using filtering data. So tell me how should i audit multiple data for UNIQUE ID.!!

How come when I put in the URL from other websites the tables don't pop up but they do for wikipedia? Any suggestions?

Hi Chandoo .... GREAT video. QUESTION .... I scrape 10,00+ webpages so this will really help. BUT if a URL is embedded in text on a webpage, PQ or BI won't extract the URL eg email address is embedded in the person's name. I've searched and can't find it. Any ideas?

Can web scraping be done in macbook?

Anyone know of a detailed video to scrape sofascore's historical score data? I currently have to scroll manually to scrape it. TIA.

Sir i didn't get dataset of previous video "pivot table"

Amazing

I tried to extract data from my Orf Intranet portal. I am not getting data table, Can you please help in this

I honestly would have just copied and pasted the table straight from the site bc I'd be concerned that the data wouldn't import correctly. I thought that you were going to point that out as being one of the issues. Is the technology behind it so good that the tables come through just as they are on the screen? What are the benefits of using this over a normal copy/paste?

@noedits5543

Жыл бұрын

in this method, we can periodically pull the data eg every minute or even every 10 seconds!!!

Sir how did you create that awesome chart at 6:11

mine is appearing as "DataSource.Error: The request was aborted: Could not create SSL/TLS secure channel."...What problem?

Hi, Chandoo thanks for the incredible video, I just had a problem when I click the Data tab>Get Data> From Web.. what I saw is different from yours. Mine shows "NEW WEB QUERY and a bunch of script errors with the question box "Do you want to continue running scripts on this page? Yes/No " while yours is "From WEB" and with choices Basic or Advanced. What's the problem with my Excel don't you think? Thank you

@chandoo_

10 ай бұрын

That depends on the webpage you are connecting to.

@lanieticman1807

10 ай бұрын

@@chandoo_ Thanks so much for your response. How can I make it the same like you my web seems msn.. don't know how to change it

cool! bro...

Hello sir I have a serious questions That is from which playlist i have to start and which step by step playlist I have to follow? From ur all playlist

@chandoo_

Жыл бұрын

Hi Musafir, Thanks for your question. I suggest using the FREE Excel course playlist. As my videos cover a wide range of topics and have been made over 10+ years of uploads, it is impossible to find a thread that connects them all. If you want a step-by-step course without any distractions or ads, just join my Excel School program. Visit chandoo.org/wp/excel-school-program/

@SpiritedTravellerr

Жыл бұрын

@@chandoo_ thank you sir

In my previous laptop I use to press CTRL +shift +page up (arrow keys) to shift between sheets, in New laptop its CTRL +fn +page up (arrow keys) Can I change it to old method in new laptop?

@chandoo_

Жыл бұрын

Hmm.. not sure Amar. I suggest checking with your laptop provider.

Can we doing that some sites like indian railways websites where data can be accessed after log in

@chandoo_

8 ай бұрын

Probably with the brigthdata tool, but not with Power Query as of now.

Hi, I have small doubt, I want to get to data analytics and ultimately after couple of years want to be a data scientist. Is this possible or am I planning wrong!! please help me out provide some information thank you

@chandoo_

Жыл бұрын

You can certainly do that. I suggest talking to data scientists in your network to get an idea of the kind of work they do and start picking up the skills slowly.

What version of excel is this?

How to extract table data into excel from web with multiple pages ...please make a video on it ...thank u in advance 😁

@chandoo_

Жыл бұрын

Good idea. Do you have any examples of such websites? Often multi-page sites block PQ based access in my experience.

@JJ_TheGreat

Жыл бұрын

@@chandoo_ How about writing in multiple URLs in an Excel table and creating a function in PQ to scrape them!

@chandoo_

Жыл бұрын

Yes. we can do that. If the URL has a pagenum parameter, we can also parameterize it with PQ functions.

Hi, I have a query...can we paste a small table of 3 rowa & 3 column in a cell in excel

@chandoo_

Жыл бұрын

You can. Just double click on the cell and paste.

I want to import data from a website site that says "your browser is not up to date". I use Office 2016 and I can import data from other websites. Even if I change the web browser option, I still can't import data and receive the same error messega. How can I fix the problem with the website I mentioned above. Thanks and regards.

@chandoo_

11 ай бұрын

I am not sure how to fix the problem here. Try posting it in Microsoft forums.

Sir Please Tell how to Hide Row Border like you always do. Please

@vishalbhati912

Жыл бұрын

Go in view tab and uncheck the gridlines.

sir, i won't be able your bright data cause business id.

Can you help me to scrape Skyscanner prices into google sheets.

If the data will change for example author of the page will add more information on that webpage my question is :- our data will be updated automatically inside Excel or no ?

@chandoo_

11 ай бұрын

It should be as long as the underlying structure of the webpage is maintained (ie they used the same CSS class or table ID or something that you used in PQ).

@RAVIKUMAR-nx3od

11 ай бұрын

Thanks for the quick response 😊

Hi, how to scrape data that is not inside a table?

how to overcome the restriction of 100 rows extraction only?

How to extract report from Oracle in Excel ?

I just have one question, whenever there is a change in data on the website, does the data in excel also updates?

@larriemayodi2085

Жыл бұрын

yes, it should

@kasanibhanuvenkat9339

Жыл бұрын

@Chandoo

@JohnPaulIghorue

Жыл бұрын

yes, cos the url was copied and used and the data will automatically refresh and be included, as long as the URL remains valid.

Sorry for asking. This Excel is for Mac or Windows? I couldn't find the location of "Import from Web"

@chandoo_

Жыл бұрын

I am using Excel for windows. I don't think Mac Excel's PQ has Import from web yet.

@ducnguyenhong3173

Жыл бұрын

@@chandoo_ thanks for ur answer so much. On google sheet just type "import html" right?

Whenever I change the name of the table in PowerQuery when the data changes and I go to Refresh an error appears stating that the table with the name I created was not found in the source. 😐

@chandoo_

Жыл бұрын

Refer to the error message. Power Query doesn't like name changes for underlying tables or columns. You can redesign your data clean-up steps so that they don't depend on the names. But you must learn a bit more M language to make it smooth. See this video for more on Power Query - kzread.info/dash/bejne/gp16o8OYms3Hdaw.html

How to scrap from log in needed website

I just started following your channel to level up my excel skills. But I am not sure where to begin with, as there are huge number of videos on your channel. Can you share some tips or videos from your channel?

@chandoo_

Жыл бұрын

Welcome aboard Shab. I suggest watching the FREE Excel course videos first. kzread.info/dash/bejne/eGuVssOzpbaegbw.html Then go for the videos in Excel for Data Analysis playlist. kzread.info/dash/bejne/qGajsLmjkZqzYqg.html If you need a step-by-step course, I suggest going for Excel School program - chandoo.org/wp/excel-school-program/

@shab1467

Жыл бұрын

@@chandoo_ Thank you for your response. I will definitely check out the courses and videos you recommended

@JohnPaulIghorue

Жыл бұрын

@@chandoo_ Thanks so much! I was wondering why I was not receiving alerts of your updates, then saw that in all my learning I failed to click the subscriv=be button. Please forgive me. Now done!!! ..and thanks for this learning pathway!

Hi bro , can we get ? How to remove duplicate words in single cell .

@ajpw7695

Жыл бұрын

Not sure of a quick way to do this but you could: 1. Use text to column with space set as the delimiter to create a row of cells, each containing one word 2. Cut and transpose paste the cells 3. Remove duplicates 4. Concatenate the cells using TEXTJOIN with space as the delimiter

👏🏻👏🏻👏🏻

Bright Data site is not working

How you're knowing exactly what iam expecting each time @chandoo... I need share point folder name and creation date from others one drive

What about sites that require user name and passwords?

@JJ_TheGreat

Жыл бұрын

That's just another type of authorization - on that screen he showed.

how to add "box sold in 2021?"

Great, but using the geography data type would be a strong contender in a real-life situation.

@chandoo_

Жыл бұрын

That is a good alternative too. As data types are less widely available than Power Query, I chose the later option. Plus, it aligns with the sponsor for the video too. 😀

@TSSC

Жыл бұрын

@@chandoo_ Certainly. Just wanted to contribute with that option for viewers that had forgotten about (or were unaware of) the geography data type.

@TSSC

Жыл бұрын

@@chandoo_ And congratulations to reaching 250k subscribers. There’s a good reason for the number going up.

I don't understand where boxes sold came

@chandoo_

Жыл бұрын

We already have that data. It can be any internal data.

Wooo 999+1=1000 likes🤘🤘

LOL have ya seen the pricing $1000 - $2000 a month OMG! lol Yer where do i sign up quick quick NOT! Get Real mate.

@chandoo_

Жыл бұрын

Just because you haven't found their service of value doesn't mean others won't. Hundreds of companies and businesses use their product all the time.

@BrightData

Жыл бұрын

Our pricing packages reflect the amount of data a customer needs to collect on a monthly basis. If you are looking to collect smaller amounts of data we can support you as well. Set up a call with a sales rep who will gladly help you find the package that is best for you and your business.

I'm sick of seeing the exact same example on every one of these Excel web scraping videos. It's ALWAYS "Here's how to scrape this table from Wikipedia into your worksheet", it's never how to scrape a single data point from a webpage such as a dynamically changing number or name etc that can be identified with an isolated XPath...

@chandoo_

Жыл бұрын

Why not search a bit more or better still, build one yourself? How are you sick of free help and guidance offered by someone you barely know? Here are few other web scraping videos - kzread.info/dash/bejne/iIaA2sWHj9qToso.html kzread.info/dash/bejne/hIltpo98qa7ddbQ.html

@Pooty_With_A_Fat_Booty

3 ай бұрын

I noticed the same thing. It's always from Wikipedia or other source easy to extract. 😂