script Power Query:
let
Source = Table.Combine({FAT_SAP_Lista_3, FAT_SAP_Lista_4}),
Step1 = Table.Group(Source, {"CUSTOMER"}, {{"MaxDate", each List.Max([DATEFROM]), type datetime}}),
Step2 = Table.Join(Source, {"CUSTOMER", "DATEFROM"}, Step1, {"CUSTOMER", "MaxDate"}),
Step3 = Table.SelectColumns(Step2,{"Source", "SHIP_COND", "KNVAL", "DATEFROM", "DATETO", "/BIC/ZMATERIAL", "CUSTOMER", "KNART","SALES_GRP"})
in
Step3
==============================
script SQL New:
WITH Source AS ( SELECT * FROM ( SELECT * FROM FAT_SAP_Lista_3 UNION ALL SELECT * FROM FAT_SAP_Lista_4 ) AS combined ), Step1 AS ( SELECT CUSTOMER, MAX(DATEFROM) AS MaxDate FROM Source GROUP BY CUSTOMER ), Step2 AS ( SELECT s.* FROM Source s INNER JOIN Step1 ON s.CUSTOMER = Step1.CUSTOMER AND s.DATEFROM = Step1.MaxDate ), Step3 AS ( SELECT Source, SHIP_COND, KNVAL, DATEFROM, DATETO, /BIC/ZMATERIAL, CUSTOMER, KNART, SALES_GRP FROM Step2 ) SELECT * FROM Step3;
==============================
script SQL Old:
