Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |