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

Overview

The course teaches how to use Power Query to analyze customer data and extract information based on specific criteria. The learning outcomes include counting customers who visited certain stores and listing their names. Students will learn various Power Query features such as importing data, merging tables, filtering, grouping, and creating custom columns. The teaching method involves step-by-step demonstrations using Excel files and Power Query functions. This course is intended for individuals interested in data analysis, Excel automation, and Power Query usage.

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.