Need Hellp in Multiple Dates

[Santosh080]

Hello Seniors,

I have excel sheet like this,

Book1
	A	B	C
1	Account No.	Deposit DATE	Amount
2	112233	18-08-2017	50000
3	223344	26-10-2016	28600
4	112233	09-02-2017	81500
5	112233	23-10-2017	68000
6	223344	16-08-2022	7000
7	334455	15-07-2019	10000
8	334455	09-02-2022	8500
9	223344	10-07-2019	10000
10	334455	30-12-2020	9000
11	112233	14-11-2022	9000
12	445566	14-01-2022	50000
Sheet10

Here one account holder deposited in diffrent dates. I want to extract data to other sheet like this,

Book1
	A	B	C
1	Account No.	Deposit DATE	Amount
2	112233	14-11-2022	9000
3	223344	16-08-2022	7000
4	334455	09-02-2022	8500
5	445566	14-01-2022	50000
Sheet10

you can see that i need only new deposit date with amount for multiple deposit by account holders with other single deposit by account holder. Please help how can do this.

Thanks

 

[alansidman]

Book2
	A	B	C	D	E	F	G
1	Account No.	Deposit DATE	Amount		Account No.	Last Date Amount	Table1 (2).Amount
2	112233	8/18/2017	50000		223344	8/16/2022	7000
3	223344	10/26/2016	28600		334455	2/9/2022	8500
4	112233	2/9/2017	81500		112233	11/14/2022	9000
5	112233	10/23/2017	68000		445566	1/14/2022	50000
6	223344	8/16/2022	7000
7	334455	7/15/2019	10000
8	334455	2/9/2022	8500
9	223344	7/10/2019	10000
10	334455	12/30/2020	9000
11	112233	11/14/2022	9000
12	445566	1/14/2022	50000
Sheet1

Load your table to Power query and Group on the Account No for Max Date

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Account No."}, {{"Last Date Amount", each List.Max([Deposit DATE]), type datetime}})
in
    #"Grouped Rows"

Duplicate the original table and then merge it back on the above query.

let
    Source = Table.NestedJoin(Table1, {"Account No.", "Last Date Amount"}, #"Table1 (2)", {"Account No.", "Deposit DATE"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Amount"}, {"Table1 (2).Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table1 (2)",{{"Last Date Amount", type date}})
in
    #"Changed Type"