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

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

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

     

     

6 Replies

  • latimeria's avatar
    latimeria
    Solution Specialist
    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's avatar
      Fab117
      Helper IV
      3 years ago

      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

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    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's avatar
      Fab117
      Helper IV
      3 years ago

      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's avatar
        AjithPrasath
        Resolver II
        3 years ago

        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 👍