APQ01: Advanced Power Query Video #1

APQ01: Advanced Power Query Video #1 - Custom Functions

Рет қаралды 39,048

Күн бұрын

Chapters:
------------------------------------------------------------------------------------------
Intro: 0:00
1- Convert a Query to a Custom Function 1:49
2- Invoke Custom Functions inside Power Query Editor 9:48
3- Invoke Custom Functions form Excel Window 13:10
4- Invoke Custom Function in a Custom Column 14:20
To watch PQB "Power Query Basics" Series:
• سلسلة تحليل البيانات ع...
To download excel files:
drive.google.com/drive/folder...
To subscribe:
/ @aaaexcel
To follow on Facebook:
/ excelispower
To follow on LinkedIn:
/ amratef

Пікірлер: 112

@rajrawat287 3 ай бұрын

After 5 Videos of Other Tutors finally this video helped me idepth knowledge of Custom function

@AAAExcel 3 ай бұрын

So happy that you found it useful 👍

@Youmna-kx8do 3 жыл бұрын

Appreciate it so much, amazing advanced video in a very simple way

@AAAExcel 3 жыл бұрын

Thank you so much 😊

@emrc9777 3 жыл бұрын

Great video! Thanks…. So many videos cover the basics but we need more like this that really get into the good stuff of what PQ can do

@AAAExcel 3 жыл бұрын

Thank you for your comment, will try my best, wish me luck

@jeslovermusic578 3 жыл бұрын

That's very very true. We need more advanced stuff

@AAAExcel 2 жыл бұрын

Will do my best 😊

@JoeMuc2008 2 жыл бұрын

This video provides incredible value! Thank you Sir, this is exactly what I needed, and really helps me get things forward. I did not expect this from a free source. Keep up the great work! 👍‍‍ ⭐⭐⭐⭐⭐

@AAAExcel 2 жыл бұрын

Thank you so much for your nice words 🙏🙏

@suki9860 2 жыл бұрын

Excellent, smooth, perfectly paced learning experience. Thanks very much, Amr!

@AAAExcel 2 жыл бұрын

Thank you for the nice comment 🙏🙏

@smartshan4u 2 жыл бұрын

Very well explained. Thanks for sharing this!

@AAAExcel 2 жыл бұрын

Thank you for your comment 🙏

@stephanweaver1960 2 жыл бұрын

Excellent , thanks for moving along quickly. .

@AAAExcel 2 жыл бұрын

My pleasure

@ranjitnarayanan6748 3 жыл бұрын

Marvellous Video! Thank you

@AAAExcel 3 жыл бұрын

Most welcome 🙏

@jawadahmadehssan6251 Жыл бұрын

I have started watching the APQ series and it is simply amazing. Thank you for putting it together.

@AAAExcel Жыл бұрын

My please Jawad, thank you for your comment 🤩

@davida.taylor8444 5 ай бұрын

That's cool. So a custom function is similar to subroutine in VBA that you can reuse. Very cool and useful.

@AAAExcel 5 ай бұрын

Exactly

@ecvargas5272 2 жыл бұрын

Fantastic tutorial. I finally understand custom functions.

@AAAExcel 2 жыл бұрын

Excellent, happy to hear that it was useful for you 👍👍

@williamarthur4801 2 жыл бұрын

This was my first try of creating and using a a custom function , so thank you for clear instructions without over complicating things for us beginners.

@AAAExcel 2 жыл бұрын

Thank you so much William for your comment 🙏

@SSi-nq3rt 4 ай бұрын

finally understood 🎉. Thank you very much. This is exactly what i needed!!! I am so happy, you made my day😊

@AAAExcel 4 ай бұрын

Happy that you found it useful 👍

@dougbrown3607 3 жыл бұрын

Thank you. Your video was very well done, very clear, very organized, without any unnecessary content. Wish more were like you.

@AAAExcel 2 жыл бұрын

Thank you so much, happy you like it 😊

@jazzista1967 2 жыл бұрын

Great video and a great refresher on how to create customs functions.

@AAAExcel 2 жыл бұрын

Thank you, happy that you like it

@jazzista1967 2 жыл бұрын

@@AAAExcel .. you do a great job on showing these customs functions...Do yo u have a blog where i can post a question on how to correctly sort a chart of accounts in power query ? I just subscribed to your channel.

@AAAExcel 2 жыл бұрын

sorry i do not have one

@A.Shabana8 Ай бұрын

Bro you are amazing mash'allah 👏🏽

@AAAExcel 23 күн бұрын

thank you so much

@Hurmali1 17 күн бұрын

Next to enjoying your explanation about this, I think the music in beginning of each session is so much lifting the spirit. To me, the function seems like doing part of the function in macro excel? Salam dari Flores, Indonesia

@mohammed1981 3 жыл бұрын

