Forum Discussion
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?
| 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 |
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.
4 Replies
- AlB
Community Champion
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 resPlease 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
I'm getting the following error:
v-zhenbw-msftCommunity Support
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.
