Learn advanced Excel techniques for creating dynamic reports using Power Query and M Code in this 12-minute tutorial. Master the use of TOCOL, UNIQUE, IF, and TEXTJOIN functions to generate employee and manager name columns. Explore Power Query solutions, including UnPivot for proper data table creation and Group By for gathering manager names. Dive into M Code syntax, understanding the Field Access Operator and Text.Combine function. Gain insights on optimizing identifier names and interpreting let expressions. Perfect for data analysts looking to enhance their Excel skills and transform raw data into actionable information for decision-makers.
Lookup Manager Names in Column Headers to Create Report - Excel Magic Trick 1866
ExcelIsFun via YouTube
Overview
Syllabus
1 Introduction
2 Worksheet function to create column of employee names from a table using TOCOL and UNIQUE functions.
3 Worksheet function to lookup manager names and join with a comma between each name using the IF and TEXTJOIN functions.
4 Links to buy new excelisfun Mike Girvin M Code book
5 Power Query Solution. Keyboard to import Excel Table into Power Query Editor
6 UnPivot to create a proper data table
7 Group By to gather manager names into a table.
8 Why to remove spaces from identifier names
9 Edit Table.Group Function to extract a column from a table as a list using the Field Access Operator.
10 What does M Code syntax Underscore mean?
11 Text.Combine function
12 Look at let expression
13 Summary
14 Closing and video links
Taught by
ExcelIsFun
Related Courses
-
Intermediate Power Query in Excel
-
Get and transform data with Power BI
-
Running Total in Column - Power Query, DAX or Worksheet Formula?
-
Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
-
Power Query - Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook
-
M Code Class from Basic to Advanced - Power Query Excel & Power BI, Custom Functions
5.0
