There are no further requirements other than these two. Second and most important is that you should have prior knowledge of using Power Query. So now before you enroll I would like to tell you that there are few requirements.įirst 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. Since creating these patterns using power query requires lot of efforts and also very complex I am providing you these as ready to use. You will not need to create any new query for these patterns.
I will provide all the power query code which you can copy paste into your file and start using it. All precedents and dependents to your queries can be easily identified.Īnd now finally in last section I will be providing some readymade patterns for power query. 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.
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. Then I will show you how you can organize your multiple queries in separate groups. I will also show how you can troubleshoot the custom M function you have created using advanced editor. How to create new custom M function using PQ interface or through advanced editor. How to create parameters and use it inside your power query. What syntax is to be used under advanced editor. Here I will give you more information related to M Language in Power Query. Then next section is for advanced M concepts. Unstack the dataset which has uneven detailsĬombine multiple rows data back to single cell. Leave dataset which needed leave details to be broken into separate rows from a single cell. The current video are related toĪpplying local setting when converting the dates fields This section will be updated new case studies as and when I see some interesting datasets. 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. Then the next area is for advanced case studies. Issues with filters and how to use advanced filters. Identify error rows encountered while creating or on refresh of query.
Handling null values during arithmetic calculation. Some of them are as follows:Įrror encountered with adding / rename / deleting a particular column headings. Hence in this first section I will show some of the common areas where you face Power Query limitation and erros.
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. Also, if you don't use the software as programmed you may encounter errors. In real any software is not foolproof and has its own limitation. The first one is for troubleshooting the Power Query errors and workaround for limitation of Power Query. There are four major areas I will be covering as part of 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. Hello and welcome to Excel Power Query Advanced course.Īt the outset I thank you for showing interest in this course. You should have fair idea of how to use Power Query in Excel
You will apply learning to real life datasets. You will be introduced about various core M Language concepts Learn more about Power Query M Language, ready-made patterns, advanced real life case studies, solution to PQ limitation srt | Duration: 28 lectures (3h 26m) | Size: 545.7 MB Advanced Excel Power Query-M language, troubleshoot, Pattern