Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fab117
Helper IV
Helper IV

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors