Segmenting a List into 8 Pieces using Excel’s Power Query (Get and Transform)

WARNING: this is a fast-paced overview.

Don’t worry if you can’t follow every step.

The detailed workbook can be downloaded here:
0:29 Overview
2:35 Demonstration of the solution
3:17 How the solution/model works
3:45 Figuring out who HAS NOT made any purchases
4:30 Who’s made purchases at all 3 stores?
7:04 Figuring out who shopped at just 1 store
8:53 Who’s shopped at 2 stores

My friend Bart in Amsterdam sent a challenge:
In a loyalty program there are cardholders and 3 stores.

How can Excel or Power Query (Get & Transform) segment the list of transactions into 8 segments? People who’ve …

1. not made any purchases
2. purchased at all 3 stores
3. only shopped at Trader Joe’s
4. only shopped at Wegmans
5. only shopped at Publix
6. shopped only at Publix and Trader Joe’s
7. shopped only at Publix and Wegmans
8. shopped only at Trader Joe’s and Wegmans

This solution weaves together lots of Power Query features, including: left anti-join, left outer-join, grouping, fill-up, filtering, and conditional columns.

Power Query Solution over at ExcelIsFun:


My book: Guerrilla Data Analysis 2nd Edition
My old blog: