Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
In my table [TasksAndTaskBaselines (2)], I've the fields:
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
Solved! Go to 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
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
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
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
11 | |
7 | |
6 | |
6 |