// Latest sign-up per member // Pack: Gym membership sign-ups (Power Query Exercises, https://powerqueryexercises.com/downloads/membership-signups-dedupe) // // Source bindings // In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or // Power BI Desktop the first steps of this query do that binding instead: // Signups the pack file signups.csv -> File.Contents(PackFolder & "signups.csv") // Set PackFolder to the folder where you extracted the pack (keep the trailing backslash). // // Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns // 8 rows with columns Email, SignupID, SignedUpOn, Plan, Club. // It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook. // Independent practice material; not affiliated with or endorsed by Microsoft. let PackFolder = "C:\PQE\membership-signups-dedupe\", Signups = File.Contents(PackFolder & "signups.csv"), Source = Table.PromoteHeaders(Csv.Document(Signups, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]), #"Clean email" = Table.TransformColumns(Source, {{"Email", each Text.Lower(Text.Trim(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Clean email", {{"SignedUpOn", type date}}, "en-US"), #"Latest" = Table.Group(#"Changed Type", {"Email"}, {{"Row", each Table.Max(_, "SignedUpOn"), type record}}), #"Expanded" = Table.ExpandRecordColumn(#"Latest", "Row", {"SignupID", "SignedUpOn", "Plan", "Club"}) in #"Expanded"