LOOKUP(2,1 WITH POWER QUERY

[roykana]

Dear all master,

I want to bring up the date and last value of the transaction with the power query.

there are hundreds of thousands of records if I use the formula to make it very slow.please solution with power query

LOOKUP 2,1 WITH POWER QUERY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	PNM	ITM	ITC	UNI	QTY	CIU	NOD	BLC	BRAND	OPTION	DATE	SAC	ITEMDISCPC	CHECK DATE	CHECK ITEMDISCPC	status
2	1000	TESTLA R 1000	1000	Pcs	1	40000	01	40,000	TESTLA	YES	04-01-22	A.03.01.029.052	10
3	1001	TESTLU R 1001	1001	Pcs	1	40000	02	40,000	TESTLU	YES	05-01-22	A.03.01.029.052	20
4	1002	TESTLA R 1002	1002	Pcs	1	45000	03	45,000	TESTLA	YES	06-01-22	A.03.01.029.052	15	04/01/2022	10	ns
5	1003	TESTLU R 1003	1003	Pcs	1	60000	04	60,000	TESTLU	YES	07-01-22	A.03.01.029.052	10	05/01/2022	20	ns
6	1004	TESTLA R 1004	1004	Pcs	1	70000	05	70,000	TESTLA	YES	08-01-22	A.03.01.029.052	15	06/01/2022	15	s
DB

Cell Formulas
Range	Formula
N2:N6	N2	=IFERROR(LOOKUP(2,1/([SAC]=$L2)/([BRAND]=$I2)/([DATE]<$K2),[DATE]),"")
O2:O6	O2	=IFERROR(LOOKUP(2,1/([SAC]=$L2)/([BRAND]=$I2)/([DATE]<$K2),[ITEMDISCPC]),"")
P2:P6	P2	=IF([@[CHECK ITEMDISCPC]]="","",IF([@ITEMDISCPC]=[@[CHECK ITEMDISCPC]],"s","ns"))

thanks
roykana

 

[Bo_Ry]

Please try

1 NestedJoin

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    MergedQueries = Table.NestedJoin(Source, {"BRAND", "SAC"}, Table1, {"BRAND", "SAC"}, "Table1", JoinKind.LeftOuter),
    Last = Table.AddColumn(MergedQueries, "Last", each Table.Last(Table.SelectRows([Table1], (x)=> x[DATE] < [DATE]))),
    ExpLast = Table.ExpandRecordColumn(Last, "Last", {"DATE", "ITEMDISCPC"}, {"C_DATE", "C_ITEMDISCPC"}),
    AStatus = Table.AddColumn(ExpLast, "C_Status", each if [C_ITEMDISCPC] = null then null else if [C_ITEMDISCPC]=[ITEMDISCPC] then "s" else "ns")
in
    AStatus

2 SelectRows

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Latest = Table.AddColumn(Source, "Last", each    Table.Last(Table.SelectRows(Source, (s)=> s[SAC]=[SAC] and s[BRAND]=[BRAND] and s[DATE]<[DATE] ) )),
    ExpLast = Table.ExpandRecordColumn(Latest, "Last", {"DATE", "ITEMDISCPC"}, {"C_DATE", "C_ITEMDISCPC"}),
    AStatus = Table.AddColumn(ExpLast, "C_Status", each if [C_ITEMDISCPC] = null then null else if [C_ITEMDISCPC]=[ITEMDISCPC] then "s" else "ns")
in
    AStatus

Not sure which one is faster, my guess is NestedJoin.

 

[roykana]

Please try

1 NestedJoin

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    MergedQueries = Table.NestedJoin(Source, {"BRAND", "SAC"}, Table1, {"BRAND", "SAC"}, "Table1", JoinKind.LeftOuter),
    Last = Table.AddColumn(MergedQueries, "Last", each Table.Last(Table.SelectRows([Table1], (x)=> x[DATE] < [DATE]))),
    ExpLast = Table.ExpandRecordColumn(Last, "Last", {"DATE", "ITEMDISCPC"}, {"C_DATE", "C_ITEMDISCPC"}),
    AStatus = Table.AddColumn(ExpLast, "C_Status", each if [C_ITEMDISCPC] = null then null else if [C_ITEMDISCPC]=[ITEMDISCPC] then "s" else "ns")
in
    AStatus

2 SelectRows

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Latest = Table.AddColumn(Source, "Last", each    Table.Last(Table.SelectRows(Source, (s)=> s[SAC]=[SAC] and s[BRAND]=[BRAND] and s[DATE]<[DATE] ) )),
    ExpLast = Table.ExpandRecordColumn(Latest, "Last", {"DATE", "ITEMDISCPC"}, {"C_DATE", "C_ITEMDISCPC"}),
    AStatus = Table.AddColumn(ExpLast, "C_Status", each if [C_ITEMDISCPC] = null then null else if [C_ITEMDISCPC]=[ITEMDISCPC] then "s" else "ns")
in
    AStatus

Not sure which one is faster, my guess is NestedJoin.

@Bo_Ry
Dear Mr. Bo_Ry ,
Thanks for your reply. I tried power query code from you in my actual data very slowly

thanks
roykana