Power Query Count & Extract Customer Names for 8 Sales Coupon Groups
Offered By: ExcelIsFun via YouTube
Course Description
Overview
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.
Taught by
ExcelIsFun
Related Courses
Introduction to DatabasesMeta via Coursera Analyzing Big Data with SQL
Cloudera via Coursera Managing Google Workspace 日本語版
Google Cloud via Coursera Creating Advanced Reports with SAS Visual Analytics
SAS via Coursera Query Client Data with LibreOffice Base
Coursera Project Network via Coursera