cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fab117
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION
latimeria
Solution Specialist
Solution Specialist

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

 

latimeria_0-1686171941879.png

 

View solution in original post

6 REPLIES 6
AjithPrasath
Resolver II
Resolver II

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

Error message 1.pngError message 2.png

 

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

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 👍

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
Solution Specialist
Solution Specialist

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

 

latimeria_0-1686171941879.png

 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors