Forum Discussion
Fab117
3 years agoHelper IV
Custom column with filter on Min value
Hello, In my table [TasksAndTaskBaselines (2)], I've the fields: [ProjectId] (Text) [MilestoneNumberNotCompleted] (Integer). Example : ProjectId MilestoneNumberNotCompleted 123 ...
- 3 years ago
Hi Fab117 ,
You can try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRyivNyVGK1YFxjZDYpkhsNGVoXHMktjES2xCbFhNTMyDXBImNJmWEW8ociW2hFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectId = _t, MilestoneNumberNotCompleted = _t]), #"Grouped Rows" = Table.Group(Source, {"ProjectId"}, {{"AllRows", each _, type table [ProjectId=nullable text, MilestoneNumberNotCompleted=nullable text]}}), #"Add Current Milestone" = Table.TransformColumns( #"Grouped Rows", { {"AllRows", each Table.AddColumn( _, "CurrentMilestone", (r)=> if r[MilestoneNumberNotCompleted] = List.Min(_[MilestoneNumberNotCompleted]) then r[MilestoneNumberNotCompleted] else null ,type text ) } } ), Expanded = Table.Combine(#"Add Current Milestone"[AllRows]) in Expanded
Fab117
3 years agoHelper IV
Hi AjithPrasath,
Thank you for taking time looking at my challenge.
I tried your formulae. However,
1. Error
I got an error message
2. Link with [ProjectId]
I'm not sure to see in your formulae where it's specified that we are looking the min [MilestoneNumberNotCompleted] for each different [ProjectId]
Have a nice day
Fab
AjithPrasath
3 years agoResolver II
Hi Fab117 ,
Use this query to create new column . Will Work for Sure.
CurrentMilestone = IF(TasksAndTaskBaselines[MilestoneNumberNotCompleted] = CALCULATE(MIN(TasksAndTaskBaselines[MilestoneNumberNotCompleted]),
ALLEXCEPT(TasksAndTaskBaselines, TasksAndTaskBaselines[ProjectId])), TasksAndTaskBaselines[MilestoneNumberNotCompleted], Blank())
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
- Fab1173 years agoHelper IV
Thank you very much spending additional time for me.
In-between, I worked on latimeria proposal and was able to solve my challenge.
Have a nice end of the day.
Fab