Get a Free Library as you Learn Excel Power Query Tricks (Custom data types, Hyperlink data type!)
HTML-код
- Опубликовано: 26 июн 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
You can create your own free video library with Power Query Custom Data Types. You'll learn about Excel Power Query custom data types and how you can use this to optimize your Excel reports. Excel has a number of linked data types that you can use right away. But, you can also create your own data type from any supported data source by using Power Query. With this new feature, you can create custom data types and you can view the data in an Excel table, display it as an easy-to-read card, or use it in formulas. Power Query doesn't have a hyperlink data type, but you can create hyperlinks using data from Power Query by following the steps I show in the video. This way your Power Query results will be hyperlinked and clickable.
🎓 Get the complete Power Query Course: www.xelplus.com/course/excel-...
We'll use Power Query custom data types to create a free library of all my RUclips tutorials in Excel. I'll also show you a great trick in Power Query to return clickable hyperlinks with friendly names to Excel.
00:00 How to Use Power Query Custom Data Types
02:08 Create a Connection to a File From the Web
03:11 How to Fix Missing Hyperlinks in Power Query
05:34 Create Custom Data Types in Power Query
07:24 How to Expand Cards and Use in Excel Formula
08:35 Convert Links from Power Query to Hyperlinks
9:28 Updating Table after Website File is Updated
10:25 Wrap Up
► LINK to more Power Query tutorials: • Excel Power Query (Get...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #powerquery
Get access to the complete course about Excel Power Query here 👉 www.xelplus.com/course/excel-power-query/
Very good explanation. I succeeded to implement it for opening files on my local drive with a click. thank you.
You always perfect, big like to big teacher
Love you sister you are amazing!!
your bhai ❤️ from FP&A
Another "Wow!" video. THE video of all videos. Lots of hours of work to look at. Thanks once again.
Thanks again, Claire!
Mashallah
You are absolutly doing great service to technology learners.
Your channel is great place to build new much needed skills.
I am sending you a massive thank you.
You make things so clickable Leila. Thanks so much for your Excel videos.
Thank you for this tutorial! It illustrates an excellent way to keep your subscribers up to date with all the high quality videos! I wish more of course providers (Udemy, Datacamp, Coursera, O’reilly, SQLBi) could provide such TOC/metadata information about courses for their users. So the user easily could take notes and keep track of their favorite instruction videos and also implement spaced rrepetition for the more complex material.
Your channel deserves more subs. Highly underappreciated content.
I appreciate that!
Thank you yet again for saving the day!! 💫
I love the date format you chose (d-mmm-yy), because the month is displayed with letters (Jan, Feb, Mar, etc.), rather than digits. This is excellent ! If you want to make it even better (actually, perfect), I would suggest to use "d-mmm-yyy", or "d-mmm-yyyy". It is the only date format I always use. I hope these help.
“AMAZING” Thumbs up again for another masterpiece !!!
Well, I've been trying to understand Power Query for Months and I get the hang on the basics to get me going in just 2 days.
Gracias.
Glad to help!
Wait Lelia wait. I'm on RUclips watching one of your wonderful Excel videos, that links to an Excel file that ... has RUclips videos in it. Are you trying to keep me busy with an infinite loop?! :P
Ok but no really this is awesome! I've dabbled in C++ and that "X DOT Y" syntax makes me think of OOP classes, which I think is exactly what we are doing here!
Fabulous tip/workaround! Thanks, Leila!
You're awesome Leila! Not much I haven't tried in Excel, but then I watch your videos and keep learning new stuff each time. Thanks you so much for continuing to share your incredible knowledge.
You're very welcome!
I kept asking myself:"why not just add a column with hyperlink in Excel and hide the original?" and loved the final result. Thanks.
This is insane-thank you for the tutorial!
Glad you like it!
This is exactly what I've been looking for!
Been trying to figure this out for months. Thank you so much, life-changing
Happy to help!
Never used a custom data type before. Great video as always. Many thanks!
So many powerful features in an easily digestible video. Wonderfully executed. 👏👏
Unbelievable, fantastic, genius! Thank you so much.
You're very welcome, Ron!
Okay, after watching the full video this is one of the coolest features I've seen in a long time and you just helped me uber-enhance a report where I needed details in a card like format. Amazing as always!
Great to hear, Dayve! It really is a cool feature.
Thank you Leila, I ask that question at Udemy, so thank you for the video, exactly what I was looking for 😀
The Full Tutorial List is amazing and so are you. Thank you so much
You're very welcome, Tim!
Thank you! Thank you!
Thanks Lelia. You are both a genius and generous for sharing your knowledge!
@Leila Gharani I suspect that this response is from a scammer
Hi Leila, excellent solution to this! Really impressed! Extremely thankful for this video!
THIS IS EXACTLY WHAT I NEEDED! THANK YOU! YOUR VIDEOS ARE EXTREMELY BENEFICIAL!
Glad it was helpful!
Truly Amazing PQ Trick! This structured data type is surely going to unleash a lot of creativity and innovation for many! Thank you Leila for sharing this! Enjoyed it! 😊👍
My pleasure! It really is a great addition.
Hi Leila. WOW!! This is so awesome! A very cool and clever use of Power Query and the new data types. Even better than that, it makes using your video and blog resources so much easier. Hope it drives more users to your site and courses. You deserve much success and please know of the endless appreciation for all that you offer and publish :)) Thumbs up!!
Thank you so much for your ongoing support, Wayne!
Great learning... You are always a top notch! 🎉
Good, cool trick. Thanks Leila!
Excellent video!
Niiiice! I would have never thought to use custom data types! Thanks, Leila!
They're great!
Excellent !!!
Another mind blowing video to me! Thank you Leila!
You're so welcome!
Awesome 👏🏿
That was awesome! Thanks for the tutorials and the lesson on Power Query
I've spent the last year in prayer for such a library.
😁
@@LeilaGharani Please how can i get hold of the excel sheet, list of free tutorials?
This is awesome Leila! Thanks for always sharing useful tutorials 👍
My pleasure 😊
Leila, I really enjoyed this overview of Power Query. It is so nice to have the example to follow. I am wondering if you have a good example on how to mine data out of outlook. I saw the one video you did but I was looking to see how to mine the body of the email and pull out the information I need into separate columns. I am also taking your full course so perhaps there will be information in there that helps me. If you have a specific tutorial on mining information from the body of am email that would be great.
This is really a great video... Thank you very much!!!!
You're very welcome!
You should a marketer. Brilliant way to get the traffic as well as learning :D
Excellent Tutorial. Many thanks Leila for sharing this with us.
My pleasure!
How cool is that! Thank you for sharing this library and how to use the Data Type feature in Power Query. Great and super helpful as always😄,especially the trick to get the hyperlinks work.
Glad you enjoyed it, Birgit!
thanks for the sharing and coming up with this idea, this will be very useful, outstanding
Cool feature. I really want to know if people can think of other ways to use this.
Gosh this is so cool to play on excel this way.
Mam your style of speaking & voice is very sweet.
I like you so much for your contribution 👍👍👍👍👍 thanks ❤️
Thank you 👍
Excellent!
Every day Leila drops a new video is a good day!! Best excel Sensei on the web!
Wow, thank you!
This is a great Trick!! Thanks!
Glad you liked it, Geert!
So it is not possible without the new data types? Can`t we just type the hyperlink formula in the imported table from PQ?
You can ,.. you can even make it dynamic by using hyperlink formula for the whole column of the table using #colname as an array
@@adham20801 Cool. I will have a go at it 👏
This is a great idea thanks Leila!
Thanks for watching, Chris!
Namaste madam 🙏 you're doing great!
LOL, this video was great just because I did not know about the add prefix feature. All this time I was concatenating with a custom column. :)
Hi Leila! Thanks for helping me set up this video library! Will this video library be updated regularly? I noticed you have new content on your website, but I am not seeing the updates flow through to my video library. Thanks for all you do!
Wow... Great... It's really more than we expected... Thanks Leila
Glad you liked it!
بهترینی
Thanks Leila. : ) : )
Amazing gift....1000 Thanks Leila!! :)
You're most welcome, Antonio!
It is exteemely helpful. You are great teacher.
Thank you! 😃
Obrigado 👏👏👏👏👏👏👏
Oh really interesting 🤩 thank you, you're the best 👏👏👏
Thank you! 😃
Damn! Thank you for being part of my life! (You don't even have to love me, it's free)
😁
heartthrobing teacher on youtube..
Just Speechless! You are awesome Leila
Thank you! 😃
Hi Leila . It's very useful to attach email body and details in an excel sheet for the reference after importing emails to excel table by pq
I like your videos more that anything on internet.
Thank you for this 🙏
You’re welcome, Helen 😊
Really great tips!
Glad you like them!
i like your videos
🌺🌺 the best just like always
Thank you! 😃
Thanks for the awesome video. Is there a way this data type works now across all excel files?
Thank you, shame Excel 2016 does not have this feature. What I like the most is that is the 1st time power query and excel functions can work together on instant refresh in the same table
OMG your voice is so satisfying to hear
Big like 👍
Big thanks 😊
Hello Leila. by far your channel is the best.
I cannot do this on my exercise. Once I load the data on Excel, it doesn't give me the expansion option.
Any advice pls?:)
Excelente
Great tutorial on Data Types. Worth noting, I have hyperlinks turned off by default (Options -> Proofing -> AutoCorrect Options, Internet and network paths with hyperlinks: Unchecked), and was concerned that this wouldn't work without that being enabled. However, the HYPERLINK function does make the link clickable even with the option unchecked. In this case I do want them, but don't have to change the option.
Thanks for yet another great video!
Thanks for sharing, Jerry!
amazing master Leila, shall join your class soon
Always welcome!
You're so beautiful! I enjoy the way you simplify your lessons.
excellent
Leila.... Why do you look so much pretty
Great @Leila ! However can we do the same with the courses we bought on your website, it will be very useful for me to look for a video in an Excel file rather than in the navigator
Hi Leila, I've been watching your videos regularly.
Learnt a lot & implemented them in my work, it's been awesome.
I have been challenged by a requirement at work: How can we append additional data (in a table), to the same source table using power query and load it back to the source table?
Please create a video on the same.
Presume I've not confused you!!😃
You'd have to use VBA for that.
This is a super useful tool!! Thanks. This is a long list, so maybe it is useful to slice by tag/topic. But in some cells you have multiple tags, so how to select them individually by a slicer. Maybe split by delimmitter and put every tag on one row? Then you have many more rows, but I think this will definitively offset the advantage of making good selections.
Other tip: it would be nice if the user could make remarks, for example, "good video, must see again..". But of course, after refresh this own remark should stay, and in the same row!
Greetings Bart
Thanks Bart. The comment is a good idea. Here we can work with self referencing query. Matt Allington has a post one this.
@@LeilaGharani exceleratorbi.com.au/self-referencing-tables-power-query/ Thanks I will try this...
Nice! I just started using power query this year and dropping files into a folder and refreshing the list/pivot table is a real time saver! I'm not sure what I would use the hyperlink for yet, but I'm sure it will come in handy down the road. Thank you for sharing!
My pleasure!
Awesome.. really it helps..
Glad to hear that
@Leila Does PowerBI also have the Custom Data Type function? I can't find it.
Dear Leila, I am a great fan of your videos.
just wanted to let you know that you have not updated the file you use in this video as an example or Power Query capabilities since september last year.
I know that you have plenty of things and this file is certainly not your priority.
But is a good reference (for me) to pick up the pointers of your videos and quickly share it with some colleagues I work with.
Thanks for your contributions and knowledge sharing
What a great video!
I have been "dipping my toe" into this feature in power query but now I really want to go all in and see what all this feature can do!
Thank you for the great video Leila!
Go for it, Eric!
Thanks to the tutorial. Now, I want to create the same list of my tutorials save in my google drive folder by folder (tutorial mp4 files). Do you have the same guidance yet for that need?
👍👍
I award you an Olympic gold medal for this tutorial.
Thank you kindly :)
Structured Column in not activated and also ”create data type ” button is missing. Tried to write the m code. But Table.CombineColumnsToRecord is not recognised. What to do?