Return value from a column based on date in another column

[andronachealin]

Hi everybody.

Need some help in writing a formula in PowerQuery that would give me the results from the table below.

Problem: I need to populate the "First Course" column with the value from the "Course" column based on the 1st date entry in the "Enrollment Date" column.

Any ideas?

User	Course	Enrollment Date	First Course
John Doe	A	10/21/2017	C
John Doe	B	1/29/2020	C
John Doe	C	7/18/2016	C
John Doe	D	8/14/2019	C
Alex Hamilton	A	7/18/2020	B
Alex Hamilton	B	1/29/2019	B

[AlB]

Hi @andronachealin 

Paste the following M code into a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lFyBGJDA30jQ30jA0NzpVgdFFknkKy+kSVQ0sgAXdIZiM31DS1AOs3QJV2A2ELf0AQkaQmWdMxJrVDwSMzNzCnJz4PaDNUONRtdBZLtIDNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Course = _t, #"Enrollment Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Course", type text}, {"Enrollment Date", type date}}),
    auxT_ = Table.Group(#"Changed Type", {"User"}, {{"First Course", each Table.Sort(_,{{"Enrollment Date", Order.Ascending}})[Course]{0}}}),
    res = Table.AddColumn(#"Changed Type", "First Course", each auxT_[First Course]{List.PositionOf(auxT_[User],[User])})
in
    res

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[andronachealin]

I'm getting the following error:

[v-zhenbw-msft · Accepted solution]

Hi @andronachealin ,

You need to put the @AlB 's M query in Advanced Editor.

Or you can use merge function to get the result.

1. Copy the original table, then group by users to get the min date.

2. And we can merge two tables based on two conditions.

3. We can expand it to get the min date course.

4. At last we need to merge again to get the result.

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[andronachealin]

Thank you @v-zhenbw-msft  & @AlB 

Both solutions work great.

Much appreciated.