Completed
) Group By Feature to aggregate and to list all matching records for Customers who went to Zero Stores. Use Count Rows and All Rows.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Introduction & Problem Introduction
- 2 ) Import From Excel Table
- 3 ) Left Outer Merge or Left Outer Join
- 4 ) Reference another Query in Power Query
- 5 ) Filter to show null
- 6 ) 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 ) 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 ) Underscore character in Power Query = All items in Row
- 9 ) Start Code for final report
- 10 ) Filter out nulls to avoid errors from inconsistent data type in column.
- 11 ) Remove Duplicates
- 12 ) Steps to get three columns, one for each Store and then list the stores that each customer visited: 1) Duplicate Column then Pivot.
- 13 ) Group By to Count Customers in each Coupon Group and List of Customers in Each group using Count Rows and All Rows.
- 14 ) 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).
- 15 ) Append Earlier Query to current query so that the count and list of customers that visited no stores is listed in the final report.
- 16 ) Edit Previous Query to update current query.
- 17 ) 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.RemoveLas…
- 18 ) 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.
- 19 ) 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.
- 20 ) Check Query Report by adding store to transaction table
- 21 ) 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.
- 22 ) Summary
