March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
User | Count |
---|---|
62 | |
55 | |
27 | |
17 | |
11 |