Hi Everyone,
So, what I need to do is create for each row, a range of numbers that considers the first value of the row, and the value in the next row.
For example:
1- For the Anchor Milestone for Q50 Community Task, I need to have two new columns that are "Range Start" and "Range End", which would be 2 for start, and 160 for end.
2- For the Anchor Milestone for Agilsysy Community it would be Range Start 161, and Range End 321.
Etc.
I need to do this, so I can identify then in another table where I have several other Tasks which has their own ID (which dynamically changes) under which "main task" they fall under.
Any help is much appreciated,
Regards,
@Anonymous try using power query as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUQpJLM42VIrViVYyNDOE8o3AfGMjmLwxmG9mCuObKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowNumber = _t, Task = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RowNumber", Int64.Type}, {"Task", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"RowNumber", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Range End", each try #"Added Index" [RowNumber] { [Index] + 1 }-1 otherwise null)
in
#"Added Custom"
Accept the solution if that works for you.
Thanks,
Mohan V.
Hi @Anonymous
Here is a sample file with the solution https://we.tl/t-BQUxx4q6jq
Range End =
VAR PreviousStart =
MINX (
FILTER ( Data, Data[RowNumber] > EARLIER ( Data[RowNumber] ) ),
Data[RowNumber]
)
RETURN
IF (
NOT ISBLANK ( PreviousStart ),
PreviousStart - 1
)Hi @Anonymous
Here is a sample file with the solution https://we.tl/t-BQUxx4q6jq
Range End =
VAR PreviousStart =
MINX (
FILTER ( Data, Data[RowNumber] > EARLIER ( Data[RowNumber] ) ),
Data[RowNumber]
)
RETURN
IF (
NOT ISBLANK ( PreviousStart ),
PreviousStart - 1
)
@Anonymous try using power query as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUQpJLM42VIrViVYyNDOE8o3AfGMjmLwxmG9mCuObKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowNumber = _t, Task = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RowNumber", Int64.Type}, {"Task", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"RowNumber", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Range End", each try #"Added Index" [RowNumber] { [Index] + 1 }-1 otherwise null)
in
#"Added Custom"Accept the solution if that works for you.
Thanks,
Mohan V.
@Anonymous if you want it in DAX so you can add in the model this caclulated column for range end:
Range End =
MINX(
FILTER(
'Table',
'Table'[RowNumber] > EARILER('Table'[RowNumber])
),
'Table'[RowNumber]
) - 1
For range stat it's just equal to the value you already have in the row number.
Hi SpartaBI,
Thanks for your support. I tried the calculated DAX column you provided, but the result is not the correct. Am I doing something wrong?
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
