Advanced Excel Power Query-M language, troubleshoot, Pattern

Name: Advanced Excel Power Query-M language, troubleshoot, Pattern
Rating: 4.3 (685 reviews)

Learn more about Power Query M Language, ready-made patterns, advanced real life case studies, solution to PQ limitation

Created byAbhay Gadiya

Last updated 6/2024

English

English [Auto],Turkish [Auto],

What you'll learn

You will be introduced about various core M Language concepts
You will apply learning to real life datasets.

Course content

5 sections • 38 lectures • 4h 20m total length

Introduction to Power BI Query Advanced Excel4:26
Discover how to troubleshoot Power Query errors, explore advanced concepts, case studies, and ready-made patterns in Excel Power Query.
Course requirements and ratings0:37
Errors while adding / deleting / renaming columns7:16
Troubleshoot errors when adding, deleting, or renaming columns in Excel Power Query, including differences between source steps and subsequent steps, and best practices for refreshing and loading datasets safely.
Dynamically Reference to column while renaming it and avoid errors6:42
Learn to dynamically reference and rename Power Query columns using table dot column names and zero-based indices to prevent refresh errors when source column names change.
Advanced Filters and Case sensitivity15:35
Explore how Power Query filters differ from Excel filters, highlighting case sensitivity, data-type dependent options, and advanced filters with multi-column conditions and dynamic parameters.
Identifying error Rows - Keep or Remove them5:10
Identify and fix data errors in Power Query by locating exact error rows and cells, understanding mismatched data types, and removing or correcting erroneous values to ensure clean load.
Handling Null values during addition / Multiplication / Division7:54
Power Query handles null values in math operations by converting nulls to zero for addition and to one for multiplication, enabling correct results with list dot sum across multiple columns.
JOINS - Duplicates when you merge multiple queries & case sensitivity5:56
Explore how power query merges create a multiplying effect on rows with left outer joins, and enforce case sensitivity, unlike vlookup, using product and sales datasets.
Different Unpivot Options - issues and solutions for it9:16
Explains when to use unpivot columns, unpivot other columns, or unpivot only selected columns in Power Query, with guidance on adding region and 2018 data as dimensions.

Converting Date and Time based on Locale date & time5:20
Master converting date and time from different time zones to your local time in Excel Power Query, using date time zone, English UK settings, and extracting date and time components.
Leave Data Part 17:14
Convert the year’s leaf dataset into a columnar format with power query, unpivot dates, extract leave dates, and replace abbreviations via a full-form merge.
Leave Data Part 29:32
Convert a messy leave dataset into a clean, row-based table using power query by splitting on commas and dashes, handling Indian date formats, and expanding ranges.
Uneven stacked data to be un-stacked - Part 15:10
Unstack uneven date data per person with Excel Power Query. Compare a helper column method with a Power Query–only approach to dynamically create columns and load results.
Uneven stacked data to be un-stacked - Part 24:49
convert an uneven dataset into an un-stacked view using power query by importing data, adding an index column per group, grouping by, pivoting, and loading into excel.
Combine Multiple row data into single cell - reverse of delimiting text values7:55
Learn to consolidate invoice rows into a single row by grouping data, removing duplicates, and using text.combine to join product names with commas, totaling sales in Power Query M.
Getting RANK inside Power Query6:35
Rank data in power query with a workaround: sort by score, add an index, and group by score to derive min, average, max ranks, and conditional ranking with custom function.
Distribution of Fixed cost across the months - get list of dates7:57
Distribute monthly fixed costs across a date range with Power Query M, generating a per-month summary by creating date ranges, extracting start of month values, and grouping results.
Prepaid Expenses to be charged each month4:43
Proportionally distribute prepaid expenses over a date range using Power Query. Compute per day cost, generate date lists, group by start of month, and load the monthly cost into Excel.
Convert Multiple Column Groups to Rows in Power Query4:56
Convert multiple column groups into a single columnar dataset in Power Query using transpose, merge columns, and unpivot techniques to preserve streaming.

