Completed
) Filter out nulls to avoid errors from inconsistent data type in column.
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
