Lists & records › List.Transform, List.Select, List.Distinct39 / 47

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeList.TransformList.SelectList.Distinct

List.Transform: a clean newsletter email list

Nadia wants to send a promo newsletter to everyone who filled in the form. The email column in Kontak is messy: capital letters, leading or trailing spaces, empty cells, and one person who signed up twice with different spelling.

Task

Build a clean email list from the Email column in three steps: (1) List.Transform with Text.Lower(Text.Trim(_)) on every email; (2) List.Select to drop nulls and empty text ""; (3) List.Distinct to drop duplicate emails. The final result is a list of 22 emails in order of first appearance in the data (not sorted).

Data sourcesKontak

Required functionsList.TransformList.SelectList.Distinct

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
List.Transform(list, each ...) runs the same formula on every item; _ is the current item. List.Select(list, each condition) keeps the items where the condition is true.
Hint 2
Clean first, then remove duplicates. List.Distinct compares text exactly, so "Budi.Santoso@Gmail.com " and "budi.santoso@gmail.com" only count as the same after trimming and lowercasing.
Hint 3
#"Cleaned Emails" = List.Transform(Source[Email], each ___), #"Filled Emails" = List.Select(#"Cleaned Emails", each _ <> null and ___), then List.Distinct(___).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

let
    Source = Kontak,
    #"Cleaned Emails" = List.Transform(Source[Email], each Text.Lower(Text.Trim(_))),
    #"Filled Emails" = List.Select(#"Cleaned Emails", each _ <> null and _ <> ""),
    #"Unique Emails" = List.Distinct(#"Filled Emails")
in
    #"Unique Emails"

Explanation

Out of 25 contacts, one email is null (K-015), one is empty text (K-009), and K-021 turns out to be the same Budi Santoso as K-001. After the three steps, 22 unique addresses remain. Text.Trim and Text.Lower are null-safe (null stays null), so the nulls can be dropped after the transform.
Step order decides the result: run List.Distinct before cleaning and Budi's two emails still count as different. Also remember that null and "" are two different things in M, so check for both.

Power Query· sources: Kontak

let
    Source = Kontak
in
    Source

Loading the Advanced Editor…

Edit the M code until the result matches, then press Check answer.

Validation result