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
thanks
roykana
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