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
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
