Forum Discussion
Custom column with filter on Min value
- 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
Hi,
You can add the below for creating custom column CurrentMilestone :
CurrentMilestone = if [MilestoneNumberNotCompleted] = List.Min(List.Select([MilestoneNumberNotCompleted], each _ <> null)) then [MilestoneNumberNotCompleted] else null
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
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
- AjithPrasath3 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