Custom column with filter on Min value

[Fab117]

Hello,

In my table [TasksAndTaskBaselines (2)], I've the fields:

• [ProjectId] (Text)
• [MilestoneNumberNotCompleted] (Integer).

Example :

ProjectId	MilestoneNumberNotCompleted
123	null
123	2
123	5
123	null
123	null
123	7
123	3
123	1
123	null
456	4
456	null
456	2
456	null
456	7
456	8

I'd like to create in Power Query a Custom Column :

CurrentMilestone =

IF for the same [ProjectId], [MilestoneNumberNotCompleted] = Min [MilestoneNumberNotCompleted] then

[CurrentMilestone] = [MilestoneNumberNotCompleted]

Else null

In upper example, result would be:

ProjectId	MilestoneNumberNotCompleted	CurrentMilestone
123	null	null
123	2	null
123	5	null
123	null	null
123	null	null
123	7	null
123	3	null
123	1	1
123	null	null
456	4	null
456	null	null
456	2	2
456	null	null
456	7	null
456	8	null

Do someone now the formulae I should apply?

Thank in advanced.

Fab

[AjithPrasath]

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 👍

[Fab117]

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]

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 👍

[Fab117]

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

[latimeria · Accepted solution]

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]

Hi @latimeria ,

Thank you spending time on my issue.

I started with other proposed solution, because I'm not familiar with advanced editor (M language).

However, by creating a new query with your code and looking at the different steps, I've been able to replicate to my case.

Big thanks for the solution for sure, but also to push me looking an area I'm not familiar with.

Fab