Bring the date of the next row

[Jose Fonyat]

Dear members, good evening !!

Let me know if you can help me with this problem:

My table is like the one below:

Date	Project	NumContract	CustomerName	TypeContract	Points	Amount	BalancePoints	Status
01/01/2017	4	10	Alan James	150000 Pts	150.000	5.000	100.000	Active
31/01/2017	3	15	Bill Smith	300.000 Pts	300.000	10.000	250.000	Reverted
10/02/2017	2	16	Bill Smith	150.000 Pts	150.000	5.000	50.000	Active
15/02/2017	1	21	John Bradley	150.000 Pts	150.000	5.000	80.000	Reverted
28/02/2017	5	27	John Bradley	75.000 Pts	75.000	2.500	55.000	Reverted
01/03/2017	6	28	John Bradley	30.000 Pts	30.000	6.500	30.000	Active

My goal is to add a new conditional column and insert a date according to the following conditions:

1. the Customer Name appears at least in two or more records (repeated)
2. The date to be inserted is the date of the next record

Date	Project	NumContract	CustomerName	TypeContract	Points	Amount	BalancePoints	Status	Date Reverted
01/01/2017	4	10	Alan James	150000 Pts	150.000	5.000	100.000	Active	null
31/01/2017	3	15	Bill Smith	300.000 Pts	300.000	10.000	250.000	Reverted	31/01/2017
10/02/2017	2	16	Bill Smith	150.000 Pts	150.000	5.000	50.000	Active	null
15/02/2017	1	21	John Bradley	150.000 Pts	150.000	5.000	80.000	Reverted	28/02/2017
28/02/2017	5	27	John Bradley	75.000 Pts	75.000	2.500	55.000	Reverted	01/03/2017
01/03/2017	6	28	John Bradley	30.000 Pts	30.000	6.500	30.000	Active	null

Thanks for the support
Best Regards

 

[Rudi]

​

Does this work for you?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Date Reverted", each if [CustomerName] = #"Added Index"{[Index] + 1}[CustomerName] then #"Added Index"{[Index] + 1}[Date] else null)
in
    #"Added Conditional Column"

It produces the following:
Note, you had Bill Smith's value at 31/01/2017, but I assume that's a misprint and you want the next value which is 10/02/2017?

Date	Project	NumContract	CustomerName	TypeContract	Points	Amount	BalancePoints	Status	Index	Date Reverted
01/01/2017	4	10	Alan James	150000 Pts	150	5	100	Active	0
31/01/2017	3	15	Bill Smith	300.000 Pts	300	10	250	Reverted	1	10/02/2017
10/02/2017	2	16	Bill Smith	150.000 Pts	150	5	50	Active	2
15/02/2017	1	21	John Bradley	150.000 Pts	150	5	80	Reverted	3	28/02/2017
28/02/2017	5	27	John Bradley	75.000 Pts	75	2.5	55	Reverted	4	01/03/2017
01/03/2017	6	28	John Bradley	30.000 Pts	30	6.5	30	Active	5

BTW: I don't have it in the M-Code, but I'd recommend to add a sort step before the conditional column step, else you'd get undesired results!

 

[Jose Fonyat]

Thanks a lot Rudi!

The code works fine..

Best Regards