Best part of the video is when you said; "it is just part of Excel"! That seems to be our main job as Excel Trainers, to convince Excel users that Power Query is the greatest Excel Invention since the PivotTable : )
Very well said Mike! The name power query does scare people away just like pivot tables. If pivot tables were called one-click-reports I think more people would be using it... that‘s probably why Microsoft renamed power query to „get and transform“ when they put it in the data tab.
I find it's really inflexible like all external lookups, even to other excel files. I had lots and lots of trouble editing an existing query, for example. Ended up creating a whole new query from scratch, instead.
@@borstenpinsel You should probably check out some videos on it. It's very straight forward to use. More so with the intellisense that comes in the latest versions. When just using 2016, I do find I have to google the correct formula syntax due to lack of assistance, but that's probably a good way to learn too. Make sure you read the formula bar each time you create a step to get an understanding of the formula syntax and how you might tweak it for your queries.
i have been trying to learn different skills over the past few months. But all where left incomplete half way in the journey. Today started with your videos. Have been practicing them since morning. Very effective. Concise, to the point and more importantly they address real time work challenges. Thank you very much
If the external data source requires a password you can add that in "Data Source Settings" - check out this page: support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9 (If you don't see the option in the Ribbon, go to Power Query, File, Options and Settings, Data Source Settings - Edit Permissions - Edit - Basic - The input username and password. )
Was wondering if you could please help me. I have Excel for Mac and there appears to be no DATA>GET DATA >FROM WEB. Mac version instead has GET EXTERNAL DATA and then RUN WEB QUERY. How do I get this done on Mac. Thanks.
Hi Leila. My name is Julio and I live in Brazil. I'm studying a lot about Excel. I need to give my career a boost and I believe that Excel will help me a lot. I love your channel and the way you teach. Congratulations.
While carefully reviewing all your videos (after discovering your amazing channel), I realize about this functionality. So,... I only can say to you, Thank You Very Much. Based on it I got a bunch of ideas that will make me progress in my complex reporting. I often say that if EXCEL would suddenly disappear, the whole world would have a deep impact at all levels. In the same way, I want to say that your video courses, your teaching skills, and advice make the world get a bit better. Faithfully Yours.
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many RUclipsrs just steal and claim content as their own
I am learning not only Excel, but how to organize a video about a concept or concrete theme, and try to explain in a short time. Really impressive Leila, you have a special touch.
Hi Leila! While trying to load weather data of New York from website into Power Query, the Navigation panel opens and it only fetches something called 'Document' table and not the 'Table 0' table having 10 days' worth of weather info. I tried in Excel-2016 and Excel-2019. Didn't work. Please help.
Hey Robert! I am facing a similar issue as well as I am unable to load the data under the 'show more' category on the website. Do let me know if you were able to resolve it.
I can't believe what I just saw and am only halfway through this video. I was looking to automate getting some info from the web and I thought of writing some script but why should I go through all that stress when Excel makes this look way easier? Lol Thank you so much Leila!
Hi Liela, Thanks for this excellent video, it makes it simple to understand how to get data form a web page. I'm sure it will save me a lot of time. Regards Enrique PD: Your English is really clear and your pronunciation makes this video understandable for everybody.
Is this feature "from web" no longer available in Excel? I have the latest version yet no one else i can see is saying the option isn't there for them. All i have in the Data tab is "From HTML", "From text" and "New database query". Each one only allows for files on my computer to be chosen. Help.
Hi Leila is there a way to make an Excel file to “Track Packages” from FedEx, UPS, etc. and see the real time status of multiple packages upon refresh? I am thinking of a shared file where anyone from the team can write the tracking number of the package we are waiting for, then the whole team can view the status at once in a single Excel table. Thanks in advance for your help!
Using VBA with Selenium can accomplish this by manipulating specific elements on a webpage. If only one unique URL per package is used, try using the "advanced" radio button after selecting "From Web".
Thanks so much for this video. I'm using office 365... in the excel file -> get external data, I couldn't find the bottom "from web" but "from HTML", which links to a different manipulation.. Do you have any advice to let "from web" appear?
Great videos! On the weather qry. You do not need to copy and paste the emoji from the spreadsheet. You can Window logo & '.' directly in the query formula... saving you a couple steps.
Great Video. Do you have any videos of this same process for MS 365 Excel for Mac ver 16.x? There appears to be different menu system - e.g., Data > Get External Data > From HTML - The MS 365 Mac version does not appear to be able to perform the same functions as you show in your video. Thank you in advance.
@@LeilaGharani thanks for the quick note back - any suggestions of other resources to figure out how to do something similar (e.g., bring data in from web sites) for Mac version?
do you have a video that describes how to import data from a website where there are dropdown inputs or input fields that the user needs to input before the web data is generated? Thank you!
Use libraries like Selenium to interact with dropdowns and input fields. By automating these actions, you can submit the forms and scrape the generated data.
THANKYOU SOO MUCH ! I am new in here ;-) i will always like and shared your videos 👍🏻 you're really saved my life because my crazy teacher in my university ...
When we give site and press 'go',an error box saying unable to get property 'clientwidth' or undefined or null reference is shown. Pls help. How to fix this?
Thanks for your wonderful and simple videos you make I have two questions: 1) After you finished all your amendments in the Power query and before loading the data. Their where four options to load data, two of them where (Table) & (Create connection only), what is the difference between both? 2) Is there a way to load data that is changing continuously second by second such as stock prices, in other words creating live connection?
Ahmed, Load to table and create connection will both require you to refresh the data either manually (selecting Data/Refresh All) or via VBA if you like (using a timer or whatever to update every X seconds or minutes/hours). You can also select to refresh on opening of the file. A connection will literally just create a connection to the data, which you can then use to create a pivot table for example. It saves loading the data directly into the workbook and I'd always recommend this unless you really need to see all data or are you are happy with just the basic table layout. Pivots can be better formatted, or pivot charts can offer different dimension. Even better is to load to the Data Model so that you can create various additional measures in the Power pivot Window, which can then be used in your pivot tables or in dashboards.
Can we change url dynamically like for 2021 and 2022 there are tow diff links. When i open table it replace with latest year data not old year data. What is the tips.
Hello Liela, thanks, i have learn so much from you and i can never be more grateful. please i have been using power query web import for awhile until i stumble into this site which excel does not display/recognise its table.
Leila, thanks for the video. Sometimes websites are rather complicated. Sometimes we can see table in web view, but web query doesn't recognise that table. How can we help query to recognise it? Sometimes websites contains only links to tables and web query doesn't recognise tabels as well. Do you have any course or video about such cases? Or may be you have detailed course about getting data from the web?
Hey Leila, Thank you for information 🙂 While importing data it was shoing an error to Update the browser but browser is updated. Could you please help me with that?
What if I will extract on the website and I need to complete the form there first before I could extract is there an automation in power query to do that
The met office weather data in the online table shifts, so in an hour the first column is lost and the web query gives an error cannot find column X. How to solve that 🤔
Hi Leila, I opened my workbook a different day, and I’m trying to refresh the “Petroleum Spot Prices” data query, to get the latest prices, but it returns an “[expression Error] The column of the table wasn’t found” error. What do you think I should do? Sorry I'm dragging you back to a video you made two years ago, would really appreciate your help. Thanks.
This is very useful. What I need to know is how to import individual data sets from a webpage page rather than the full table. The data I want is coming from various web pages and i want all my information on one spreadsheet but can not seem to work out how to do this.
Hi Leila, If I paste a pivot table using the paste link function in an excel file and share it another colleague, can he retrieve the data from the paste link data? I want to make sure that he can't because the data is confidential. Is there a code that he could use to retrieve the data?
There are webpages where I can just copy/paste tables into Excel but following this data export procedure that same info is not available to download directly to the spreadsheet. Any idea what's happening?
I just followed the procedure but could not get any data.... I had this message under WEB VIEW tab "table highlighting is disabled because this page uses internet explorer compatibility mode" please advise
tried EXACTLY what you did in the video and doesn't import the whole weather forecast table like yours(only a few lines) do we have to do anything in Excel first ?
Hi Leila, thank you for the video. I am wondering if you have any video on hot to connect a webpage with login and then pass parameter to get the data.
Hi, I am encountering some issues while importing data from a web API URL to Excel and sharing the file with clients. Specifically, I have the following concerns: The client needs to establish a connection to fetch data for the first time. During the connection setup, the API URL is being exposed.
Ola Leila. Adoro os seus videos. Estou seguindo e posso lhe dizer que ja aprendi bastante e tenho usado alguns dos ensinamentos aqui no meu trabalho. Bom trabalho. Abracos.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
Grab the file I used in the video from here 👉 pages.xelplus.com/web-query-file
Best part of the video is when you said; "it is just part of Excel"! That seems to be our main job as Excel Trainers, to convince Excel users that Power Query is the greatest Excel Invention since the PivotTable : )
Very well said Mike! The name power query does scare people away just like pivot tables. If pivot tables were called one-click-reports I think more people would be using it... that‘s probably why Microsoft renamed power query to
„get and transform“ when they put it in the data tab.
I find it's really inflexible like all external lookups, even to other excel files. I had lots and lots of trouble editing an existing query, for example. Ended up creating a whole new query from scratch, instead.
@@borstenpinsel You should probably check out some videos on it. It's very straight forward to use. More so with the intellisense that comes in the latest versions. When just using 2016, I do find I have to google the correct formula syntax due to lack of assistance, but that's probably a good way to learn too. Make sure you read the formula bar each time you create a step to get an understanding of the formula syntax and how you might tweak it for your queries.
@@borstenpinsel Power Query is super flexible. I don't think there's anything that it can't do.
Better than pivot tables
i have been trying to learn different skills over the past few months. But all where left incomplete half way in the journey. Today started with your videos. Have been practicing them since morning. Very effective. Concise, to the point and more importantly they address real time work challenges. Thank you very much
My pleasure.
what i like about Leila is, she has a sense of art beside Excel technics.
She has what I want
... Excel knaaawledge
...and she's easy on the eye.
If the external data source requires a password you can add that in "Data Source Settings" - check out this page: support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9 (If you don't see the option in the Ribbon, go to Power Query, File, Options and Settings, Data Source Settings - Edit Permissions - Edit - Basic - The input username and password. )
Was wondering if you could please help me. I have Excel for Mac and there appears to be no DATA>GET DATA >FROM WEB. Mac version instead has GET EXTERNAL DATA and then RUN WEB QUERY. How do I get this done on Mac. Thanks.
Leila, you’re the total package - beautiful, brilliant, and an Excel nerd! :) 😍😍😍 Marry me?
Hi,
Is It possible to refresh based based on a cell change ? For ex if i fill cell E4 the refresh is done automatically? Thanks
I have 1000000 lakhs same data in 4 sheet how can i marge in one pivot
Thank you so much! I will try...
Leila is a combination of an artistic and techy person.. also, the best thing about her is she gives credit to whom she got the idea.
Hi Leila. My name is Julio and I live in Brazil. I'm studying a lot about Excel. I need to give my career a boost and I believe that Excel will help me a lot.
I love your channel and the way you teach. Congratulations.
While carefully reviewing all your videos (after discovering your amazing channel), I realize about this functionality. So,... I only can say to you, Thank You Very Much. Based on it I got a bunch of ideas that will make me progress in my complex reporting.
I often say that if EXCEL would suddenly disappear, the whole world would have a deep impact at all levels.
In the same way, I want to say that your video courses, your teaching skills, and advice make the world get a bit better.
Faithfully Yours.
Thank you! I'm happy the tutorials are helpful.
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many RUclipsrs just steal and claim content as their own
I am learning not only Excel, but how to organize a video about a concept or concrete theme, and try to explain in a short time. Really impressive Leila, you have a special touch.
I'm glad to hear that Francisco!
@@LeilaGharani This is a dream
Thanks!
Thank you, Robert!
I am a regular watcher of technology videos but the you explain things is just awesome thank you so much your initiative
THANKS FOR ALL YOUR WONDERFUL VIDEOS! yes, I am shouting from the rooftops about your wonderful teaching skills!
That was literally phenomenal... Excel is really a power tool for entrepreneurs such as myself.
👍
Amazing....
I was given such task before which I couldn't... You just made my day
Thank you Leila
Hi Leila!
While trying to load weather data of New York from website into Power Query, the Navigation panel opens and it only fetches something called 'Document' table and not the 'Table 0' table having 10 days' worth of weather info.
I tried in Excel-2016 and Excel-2019. Didn't work. Please help.
Same problem I'm facing too 😔
Thank you, Leila, I love the way you present things in such a simple and clear way!
Thanks!
Thank you!
Mind blowing...! Saving money to buy your black belt course and add value to my life.
This is amazing! Exactly what I've been wanting to do with large budget and actual datasets. 🎉
What about if one wants to pull from a Secured Web that one needs to log into?
Hey Robert! I am facing a similar issue as well as I am unable to load the data under the 'show more' category on the website. Do let me know if you were able to resolve it.
@@ShantanuSaxena666 I think in that case you need a connector, which is something that stores the access information to a data source.
I can't believe what I just saw and am only halfway through this video. I was looking to automate getting some info from the web and I thought of writing some script but why should I go through all that stress when Excel makes this look way easier? Lol Thank you so much Leila!
Glad it was helpful!
Thanks so much for your videos, Leila. They have been so timely and have enabled me to make so much progress with my project.
Hi Liela,
Thanks for this excellent video, it makes it simple to understand how to get data form a web page.
I'm sure it will save me a lot of time.
Regards
Enrique
PD: Your English is really clear and your pronunciation makes this video understandable for everybody.
Power Query is awesome. Thanks Leila..
It sure is! Thank you for your support Matt.
Best excel guru on youtube :)
😘
More amazing Excel majic described by Leila. What a wonderful world.
I love this video! I'm beginning to build an Access Database, and I think a light bulb just turned on for me. Thanks so much!
Great to hear, Bryan!
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
Man this is new lvl of excel.. Never know it can be easy as this
It really is :)
Thank you, Leila, for making things easy.
Wow. This is a very sophisticated reporting format. Thanks for teaching these kind of useful things...
an amazing content and yet you can explain to us in a very2 simple way, what an amazing teaher you are, thanks a lot leila
I appreciate that!
Is this feature "from web" no longer available in Excel? I have the latest version yet no one else i can see is saying the option isn't there for them. All i have in the Data tab is "From HTML", "From text" and "New database query". Each one only allows for files on my computer to be chosen. Help.
Hi Leila. Great tips! Love the umbrella :)) Thanks for sharing. Thumbs up!!
Haha, it's raining a lot here 😁
what if there is no table? do you have a video on how to extract say a prce from a webiste but it doesn't have a table?
Ditto that question !
That means you're foorked
Same I would like to know
copy the contents > select required cells > Paste Special > as text
**Paste special will be under Home > Paste
Hope it helps.
Need information how to insert text (a paragraph not table data) from web to Excel
How we do this if we need to login to a webpage first?
Thank you for your time
Did you ever find out how to do this?
Hi Leila is there a way to make an Excel file to “Track Packages” from FedEx, UPS, etc. and see the real time status of multiple packages upon refresh? I am thinking of a shared file where anyone from the team can write the tracking number of the package we are waiting for, then the whole team can view the status at once in a single Excel table.
Thanks in advance for your help!
Using VBA with Selenium can accomplish this by manipulating specific elements on a webpage. If only one unique URL per package is used, try using the "advanced" radio button after selecting "From Web".
Thumbs up for the last trick.
It was amazing to use emojis in power query.
Thanks
Glad you like my little umbrella :)
Amazing, Thankyou for sharing your knowledge and explaining so well.
Thanks so much for this video. I'm using office 365... in the excel file -> get external data, I couldn't find the bottom "from web" but "from HTML", which links to a different manipulation.. Do you have any advice to let "from web" appear?
You are realy great. You saved my day and job. Thanks a lot.
Thank you our queen of Excel. Quite interesting.
Glad you think so!
brilliantly explained will definitely be enrolling for the course thanks
Great video! Since older versions of Excel don’t feature ‘Data Types’, I’m importing and refreshing currency information in the same manner.
👍
Predictive Excel so easy with YOU 🌟
Thank you for explaining this so well!
You're very welcome!
Maam ur presentation was awesome 👌 really a useful one
One of my most important aspirations in 2019 is that you have a course in business intelligence tools that are as fat and complete as the VBA
Great videos!
On the weather qry. You do not need to copy and paste the emoji from the spreadsheet. You can Window logo & '.' directly in the query formula... saving you a couple steps.
Thank you for the suggestion.
Really appreciate your teachings.
I've tried this but some websites doesn't show the table on excel. How can I import a table from those websites that doesn't show the table in excel?
Thank you Leila, nice and easy way to explain!!
You're very welcome 😊
Great Video. Do you have any videos of this same process for MS 365 Excel for Mac ver 16.x? There appears to be different menu system - e.g., Data > Get External Data > From HTML - The MS 365 Mac version does not appear to be able to perform the same functions as you show in your video. Thank you in advance.
Sorry, Office for Mac does not have all the same features.
@@LeilaGharani thanks for the quick note back - any suggestions of other resources to figure out how to do something similar (e.g., bring data in from web sites) for Mac version?
@@fitforprep9441 Glad you asked this previously, and no, I haven't found a way. Someone let me know if they update this for Mac? - data analyst
my query only shows document but no table while edit in Navigation... it should show table. please suggest
Beautifully explained, Excellent
I really appreciate your efforts
It's my pleasure
thanks Leila , could you please explain how to download all pages data from web ?
according to the website im trying to take the data from, it only has the document with the null nothing else, is there a way around it ?
do you have a video that describes how to import data from a website where there are dropdown inputs or input fields that the user needs to input before the web data is generated? Thank you!
Use libraries like Selenium to interact with dropdowns and input fields. By automating these actions, you can submit the forms and scrape the generated data.
Hello Leila, what if i have data the in multiple pages ? Can power query read multiple pages ?
THANKYOU SOO MUCH ! I am new in here ;-) i will always like and shared your videos 👍🏻 you're really saved my life because my crazy teacher in my university ...
I'm glad to hear that :)
I started to get used on your vedios, thanks thanks thanks a lot.
You are really great inspire for all content creators.
how can you even put a dislike button to ANY of her videos? Great content as ALWAYS!
😘
Great video. Quick and clean. Thanks!
When we give site and press 'go',an error box saying unable to get property 'clientwidth' or undefined or null reference is shown. Pls help. How to fix this?
Thanks for your wonderful and simple videos you make
I have two questions:
1) After you finished all your amendments in the Power query and before loading the data. Their where four options to load data, two of them where (Table) & (Create connection only), what is the difference between both?
2) Is there a way to load data that is changing continuously second by second such as stock prices, in other words creating live connection?
good question
Ahmed, Load to table and create connection will both require you to refresh the data either manually (selecting Data/Refresh All) or via VBA if you like (using a timer or whatever to update every X seconds or minutes/hours). You can also select to refresh on opening of the file. A connection will literally just create a connection to the data, which you can then use to create a pivot table for example. It saves loading the data directly into the workbook and I'd always recommend this unless you really need to see all data or are you are happy with just the basic table layout. Pivots can be better formatted, or pivot charts can offer different dimension. Even better is to load to the Data Model so that you can create various additional measures in the Power pivot Window, which can then be used in your pivot tables or in dashboards.
Can we change url dynamically like for 2021 and 2022 there are tow diff links. When i open table it replace with latest year data not old year data. What is the tips.
Excel 365 doesn't have the option to import data from the web.
Is there any solution?
Hello Liela, thanks, i have learn so much from you and i can never be more grateful. please i have been using power query web import for awhile until i stumble into this site which excel does not display/recognise its table.
Am always amazed with your videos
Thank you Leila
Leila, thanks for the video. Sometimes websites are rather complicated. Sometimes we can see table in web view, but web query doesn't recognise that table. How can we help query to recognise it? Sometimes websites contains only links to tables and web query doesn't recognise tabels as well. Do you have any course or video about such cases? Or may be you have detailed course about getting data from the web?
I have the same problem. Tables are not being recognized.
Hey Leila,
Thank you for information 🙂
While importing data it was shoing an error to Update the browser but browser is updated.
Could you please help me with that?
Will you plz explain the way to extract data from multiple web pages of the same website?
Get & Transform is a Beast!
It sure is :)
I am Using Microsoft Excel 2016 I tried like you but it cannot show query editor like you.
Please suggest me a Solution
Hi Leila, facing issue here, when i am trying to add conditional step after adding emoji, its showing as null value?
What if I will extract on the website and I need to complete the form there first before I could extract is there an automation in power query to do that
Nice clear and concise explanation. I real like your videos.
The met office weather data in the online table shifts, so in an hour the first column is lost and the web query gives an error cannot find column X. How to solve that 🤔
Hi Leila, I opened my workbook a different day, and I’m trying to refresh the “Petroleum Spot Prices” data query, to get the latest prices, but it returns an “[expression Error] The column of the table wasn’t found” error. What do you think I should do?
Sorry I'm dragging you back to a video you made two years ago, would really appreciate your help. Thanks.
Very useful ! First class tutorial
Great video. Thanks. Plz make video about how to do data entry feom web to excel for beginners
Excellent tutorial. Many thanks.
this is Brilliant!! I had no clue that so much power was avail in Excel for web interfacing.
*THANK YOU* *THANK YOU* *(NEW SUB)*
Thanks for the sub!
This is very useful. What I need to know is how to import individual data sets from a webpage page rather than the full table. The data I want is coming from various web pages and i want all my information on one spreadsheet but can not seem to work out how to do this.
Extremely useful 👍👍👍
I love the way you talk
Hi Leila, If I paste a pivot table using the paste link function in an excel file and share it another colleague, can he retrieve the data from the paste link data? I want to make sure that he can't because the data is confidential. Is there a code that he could use to retrieve the data?
Leila, you are amazing! Thank you for open to us this precious jewel
it does not let data open in power query. error shows as headers name not found
There are webpages where I can just copy/paste tables into Excel but following this data export procedure that same info is not available to download directly to the spreadsheet. Any idea what's happening?
I just followed the procedure but could not get any data.... I had this message under WEB VIEW tab "table highlighting is disabled because this page uses internet explorer compatibility mode" please advise
Hi Leila 🙏, I want to save data collected from web to a data base continuously. Could you please suggest me a simple way to do it?
tried EXACTLY what you did in the video and doesn't import the whole weather forecast table like yours(only a few lines) do we have to do anything in Excel first ?
Hi Leila, thank you for the video. I am wondering if you have any video on hot to connect a webpage with login and then pass parameter to get the data.
Hi,
I am encountering some issues while importing data from a web API URL to Excel and sharing the file with clients. Specifically, I have the following concerns:
The client needs to establish a connection to fetch data for the first time.
During the connection setup, the API URL is being exposed.
is there a way to have date/ filter to check historical data?
Ola Leila. Adoro os seus videos. Estou seguindo e posso lhe dizer que ja aprendi bastante e tenho usado alguns dos ensinamentos aqui no meu trabalho. Bom trabalho. Abracos.
I'm glad the videos are helpful Fernando.
Not working as query showing that 'the table highlighting is disabled because this page uses internet explorers compatibility mode'.