Learn advanced Power Query and M Code techniques to transform data and create a comprehensive marketing customer coupon report in this 25-minute tutorial. Master the process of counting and categorizing customers into different marketing coupon groups using sophisticated data transformation methods. Tackle the fundamental challenge of creating missing store usage records from existing tables through strategic data manipulation. Import and merge multiple data tables using left outer joins to combine customer and sales information effectively. Explore M Code syntax including let expressions and their structural components for advanced query building. Apply filtering, grouping, and pivoting techniques to organize customer data by coupon usage patterns. Implement dynamic variables using List.Distinct and List.Sort functions to create flexible store name sets for Table.Pivot and Table.Group operations. Utilize advanced M Code functions including Record.ToList, List.RemoveLastN, and Table.FromColumns to construct the final report structure. Practice removing duplicates, handling null records, and creating custom columns with complex calculations. Combine queries using append operations and sort results for optimal presentation. Follow along with the provided Excel file to gain hands-on experience with these advanced Power Query techniques for creating seemingly impossible reports through strategic M Code implementation.

Syllabus

1. 00:00 Introduction.
2. 00:38 Thanks to Bill Szysz for M Code in this video.
3. 00:58 Goal for data transformation and final report.
4. 01:43 Fundamental data problem: we do not have store usage records for all customers. We will have to create the data from existing tables.
5. 02:04 Download Excel file so you can follow along.
6. 02:11 Import initial table into the Power Query Editor.
7. 02:30 Why you always want to remove spaces in M Code Identifiers.
8. 03:13 Load first table as Only Create Connection.
9. 03:26 Import second source data table into the Power Query Editor.
10. 03:31 Remove existing query step.
11. 03:34 Keep customer & store columns in fSales table.
12. 03:44 Remove duplicate customer & store records.
13. 04:36 What is a let expression? And how is structured M Code Syntax?
14. 06:16 Create left out join using the merge feature: dCustomer table and fSales table.This pulls store names from sales table into the customer table.
15. 08:19 Reference left outer join query and create intermediate step with customers who used no coupons.
16. 08:53 Filter to get null records.
17. 09:09 Group null records to get count and list of customers who did not use coupons.
18. 10:02 Amend M Code in Table.Group function. We want a list rather than a table.
19. 11:23 Remove store column from null groupby query.
20. 11:55 Begin query to create the final report.
21. 12:11 Filter to remove null records.
22. 12:22 Why we have to pivot table.
23. 12:54 Copy store columns.
24. 13:13 Pivot copied store column with store column as values and the calculations is “Don’t Aggregate”.
25. 13:56 Groupby feature used on store columns to create count and list of customers in coupon groups.
26. 15:18 Create custom variable that can create a dynamic set of store names for the Table.Pivot and Table.Group functions. Use the List.Distinct and List.Sort functions.
27. 17:26 Append Query with Customers who used no coupons to the query with customers who used coupons.
28. 18:19 Sort Count column Z to A.
29. 18:26 Create Custom Column with final columns for the final table as lists. Use the Record.ToList and List.RemoveLastN functions, as well as the join operator ampersand, &, and a null list.
30. 20:55 Create Custom Column with field names for final table.
31. 22:06 Use the Table.FromColumns function to create the final report table.
32. 23:25 Load final report and test it.
33. 24:19 Summary
34. 24:27 Closing