Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 |
---|---|
25 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
44 | |
27 | |
21 | |
16 | |
12 |