Building blocks of M Language in Power Query5:11
Explore the building blocks of M language in Power Query, covering case sensitivity, data type rules, zero-based indexing, lack of IntelliSense, and how to access text, date, and conditional functions.
Power Query objects - What are "Lists" in Power Query ?11:42
Master power query lists and list of lists, including manual lists, lists from columns, and sequential or alphabet lists. Convert lists to tables to enable transformations like removing duplicates.
Power Query Objects - What are "Records" in Power Query ?9:16
Explore Power Query records, the horizontal counterpart to lists, with named fields and values, manual and multiple records, and how to convert them to tables and extract data.
Power Query Objects - What are "Tables" in Power Query ?3:10
This video introduces Power Query objects beyond lists and records, detailing tables, values, binaries, errors, and functions, and explains converting to tables and using M functions.
Power Query objects Recap1:15
Explore key Power Query objects—lists, records, and tables—and learn to convert lists or records into tables using the into table button on the Power Query ribbon.
M Language and Advanced query editor concepts9:11
Explore m language and the advanced query editor in power query, transforming data with let, source, and in, computing rate per quantity, and grouping by customer and product.
Introduction to Parameters in Power Query4:47
Discover how Power Query parameters, a type of query that acts as variables, enable data interaction by defining and editing values from any value, list, or query across your workbook.
Parameters - Creating / editing / deleting through PQ interface11:36
Discover how to create, edit, and delete parameters in Power Query, apply them in filtering and removing top rows, and choose between any values, list values, or query lists.
Custom M Function - Introduction4:13
Learn how to create and use custom M functions in Power Query to consolidate multiple similarly structured files, cleanse data, and automate transformations driven by variables.
Custom M Functions - using Parameters and Power Query Interface13:41
Learn to build a custom function in Power Query using parameters. Apply it to folder-based text files, append cleansed worker time data, and execute data cleansing steps.
Custom M function - using advanced editor11:14
Master creating a custom M function in Power Query using the advanced editor, inserting a parameter, and converting between function and original queries for folder-based text files.
Conditional RANKIF using custom M function - Nested calculation in Power Query6:41
Learn to calculate conditional ranks within each department in Power Query by building a custom M function, grouping by department, and invoking it to rank student scores.
Referencing Intermediate steps from other query7:35
Learn how to reference intermediate steps from one Power Query query to another, using records and meta steps, in Excel or Power BI.
Grouping Queries and Query Dependency view6:37
Learn to organize power queries by grouping them, create reference and duplicate queries, and use the query dependency view to visualize data flow from sources to output in data models.

Introduction to ready to use Custom M Function2:36
learn how to use ready to use custom M functions in Power Query for Excel, copy into a blank query, apply them instantly, and manage function inventories.
Performing Approximate Match inside Power Query6:58
Explore performing an approximate match in Power Query in Excel to map ages to age bracket descriptions using a list-based lookup.
Performing Vendor Ageing analysis using Approximate Match inside Power Query11:52
Explore how to perform approximate match in power query for aging analysis of vendor balances using a custom function from Ken Pulse, including aging buckets and region-wise pivots.
Calculating working days between dates using custom M function6:01
Create a custom M function in Power Query to calculate working days between two dates with configurable weekends, by copying code and invoking it on your data; holidays aren’t excluded.

Requirements

You should have fair idea of how to use Power Query in Excel

Description

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 erros. 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 readymade 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.

Who this course is for:

Person who wants learn more about M language of Power Query
Customizing options available inside Power Query

Advanced Excel Power Query-M language, troubleshoot, Pattern

What you'll learn

Explore related topics

Course content

Troubleshotting errors and workaround for limitation of Power Query9 lectures • 1hr 3min

Case Studies requiring more in depth knowledge of Power Query10 lectures • 1hr 4min

M Language and Advanced query editor concepts14 lectures • 1hr 46min

Ready to use Patterns of Custom M Function4 lectures • 27min

Bonus Lecture1 lecture • 1min

Requirements

Description

Who this course is for: