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 ...
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

     

     

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