Subscribe to the new YouTube channel dedicated for English content
A table with 5 columns: Employee ID, Name and 3 columns for monthly amount from Jan through Mar. Using Power Query delete all columns except the Name and Amount for the last month (Mar), but if new months are added, how can Power Query choose the last column automatically?
Find the working files below the video - give it a try then watch the solution
The solution is presented in three different ways, all of which are correct:
The solution using the Power Query Editor (User Interface)
Hybrid solution using "M" functions & Power Query editor together
Simplify the code of the second solution to turn it into a single line of code
Two-column table: date (during Jan through Mar) and amounts
Required using Power Query:
Add a column for the running total for the entire period
Add a column for the running total for each month separately
Find the working files below the video - give it a try then watch the solution
The solution is presented in 2 ways:
Using a combination of 2 "M" functions List.FirstN & Table.SelectRows
Depends on Custom Functions and shared by our friend (Hicham bin Tama)
We have two tables: the first contains employee data (Name, Code, Band & Department), the second has one column for the department and may contain one or more rows of data
Required using Power Query: Filter the first table using the data of the second table in a dynamic manner with ignoring letters' case
Find the working files below the video - give it a try then watch the solution
The solution is presented in 2 ways:
Using the Power Query editor (User Interface) by applying a Merge Query technique (Inner Join Kind), in order to handle letters' case, we should use Fuzzy Matching
Depends on 2 of the "M" functions, Table.SelectRows and List.Contains
PQC04 is an advanced level from PQC03, and more or less using same data
Required using Power Query: Filter the first table using the data in the second table (the second table contains 2 columns: Band & Department)
The working file is below the video - try it yourself before watching the solution
(It is recommended to watch the solution for PQC03 first)
The solution is presented in two ways:
First method: using (if statement) where three separate steps are made for filtering data table then the appropriate step is chosen using both: (Table.IsEmpty) function with if statement. Filter steps are using the functions (Table.SelectRows + List.Contains)
Second method: It is taken from one of the posts and depends on Flags, where for each search column, a flag of zero or one is added to the table. Then, Table.SelectRows and List.Max functions is being used to choose the correct rows.
You have a table that contains the prices of a specific product by date. Using Power Query, you need to separate the date column into two columns: a column contains the start date and a column for the end date, taking into account the following:
The price end date is the day before the new price start date
The end date of the last price is today's date
Working file is below the video - try it yourself before watching the solution
Solution approach is based on adding an index column starting from number "1"
Another column to be added for the end date, each starting date will be shifted up based on the index column
Then, one day will be subtracted from each date in side End Date column using the Date.AddDays function
This method will result in an error in the last row, "try/otherwise" will be used to handle it.
Functions: DateTime.LocalNow and Date.From will also used to input today's date in the last row
You have a table that contains offer prices for some product obtained from a number of vendor s. Using Power Query, you need to add a column contains the minimum price for each product and another column to specify the vendor name provided the lowest price:
Working file is below the video - try it yourself before watching the solution
The solution is presented in two ways:
First: using the M functions, Record.ToList function is used to convert the rows into lists, then the lowest value to be calculated using function List.Min. The name of the vendor is then determined by using Record.FieldNames and List.PositionOf functions.
Second: through the Power Query editor, where it relies on converting columns into rows using the Unpivot option and extracting the least value using the Group By. Finally, two tables will be merged from within the same Query using the Table.NestedJoin function.
Is Power Query able to perform approximate match?
You have a table that contains the quantity discount according to the different sales levels, and another table that contains the actual sales. It is required to find the correct discount rate for each sale according to the actual quantity sold using Power Query.
Working file below the video - try it yourself before watching the solution
The solution is presented in two different ways:
Using the User Interface: Append both tables then perform a sort then fill down. It is also useful to insert an index column to maintain the original table order.
Using M-Code functions: Mainly depends on the Table.SelectRows function together with a custom function to configure the selection criteria. List.Max is used also to pick up the correct discount rate.
In this challenge your are required to change a layout of a table. You have a table containing vendor names together with their categories in tabular form, you need to use this data to create a pivoted table that shows names of the vendors in the headers, then the categories will be in the rows below each and every relevant header. Take a look on the challenge before watching the solution
The solution is very simple and short, at the beginning we will group the table by vendor name, then we will using the Table.FromColumns function to rebuild the in the required layout.
Watch the video to see how simple is the solution
In this challenge your are required to set up a flexible filter to your data table. You have a Filter Table contains one column of data, you can change the header of this column to match any column from the Data Table column, then you can add any number of items to the filter column. Using Power Query, you need to pick up the right column dynamically and perform the filtration. Take a look on the challenge before watching the solution
2 Solutions provided.
The 1st solution is based on M-Code Function, that is using 5 functions to get seamless results:
Table.SelectRows - Table.ColumnNames - Tabel.Column - List.Contanis - Record.Filed
The 2nd solution will based on Merge Queries with some twists to make sure that you can pick-up the right column dynamically.
