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
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.
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.
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 UA-camrs just steal and claim content as their own
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.
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!
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.
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.
Like information feeding document not information breeding like make new documents? Like copying deleting paste share like that? Do those deleted files return if use special program? Emcase you wanted them back? I'll see you later on until next post? In next months?
@@brandonhowarth2402 for sure I knew that way for easily importing data from web, I wonder for the fact, that I have to give up thumb and subscribe if I like this video. That's Amazing, never knew about this option on UA-cam.
40 seconds of beginning I have watched again because my full attention was on you and your smile that's why I had to restart video 😉, I've seen your all videos almost but you are looking very beautiful in this video after watching this video I played again it's first 40 seconds around 10 times, I wish to meet with you once In my entire life. I've inspired from you too much and learnt many things in Excel. Have a big thanks to you for such videos after seeing that now I'm a good trainer of Excel in india but not like you but wish to be. =May_you_live_long as Long and Long In Excel VBA form 😉😉😉 Love you ❤️ and your videos
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.
Nice! As always, you're full of brilliance. Question: Can you share with us some tips on how we can select a specific date such as MTD Report from the examples you've shown us?
Beautifully presented. Is there a technique for dealing with multiple pages within a page-ie those buttons you click '2', '3', 'Next'-so that Refresh will pull them all in? My current need has a variable number of pages, some days 10, some days 15…
Wonderful tip. I was able to make it work with the proposed example but never with any of the sites I am interested in. Either the login/pwd is not recognised either (I think) it remains blocked on the cookies agreement pop up...
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 opened the Emog (Window Key + the period) in the area where you pasted the umbrella Equipped and selected all the different symbols I wanted. Then Cut and pasted them in the other cells. Thanls for the fun weather program.
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.
very useful to know that excel has such functions but what about to print the weather forecast from the webpage directly and give to the hotel guest? :)
Especially useful information and I use this method every time I want to pull the table from a webpage. THANK YOU!!. Can you please tell me what to do if access to the webpage that contains the data/table is password protected? I could not find the place where I could put my credentials.
Very nice & thank you! I think Microsoft should hire you to announce new Excel features, instead of their lengthy & boring Release Notes that nobody reads!
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.
Grab the file I used in the video from here 👉 pages.xelplus.com/web-query-file
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.
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.
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.
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
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.
I am a regular watcher of technology videos but the you explain things is just awesome thank you so much your initiative
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many UA-camrs just steal and claim content as their own
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
THANKS FOR ALL YOUR WONDERFUL VIDEOS! yes, I am shouting from the rooftops about your wonderful teaching skills!
Power Query is awesome. Thanks Leila..
It sure is! Thank you for your support Matt.
That was literally phenomenal... Excel is really a power tool for entrepreneurs such as myself.
👍
Mind blowing...! Saving money to buy your black belt course and add value to my life.
Thumbs up for the last trick.
It was amazing to use emojis in power query.
Thanks
Glad you like my little umbrella :)
Thank you, Leila, I love the way you present things in such a simple and clear way!
This is amazing! Exactly what I've been wanting to do with large budget and actual datasets. 🎉
Amazing....
I was given such task before which I couldn't... You just made my day
Thank you Leila
Thank you, Leila, for making things easy.
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
Maam ur presentation was awesome 👌 really a useful one
Thanks so much for your videos, Leila. They have been so timely and have enabled me to make so much progress with my project.
I really appreciate your efforts
It's my pleasure
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...
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!
More amazing Excel majic described by Leila. What a wonderful world.
Great video! Since older versions of Excel don’t feature ‘Data Types’, I’m importing and refreshing currency information in the same manner.
👍
Wow. This is a very sophisticated reporting format. Thanks for teaching these kind of useful things...
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!
Thank you for explaining this so well!
You're very welcome!
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!
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.
Amazing, Thankyou for sharing your knowledge and explaining so well.
Wow, this is super useful!
Man this is new lvl of excel.. Never know it can be easy as this
It really is :)
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.
Great video. Thanks. Plz make video about how to do data entry feom web to excel for beginners
Really appreciate your teachings.
brilliantly explained will definitely be enrolling for the course thanks
Very useful...Thanks a lot.
Hi Leila. Great tips! Love the umbrella :)) Thanks for sharing. Thumbs up!!
Haha, it's raining a lot here 😁
I love the way you talk
Great content!Thanks so much!
Beautifully explained, Excellent
Predictive Excel so easy with YOU 🌟
Extremely useful 👍👍👍
I started to get used on your vedios, thanks thanks thanks a lot.
You are really great inspire for all content creators.
Very enjoyable and user friendly
You are realy great. You saved my day and job. Thanks a lot.
Excellent video! Thank you!
Thank for the video, so interested.
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
Thank you Leila, nice and easy way to explain!!
You're very welcome 😊
Best excel guru on youtube :)
😘
That was amazing, didn't even know about that....
Like information feeding document not information breeding like make new documents? Like copying deleting paste share like that? Do those deleted files return if use special program? Emcase you wanted them back? I'll see you later on until next post? In next months?
@@brandonhowarth2402 for sure I knew that way for easily importing data from web, I wonder for the fact, that I have to give up thumb and subscribe if I like this video. That's Amazing, never knew about this option on UA-cam.
so so so so so so thank you ♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥♥ you are the best...
Most welcome 😊
Get & Transform is a Beast!
It sure is :)
Absolutely amazing. Never knew excel had this feature.
NOPE and i am thrilled to discover this. What a Power Tool at hand.
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?
Very useful ! First class tutorial
40 seconds of beginning I have watched again because my full attention was on you and your smile that's why I had to restart video 😉, I've seen your all videos almost but you are looking very beautiful in this video after watching this video I played again it's first 40 seconds around 10 times, I wish to meet with you once In my entire life. I've inspired from you too much and learnt many things in Excel. Have a big thanks to you for such videos after seeing that now I'm a good trainer of Excel in india but not like you but wish to be. =May_you_live_long as Long and Long
In Excel VBA form 😉😉😉
Love you ❤️ and your videos
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.
Nice! As always, you're full of brilliance.
Question: Can you share with us some tips on how we can select a specific date such as MTD Report from the examples you've shown us?
Beautifully presented.
Is there a technique for dealing with multiple pages within a page-ie those buttons you click '2', '3', 'Next'-so that Refresh will pull them all in?
My current need has a variable number of pages, some days 10, some days 15…
Leila, you are amazing! Thank you for open to us this precious jewel
Wonderful tip. I was able to make it work with the proposed example but never with any of the sites I am interested in. Either the login/pwd is not recognised either (I think) it remains blocked on the cookies agreement pop up...
Excellent tutorial. Many thanks.
Way cool, must add to my work flow. Cool way to grab Monthly data from report.
Am always amazed with your videos
Thank you Leila
Leila you always amazed us with your versatile brain 🧠. You beauty with the brain.
Now that was 'Transformational' !! Great tutorial
Asalam'alaykum! as always helpful
Great video. Quick and clean. Thanks!
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.
I opened the Emog (Window Key + the period) in the area where you pasted the umbrella Equipped and selected all the different symbols I wanted. Then Cut and pasted them in the other cells. Thanls for the fun weather program.
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.
Thank You Ma'am
Thanks for this video
Thank you our queen of Excel. Quite interesting.
Glad you think so!
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!
very useful to know that excel has such functions but what about to print the weather forecast from the webpage directly and give to the hotel guest? :)
Wow! I didn’t think this was possible in Excel. Thank you.
Thank you very much
Leila, I admire you! You are an outstanding & beautiful person. Very impressive!
I had no idea this was possible. Thanks!
Leila, eres brillante!!!!!! Muchas gracias.
Con mucho gusto Carlos :)
you are really genius EXCEL world no.1
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?
good concept professor love it
Thanks! 😃
Nice clear and concise explanation. I real like your videos.
This is super. Thanks a lot Leila Gharani. I keep checking your video tutorial.
Especially useful information and I use this method every time I want to pull the table from a webpage. THANK YOU!!. Can you please tell me what to do if access to the webpage that contains the data/table is password protected? I could not find the place where I could put my credentials.
YOU are great
Thanks alot
Very nice & thank you! I think Microsoft should hire you to announce new Excel features, instead of their lengthy & boring Release Notes that nobody reads!
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.
Thanks Leila very useful. Today i learned emoji's with Windows + . = 😊. Every day is a school day
Thank You... Learnt a lot
Always great practical knowledge for learning.
Thank for alot this type contents Gharani u r fantastic tutor