Power Query Count & Extract Customer Names for 8 Sales Coupon Groups

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn how to use Power Query in Excel to analyze customer coupon usage across multiple stores. Master techniques for counting and extracting customer names in various coupon groups, perform left outer joins, utilize Group By features, and manipulate data with custom columns and M code. Explore advanced Power Query functions like Record.ToList and Table.FromColumns to create comprehensive reports on customer behavior and coupon redemption patterns.

Syllabus

) Introduction & Problem Introduction.
) Import From Excel Table.
) Left Outer Merge or Left Outer Join.
) Reference another Query in Power Query.
) Filter to show null.
) Group By Feature to aggregate and to list all matching records for Customers who went to Zero Stores. Use Count Rows and All Rows..
) Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action)..
) Underscore character in Power Query = All items in Row.
) Start Code for final report.
) Filter out nulls to avoid errors from inconsistent data type in column..
) Remove Duplicates.
) Steps to get three columns, one for each Store and then list the stores that each customer visited: 1) Duplicate Column then Pivot..
) Group By to Count Customers in each Coupon Group and List of Customers in Each group using Count Rows and All Rows..
) Second Time we Edit Table.Group Function to show just the column and convert to a list rather than a table (Change table type to list type in Group By action)..
) Append Earlier Query to current query so that the count and list of customers that visited no stores is listed in the final report..
) Edit Previous Query to update current query..
) Add Custom Column to Convert the Record to a List. This lists created for each row will be the columns in the final report. In this step see the Power Query Functions: Record.ToList, List.RemoveLastN. Also see how join the record to a columns and add a null row..
) Create a column that represents the Field Names (Column Names / Headers) in the final Table/Report. See how to add a column as an Index and then add a Prefix..
) Edit M Code using Advanced Editor and add a line of code that uses the Table.FromColumns Function to combine the Columns and Headers into a table..
) Check Query Report by adding store to transaction table.
) Look at Previous Steps in the Power Query Editor to check if the edited code will be correctly reflected and to verify if all stores are listed..
) Summary.