I am glad to announce that I have launched my first online course – Power Query Training at Udemy.com.
Special discount coupon – PQSP45
You can use above coupon even though there are no current promotion running from Udemy.com.
This Power Query Training is for people who are completely new to Power Query. I am explaining things from scratch so that you understand all fundamentals about Power Query and feel confident about using. I am covering 80% of topics in this Power Query Training course.
This Power Query Training is being divided into 10 sections.
In Section 1 I will be introducing you about Power Query, why should we use Power Query instead of other tools like VBA, SQL or Excel formulas. You will understand the layout of Power Query window in this section.
Under Section 2 you will understand how you can connect to different sources of data. After completing this section you will learn how to import data from existing excel file using Tables, Dynamic ranges and named ranges into Power Query. I will also explain steps to import data CSV files, Text Files, Access files and Excel files stored in a particular folder into Power Query.
After importing the data into Power Query you would perform transformations on it. The crux of Power Query are these transformation. Each of these transformation are covered in Section 4 till end of the course.
In Section 3 I have skipped transformation step and have explained different options available in Power Query through which you load data back into Excel. These options are – Excel Tables, As connection only and load to Data Model (Power Pivot / Pivot Tables). I have also explained the steps to automate refresh of Power Query so that whenever the source data changes you would get most updated results from Power Query.
Section 4 covers the basic transformation which can be accessed from Power Query ribbon itself. For using these transformation you would have to just click the button on Power Query Ribbon. No need to key in any formulas. This kind of GUI is one of the most intuitive interface I have seen any software yet. It makes learning Power Query easy and also to review and maintain it.
Under column transformation you will learn how to insert, duplicate, remove, splitting, Unpivot columns. Also, you would learn about different data types in Power Query.
Then Row transformation video will explain you about filtering rows, Removing or keeping duplicates, Grouping the rows.
I have also covered built transformation viz. Text transformation, Numbers Transformation, Date transformation and condition column transformation in this section.
Then in Section 5 you would learn how to append data from multiple files stored in folder. You will also understand the difference between duplicating or referencing a Power Query. Now instead of import data from files stored in a particular folder individually you could also import everything at one go itself. Also if you change the folder name or move the files to other folder then how to change source of Power Query built using old path is explained in this section. This will be also helpful if you are sharing Power Query files with other people for whom source file path would be different. Also in case of big project you would need to keep track of files created, received, last modified, etc. then Power Query can easily built that tracker and will update on each refresh cycle.
Further, instead of appending data from multiple sources you would like to merge data sets. Till now you would have been using VLOOKUP, Index & Match formulas for this. I had posted one blog on this as well. In Section 6 you would learn in detailed manner how you can perform each of those joins and also Cross Joins / Cartesian Products of Table in Power Query.
Section 7 will explain some fundamental concepts of M Language if you would like to move yourself to advanced level of Power Query. In this section you would also look how you can manually perform built in Text, Date, Conditional transformation using Power Query Formulas. You will learn Power Query formulas which are equivalent to Excel – And / Or formulas within IF function. You will also learn about IFERROR equivalent function.
Section 8 will show some real life case studies where you have received data in dirty format and how you can easily convert that into a tabular format using Power Query. These case studies are based on the knowledge you have gained till now from previous videos.
Further, Section 9 will explain you more about Power Query Objects like – Lists, Records, Tables, Values, Binaries, etc. Having knowledge of these Power Query Objects is must if you want to move to next level.
And finally in Section 10 you would see some advanced case studies using the Power Query Objects. You would learn how you can refer to steps within same Power Query or steps from other Power Query. How you can insert a custom step within Power Query, How you can build dynamic calendar table with just start and end date as input to Power Query. This should help you increase your appetite for learning and using Power Query more.
After completing this course you would also get a completion certificate. And you would be confident about the potential of Power Query and why it is a real Game Changer for Excel users.
So hurry and immediately enroll into course. And remember to use special discount coupon for you.
Special discount coupon – PQYT80