Forum Discussion

Fab117's avatar
Fab117
Helper IV
3 years ago
Solved

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 ...
  • latimeria's avatar
    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