brilliant as usual, wish you the best

@AAAExcel 3 жыл бұрын

Thank you, same to you dear

@eduardomunoz2764 Жыл бұрын

Great video, thanks

@AAAExcel Жыл бұрын

My pleasure

@alexrosen8762 2 жыл бұрын

Great explanation and a very useful tutorial

@AAAExcel 2 жыл бұрын

Thank you, happy you like it ☺️

@arnoldschwartz114 3 жыл бұрын

Sir, you are an excellent teacher. The explanation of custom functions is clear, easy to follow, and very useful. I encourage you to please add more advanced topic to your playlist - I subscribed and look forward to future videos. Thank you very much.

@AAAExcel 3 жыл бұрын

Thank you so much for your comment, i will do best to meet the expectations:)

@ExcelInstructor 2 жыл бұрын

@@AAAExcel great video for basic function lesson. However for 2 months now ive been working on some big project @ work, and now i can truly appriciate dynamic solutions. In thos example, each Table has to be loaded into PQ for the function to work. However if you put those tables (even as normal ranges) into new workbook as separete sheets and then import them into new workbook. ull only have to expand the source data workbook. Better yet if each table would be its own workbook - now itll be easy toa dd new file into a folder and simply import it all, then use that function

@AAAExcel 2 жыл бұрын

Nice idea .. thank you

@ExcelInstructor 2 жыл бұрын

@@AAAExcel ur Welcome, also i commented on other video. Please check it since that comment may shorten ur slutions by few lines.

@PhamNhatAnhTB Жыл бұрын

your lectures are very good please make more videos. Wishing you a lot of health and success

@thanhnguyentien2709 2 жыл бұрын

Thank you very much, very useful lesson. hope you will have many new videos. Thank you and good health

@AAAExcel 2 жыл бұрын

Thank you so much, happy you find it useful. Will do my best 😀

@ginesc 2 жыл бұрын

Just in "two words" EXcel" lent !!!! Wouwwww

@AAAExcel 2 жыл бұрын

Thank you so much 😊

@Ndofi 2 жыл бұрын

Super Class. Thanks

@AAAExcel 2 жыл бұрын

Thank you 😊

@kennethstephani692 2 жыл бұрын

Great video!

@AAAExcel 2 жыл бұрын

Thanks!

@muhammadshahzadafzal6229 2 жыл бұрын

Cool👍. You are a great teacher. A lot of love and respect from Pakistan💖

@AAAExcel 2 жыл бұрын

Thank you 🙏, cheers from Egypt brother

@sr5726 5 ай бұрын

Thank you so much

@AAAExcel 5 ай бұрын

Most welcome

@ano1976 2 жыл бұрын

That's so amazing, I should know your chanel earlier. Thanks for your sharings

@AAAExcel 2 жыл бұрын

Thank you for your comment 😊

@kishorpanara4191 3 жыл бұрын

Excellent and I like it

@AAAExcel 3 жыл бұрын

My pleasure

@HaiderAli-vt8wn 2 жыл бұрын

Just great

@AAAExcel 2 жыл бұрын

Just thank you 🙏

@SSi-nq3rt 4 ай бұрын

Danke!

@AAAExcel 4 ай бұрын

Thank you , much appreciated 🙏

@khalidmajeed2886 2 жыл бұрын

knowledge financer......................great sir

@AAAExcel 2 жыл бұрын

Thank you 🙏

@altafhussainbakhwani3847 2 жыл бұрын

Wonderful presentation & explanation. It enriches my knowledge. Many videos are seen regarding PQ but your explanation is amazing. I hope, hear from you soon new video related to PQ. I also request you to please make videos related to Power Pivot and DAX.

@AAAExcel 2 жыл бұрын

Thank you so much for your comment, I already have posted couple of videos about Power Pivot and DAX kzfaq.info/get/bejne/jZyghr2nm9O6m4E.html kzfaq.info/get/bejne/Y9ShjdupqteqaHU.html kzfaq.info/get/bejne/lbKebJRzmsDZpoU.html kzfaq.info/get/bejne/bJuAn7OW2NqWkac.html

@ryner.k4963 2 жыл бұрын

Subscribed, I'm very interested to learn more about Advanced PQ from ur KZfaq channel. Very much appreciated.

@AAAExcel 2 жыл бұрын

Thank you so much 😊

@SantiagoMolero 3 жыл бұрын

Hello. I was waiting for your videos in english. Thank you very much.

@AAAExcel 3 жыл бұрын

Most welcome, hope you like it

@Bhavik_Khatri 2 жыл бұрын

Wow, bonus feature, learn how to create custom function plus loop through multiple named ranges. #This makes work so easy and can't believe it's legit too#

@AAAExcel 2 жыл бұрын

Happy that you like it, thank you 🙏

@shoaibrehman9988 2 жыл бұрын

