How to create a columns with range of numbers from values in rows

[Anonymous]

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 · Accepted solution]

@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.

[tamerj1 · Accepted solution]

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]

Also works like a charm! Thanks!

[SpartaBI]

@tamerj1 what am I missing here? We did the same thing including the "-1". You also added the test for blank but besides that it's the same. How did @Anonymous got the result he did with my code?

[tamerj1]

@SpartaBI 

Because his column is text type. 

[SpartaBI]

@tamerj1 I see, but how did you know that btw? He didn't share a screenshot of the model. Is it because you saw the abc 123 in Power query? 

Anyway, he should have accepted my solution also, especially that I was first 😜

[tamerj1]

@SpartaBI 
Actually not from power query. I knew from this: The numbers are aligned left like text 

And you are right. I should have been marked for your answer.

[SpartaBI]

Nice catch! 💪

[Anonymous · Accepted solution]

@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]

Worked perfectly! The Each Try did the trick!

Thanks Mohan!

[SpartaBI]

@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.

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

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?

[tamerj1]

Hi @Anonymous 
except of the "-1",  @SpartaBI's solution is correct you just need to set the data type of the RowNumber column to intiger from power query.

[tamerj1]

Hi @Anonymous 
That would be much easier using DAX. If you are interested in DAX calculated columns solution please share the same sample dat as copy/paste to prepare a sample file for you. Thank you

[Anonymous]

RowNumberTask

2	Anchor Milestones for Q50 Community
161	Anchor Milestones for Agilysys Community
322	Infrastructure: Core
654	Infrastructure: Network
699	Infrastructure: Service Desk
791	Infrastructure: Technical Operations Center (MTOC)
836	Travel Innovation Platform (TIP)
1178	BTO: Change Management
1180	BTO: Deployment
1182	BTO: Learning
1184	BTO: UX/UI, XD, Business Process
1186	CRS
1632	Revised ACRS
1734	Data Analytics & Reporting
1817	Data Management
1878	Data Migration
2490	Impacted System - Digital: Comms
2573	Impacted System - Digital: On-Site Digital (OSD)
2615	Impacted System - Digital: Property & Product
2679	Impacted System - Digital: Recognition
2713	Impacted System - Digital: Search
2747	Impacted System - Digital: Shop / Book
2780	Impacted System - Digital: Group
2814	Impacted System - Distribution
3205	Impacted System - Empower Guest Services (GXP, CEC, ARM)
3404	Impacted System - Finance
3529	Impacted System - Group Inventory Portal
3538	Impacted System - Hotel Ops: Integrations
4491	Impacted System - Loyalty Earn
4518	Impacted System - Loyalty: Redeem
4536	Impacted System - Loyalty: Member Benefits
4554	Impacted System - Loyalty: Loyalty Platform
4571	Impacted System - Loyalty: Risk Management, Reporting & Controls
4593	Impacted System - Loyalty: Funding & Reconciliation
4616	Impacted System - Order Domain
4785	Impacted System - Payments
4872	Impacted System - Reservations
5045	Impacted System - Revenue Mgmt: HPP
5107	Impacted System - Revenue Mgmt: MarRFP, Pgoos
5171	Impacted System - Revenue Mgmt: OY
5307	Impacted System - Revenue Mgmt: TY
5386	Impacted System - Sales: Associate Facing Reservation App Phase 1
5483	Impacted System - Sales: Associate Facing Reservation App Phase 2
5591	Impacted System - Sales: CEC/Property
5603	Impacted System - Sales: Corp
5723	Impacted System - Sales: Group
5873	Impacted System - Sales: Omaha Billing
5898	Impacted System - Sales: OSCAR
5935	Privacy
5964	Quality Engineering
6010	Security
6093	Technical Release
6223	Agilysys (Vendor Plan)
6257	APMS (Vendor Plan)
6291	Auto-Room Assignments (ARA)
6325	Opera Cloud (Vendor Plan)
6359	Point-of-Sale (POS)
6393	Single Pane of Glass (SPOG)