Power Query - Dynamic Column Selection
Learn how to go beyond the point-n-click User Interface to create custom functions for row-level aggregations. Also, witness the power and simplicity of the "_" underscore feature to create records. See how to target specific columns in a record and convert records to lists.
NOTE: I realize that this can be accomplished more easily using "Unpivot" and "Group By", but I wanted to showcase the use of the often misunderstood 'underscore' symbol in "M". Plus, bring attention to record and list manipulation functions.
File Download Link:
https://www.bcti.com//wp-content/YT_D...
00:00 Demonstration of Objective and Issue
01:04 Adding New Data (Problem)
01:54 Project Objectives
02:17 File Download Link Information
02:25 Convert Row Data to Records
03:04 Removing Unwanted Columns
03:44 Converting Records to Lists
04:17 Creating Averages from a List of Values
04:34 Final Data Cleanup
05:12 Testing for New Data Discovery
06:23 Performing Multiple Aggregations
06:46 Creating the Lists for Aggregations
07:55 Creating Row-Level Sums
08:10 Creating Row-Level Averages
08:21 Creating Row-Level Max
08:32 Creating Row-Level Min
08:40 Creating Row-Level Count
08:57 Performing Final Data Cleanup Steps
09:13 Testing for New Data Discovery