Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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
I'm getting the following error:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |