Subscribe to the new YouTube channel dedicated for English content
Understand the structure of the "M" language
What are Variables and Applied Steps?
The nature of Values and Expressions
Primitive and Function values with examples using the advanced editor
Types of Structured Data values
How to create a List and how to access and item of a List?
Form a Record and access a field within a Record
Create a Table access a Column, a Row, or a single value
Extract a table from same workbook: Excel.CurrentWorkbook
Change data types: Table.TransformColumnTypes
Add custom column: Table.AddColumn
Sum a column of numerical values: List.Sum
Convert a query to a Custom Function
Invoke Custom Functions inside power query editor (User Interface)
Invoke Custom Functions from an excel worksheet
Invoke Custom Functions in a custom column
Use Parameters as a variable inside a calculation
Use Parameters to dynamically change data source
Create Parameters inside your worksheet
How to use: File.Contents & Excel.WorkBook
An example of importing & transforming data using 7 table functions
Four functions to transform data: Table.FillDown - Table.Skip - Table.PromoteHeaders - Table.UnpivotOtherColumns
Three functions to handle column headers: Table.FirstN - Table.SelectColumn - Table.Transpose
Import and combine data from a set of inconsistent tables dynamically using "M" language functions:
Excel.Workbook - Table.ColumnNames - Table.ExpandTableColumn
Compare 3 tables to extract matching and unmatching rows using 4 of the List Functions (M language):
List.Union - List.ContainsAny - List.Intersect - List.Difference
Simplify the code used in APQ04 & decrease the number of functions used from 4 to 1
Table.Combine
2 ways comes to rescue if you are stuck with an error:
try .. otherwise -> when text value being entered into a mathematical calculation
if .. then .. else -> when null value being entered into a mathematical calculation
Sometimes you need to bulk replace the values within a single table. If you have a set of text values and want to replace them with another set of values, it is proper to use List Functions.
We'll use a helper table that contains old and new values
The tables will be converted into lists
After completing the replacement process, we will return the table to its original state
We will use a number of list and table functions
(List.RepalceMatchingItmes-Table.ToColumns-Table.FromColumns-Table.ColumnNames)
Are there other ways (other than merge queries) to search within a number of tables using Power Query? This is what we will discuss by searching for customer names and product prices in other tables without using merge queries
Use function: List.PositionOf
Use lookup operator []
Group By to build a report
One of the recurring tasks is the allocations numbers based on allocation ratio that is calculated from within the data itself
Such as distributing the cost to some products or sales outlets according to the percentage of sales, for example
In this example, we will see the allocation of total budget over the months based on the actual percentage of sales for each month from the total sales in the past year.
Solution will be using Group By All Rows and Merge Queries
Merge same way as VLookup ( avoid duplication & control the output)
Merge tables based on 3 Matching Columns
Retrieve information before Expand
Six M-Code functions (Table.Sort - List.Min - List.Max - List.AverAverage - List.First - List.Last)
In a custom column use a Record to do more calculations
In this video you will learn Exact & Approximate match in power query, the comprehensive example will show how you can pick up the correct price and proper volume discount from a dynamic price list and a volume discount matrix
Merge Queries: Exact Match
Group by: Aggregate Transaction Table
Table. SelectRows (M-Code): Approximate Match
List.Last (M-Code): Select Last Item in a List
You have given a one column table contains data for some persons
Every 3 items in this column reprepresents data for one person: Name, ID, and Nationality
You are required to split this column into 3 columns, one column for Name, another column for ID and the last column is for Nationality
The last part of the video explains how to do the same task using worksheet formula
In this video you will learn how to use the Power Query editor as a self learning tool for M language functions. During this lecture we will learn 9 functions of the "M" language as follows:
3 text functions (Text.From - Text.Start - Text.End)
3 Date functions (Date.Year - Date.DayOfWeekName - Date.MonthName)
3 table functions (Table.SelectRows - Table.Group - Table.RowCount)
Each & "_" are working as a shorthand (or sugar syntax) for custom function , the variable "_" can represent:
An item in a list,
A record in a table,
Or a table in a group of tables, based on the context in which it has used
The video is focusing on the concept and contains a bunch of examples, some of them are very basic and some other are a bit complicated.
In this video, you will learn how to use the Table.Buffer function to increase data processing speed. Through a practical example, we will cover the following:
How to calculate the running total for a number of rows
Updating data before using Table.Buffer
Updating data after using Table.Buffer
In this video, we will learn how to breakdown the year-to-date (YTD) total to arrive at monthly totals. This video focuses on Power Query within Excel and starts with a simple example, gradually increasing in complexity. The episode covers the following topics:
Utilizing the Table.SelectRows function
Implementing the Try & Otherwise tools
Handling multiple years within a dataset
Managing multiple products within a dataset"