Superb video about custom function a simplest way, full of info pack. Great work please continue Power Query video. Thanks a lot.

@AAAExcel 2 жыл бұрын

Thank you 🙏

@shoaibrehman9988 2 жыл бұрын

@@AAAExcel Could you please do some videos of advance power query in English. You are perfect

@AAAExcel 2 жыл бұрын

Thank you so much, I have 2 more videos, and more to come soon

@itgyantricks7218 2 жыл бұрын

Excellent

@AAAExcel 2 жыл бұрын

Thank you 🙏

@malchicken 3 жыл бұрын

Very nice video, thank you ^^. What is the difference between using a “parameter” vs just writing in the parameter into the function? Is there a way I can have 3 arguments to a function but make them *all* optional so if the function can clean any 1 of 3 different *types* of reports but I don’t _have to_ have every type of report represented. Is it a Try function that can do do this? Try x Try y Try z otherwise “no report”?

@AAAExcel 3 жыл бұрын

Thank you for your comment. Parameter is different from function, you can use parameter as an input to a query or a function . I didn’t try the idea of having optional inputs to a function and I don’t think it’s doable

@pardawala_Bhiwandi 2 жыл бұрын

Wow

@AAAExcel 2 жыл бұрын

😊😊

@Altaf_Alam 2 ай бұрын

Thank u so much sir 🙏😊 Sir I have one question to ask you... As of now I am learning advance power query from your fruitful videos so my question is Which one should I learn first VBA or Power Query M code?

@AAAExcel 2 ай бұрын

thank you so much Altaf, actually I am not a big fan of VBA, so my answer will be automatically Power Query M-Code :)

@smartshan4u 2 жыл бұрын

Hi , I have one query. Is custom function can be also be used when source of the data in power query is " From folder" . In other words, irrespective of any source from which data is coming into power query editor, we can use always custom function...Am I right?

@AAAExcel 2 жыл бұрын

Absolutely right 👍

@hungmai9254 Жыл бұрын

this is exactly thing what i need. But can you make eng-sub for this video and others? My listening is not good, so i have to see subtitles to understand what you say. Thank you so much!!!

@AAAExcel Жыл бұрын

Hi , eng sub you can activate yourself. It’s automatically generated by KZfaq

@hungmai9254 Жыл бұрын

@@AAAExcel I see youtube automatically creates a sub in Dutch, not English.

@AAAExcel Жыл бұрын

You can manually change it

@bernardarhin3313 2 жыл бұрын

Thank you sir for this very helpful video. I was able to successfully invoke the custom function for the Jan_OPEX table and load data to table however, I have failed to achieve the same result for all three data sets. I keep getting the message: "[Expression.Error] The Column 'Account Number' of the table was not found" when I try to load to a table. Also, my query has the message : "Downlaod did not complete". Kindly assist.

@AAAExcel 2 жыл бұрын

Thank you for your comment and sorry for the late echo, if you are talking about the section of invoking the custom function in a custom column, so my guess hear that you missed the "filter" step in query "Clean_Data", please have a look again starting minute 14:20. hope this will help

@darwisyb 2 жыл бұрын

Thank you. How to show up color of formulae in advance editor?

@AAAExcel 2 жыл бұрын

I think based on excel version you are using

@darwisyb 2 жыл бұрын

@@AAAExcel i use excel 2016. btw, i can invoke with custom function but can not add as custom column. any link for working with 1 column?

@AAAExcel 2 жыл бұрын

I think you need 2019 or office 365 For working with custom columns, it should be working with 2016

@darwisyb 2 жыл бұрын

@@AAAExcel thanks for your reply. I mean, i can't add custom column with function i made. kzfaq.info/get/bejne/hbKEdsdhtMCYf30.html

@mahamohan1 2 жыл бұрын

In the February and March data, there is an additional column - "Account Name" In this there are totally 9 columns where as in the Jan data, there are only 8 columns. Account name is missing. Hence, when we apply the custom function formula. we don't get the desired result.

@AAAExcel 2 жыл бұрын

Thank you for your comment, i double checked all files shared, all contain nine columns, could you please double check or elaborate further, cheers

@AAAExcel 2 жыл бұрын

If you are talking about the section of invoking the custom function in a custom column, so my guess hear that you missed the "filter" step in query "Clean_Data", please have a look again starting minute 14:20. Hope this will help

@thababozulu Ай бұрын

Hi Sir. This is the error l get “Expression.Error: The column "Account number " of the table wasn't found. Please can you advise why lam getting this error? Thank you

@thababozulu Ай бұрын

Expression.Error: The column Account number" " of the table wasn't found. It is this error l am getting l tried to solve it the entire day without any success please help

@thababozulu Ай бұрын

@20:27 yours does not give errors and yet mine gives mr expression, table account number was not found what is the reason for this