See how to use Power Query to analyze a group of customers who have been given coupons to three stores. Our goal is to count how many customers used coupons in one of eight coupon groups (all combinations of the three store coupons from the Coupon Redemption Table) and then extract the customer names in each group. Learn about many Power Query features and functions. This trick comes from Bill Szysz, Power Query Master, at YouTube. The data and goals are:
1) Each customer was given coupons to go to Wegmans, Publix, Trader Joe’s.
2) We have a list of Stores names.
3) We have a list of transactions for the coupon redemptions that lists purchases, customer name and store
4) Goal 1: Count Customers who visited a given set of stores.
5) Goal 2: List Customers who visited a given set of stores.
Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Topics:
1. (00:13) Introduction & Problem Introduction
2. (02:35) Import From Excel Table
3. (04:01) Left Outer Merge or Left Outer Join
4. (06:47) Reference another Query in Power Query
5. (07:24) Filter to show null
6. (07:49) Group By Feature to aggregate and to list all matching records for Customers who went to Zero Stores. Use Count Rows and All Rows.
7. (09:28) 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).
8. (10:07) Underscore character in Power Query = All items in Row
9. (11:15) Start Code for final report
10. (11:40) Filter out nulls to avoid errors from inconsistent data type in column.
11. (12:06) Remove Duplicates
12. (12:25) Steps to get three columns, one for each Store and then list the stores that each customer visited: 1) Duplicate Column then Pivot.
13. Pivot Features to get a record for each Customer containing the store names that they visited
14. (14:00) Group By to Count Customers in each Coupon Group and List of Customers in Each group using Count Rows and All Rows.
15. (15:40) 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).
16. (16:02) Append Earlier Query to current query so that the count and list of customers that visited no stores is listed in the final report.
17. (16:34) Edit Previous Query to update current query.
18. (17:26) 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.
19. (20:40) 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.
20. (21:31) 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.
21. (23:40) Thanks to Bill Szysz
22. (24:08) Summary
Related Videos:
Excel Magic Trick 1444: Logical Formulas: Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1444 Part 2: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1445: Single Cell Array Formula: Count Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1446: Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Excel Magic Trick 1447: DAX Formulas to Count Customers in 8 Sales Coupon Groups & List Store Names