Advance Excel Power Query - Troubleshooting, Case Studies, Custom M Function with M language
Learn more about Power Query M Language, ready-made patterns, advanced real life case studies, solution to Power Query limitation
Updated on Sep, 2023
Language - English
Hello and welcome to Excel Power Query Advanced course.
At the outset I thank you for showing interest in this course.
In this video I will provide overview of all the contents of this course and what are basic requirements before you enroll into this course. So let’s get started.
There are four major areas I will be covering as part of this course. The first one is for troubleshooting the Power Query errors and workaround for limitation of Power Query. In real any software is not foolproof and has its own limitation. Also, if you don’t use the software as programmed you may encounter errors. And I hope you would agree that error codes or description in most of the software are not that user friendly or they do not provide appropriate solution to resolve the error. Hence in this first section I will show some of the common areas where you face Power Query limitation and errors. Some of them are as follows:
Error encountered with adding / rename / deleting a particular column headings.
Handling null values during arithmetic calculation.
Identify error rows encountered while creating or on refresh of query.
Multiplying effect when you JOIN queries
Issues with filters and how to use advanced filters.
Then the next area is for advanced case studies. Similar to my starter course of Excel Power Query where I have shown you more than 20+ real life case studies of applying power query, here also I have taken some real life case studies which require some more in depth knowledge of Power Query and workaround. This section will be updated new case studies as and when I see some interesting datasets. The current video are related to :
Applying local setting when converting the dates fields
Leave dataset which needed leave details to be broken into separate rows from a single cell.
Unstack the dataset which has uneven details
Combine multiple rows data back to single cell.
Then next section is for advanced M concepts. Here I will give you more information related to M Language in Power Query. What syntax is to be used under advanced editor. How to create parameters and use it inside your power query. How to create new custom M function using PQ interface or through advanced editor. I will also show how you can troubleshoot the custom M function you have created using advanced editor. Then I will show you how you can organize your multiple queries in separate groups. Further I will also show how to use Query Dependency view which will give you visual presentation of all the queries inside your excel file. This will help you understand the flow of all the data from source till the final output, where it is loaded or which queries are referenced to each other. All precedents and dependents to your queries can be easily identified.
And now finally in last section I will be providing some ready made patterns for power query. I will provide all the power query code which you can copy paste into your file and start using it. You will not need to create any new query for these patterns. Since creating these patterns using power query requires lot of efforts and also very complex I am providing you these as ready to use.
So now before you enroll I would like to tell you that there are few requirements.
First one is that you should have latest version of Power Query installed if you are using Excel 2010 / 2013 or if you are using Excel 2016 then it should be updated.
Second and most important is that you should have prior knowledge of using Power Query.
So that is it. There are no further requirements other than these two. So what are you waiting for, enroll now into the course.
What will you learn in this course:
- You will be introduced about various core M Language concepts
- You will apply learning to real life datasets.
- You will learn workarounds for Power Query problems
What are the prerequisites for this course?
- Basic Knowledge of Power Query and Excel
- Office 365 or Power Query installed for Excel
Check out the detailed breakdown of what’s inside the course
Troubleshooting errors and workaround for limitation of Power Query
- Errors while adding / deleting / renaming columns 07:17 07:17
- Supporting files for the course
- Dynamically Reference to column while renaming it and avoid errors 06:42 06:42
- Advanced Filters and Case sensitivity 15:35 15:35
- Identifying error Rows - Keep or Remove them 05:10 05:10
- Handling Null values during addition / Multiplication / Division 07:54 07:54
- JOINS - Duplicates when you merge multiple queries & case sensitivity 05:56 05:56
- Different Unpivot Options - issues and solutions for it 09:16 09:16
Case Studies requiring more in depth knowledge of Power Query
M Language and Advanced query editor concepts
Ready to use Patterns of Custom M Function
Abhay GadiyaBest seller instructor for Power BI and Power Excel
I am Chartered Accountant, Certified Fraud Examiner and have completed Diploma in Information System Audit (Indian equivalent exam for CISA).
I have circa 16+ years of experience into Data Analytics, Internal Audits, External (Statutory) Audits, SOX Compliance, Risk Assessment and Regulatory Compliance. I have audited various entities under BFSI, Automotive Manufacturing, ITES, Infrastructure and Pharmaceuticals sectors. I have worked circa 9 years with Big4 consultancy firms – Deloitte, Price Waterhouse and other with Bank of New York Mellon.
I have conducted various workshops at Institute of Chartered Accountants of India. Approximately 600+ people have attended these workshops and have given extremely positive feedback.
User your certification to make a career change or to advance in your current career. Salaries are among the highest in the world.
Our students work
with the Best
Related Video CoursesView More
Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video CoursesSubscribe now
Master prominent technologies at full length and become a valued certified professional.Explore Now