Find Max date and return corresponding row data power query

[Anno2019]

Dear Guru's

I have been searching for the answer, came close, but each person's needs are slightly different.

I have what seems like a simple task, but alas, I need help.

Below is an illustration of what I am dealing with.  I have three tables

1.  Purchasing Data - Contains purchasing data of customer store transactions

2.  Survey Data - Contains survey data of customer survey trends and feedback

3.  Final Result - Contains Customer ID, requires the last transaction with corresponding data elements from both purchasing and survey tables.

I hope the below explains.  Note:  I would prefer to create this in power query mode.

[Anonymous]

Hi @Anno2019,

Did mattww 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[mattww · Accepted solution]

Hi @Anno2019 

You could add a grouped index onto your purchase and survey table, which could then be used as a filter to identify the most recent entry per customer. See sample code below:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTIy1Tcw1jcyMAJxgkvyi1KBwrE6SKqMQKrM9Q1M8KoCSRgCzTJFVWWEqQpkowWqKmNMGw2N9A0MUVWZKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Purchase Date" = _t, #"Store Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Purchase Date", type date}, {"Store Name", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Purchase Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer ID"}, {{"Count", each _, type table [Customer ID=nullable text, Purchase Date=nullable date, Store Name=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Purchase Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}, {"Customer ID.1", "Purchase Date", "Store Name", "Purchase Index"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Customer ID", "Purchase Date", "Store Name", "Purchase Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Purchase Index] = 1))
in
#"Filtered Rows"

This is based on the following blog post if you want to work through the steps yourself.

Create Row Number for Each Group in Power BI using Power Query - RADACAD

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anno2019]

@mattww , so sorry, but I have never tried grouping before, I will try this.