cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Return value from a column based on date in another column

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?

 

UserCourseEnrollment DateFirst Course
John DoeA10/21/2017C
John DoeB1/29/2020C
John DoeC7/18/2016C
John DoeD8/14/2019C
Alex HamiltonA7/18/2020B
Alex HamiltonB1/29/2019B
1 ACCEPTED SOLUTION

Hi @andronachealin ,

 

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

 

R1.jpg

 

Or you can use merge function to get the result.

 

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

 

R2.jpg

 

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

 

R3.jpg

 

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

 

R4.jpg

 

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

 

R5.jpg

 

R6.jpg

 

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.

View solution in original post

4 REPLIES 4
Super User III
Super User III

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 

 

SU18_powerbi_badge

I'm getting the following error:


2.PNG

 

Hi @andronachealin ,

 

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

 

R1.jpg

 

Or you can use merge function to get the result.

 

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

 

R2.jpg

 

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

 

R3.jpg

 

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

 

R4.jpg

 

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

 

R5.jpg

 

R6.jpg

 

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.

View solution in original post

Thank you @v-zhenbw-msft  & @AlB 

Both solutions work great.

Much appreciated.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors