peter59
Terugkerende gebruiker
- Lid geworden
- 21 mei 2007
- Berichten
- 2.683
- Besturingssysteem
- Windows 11
- Office versie
- Office 365
Hallo,
Komt dit in de beurt? Zie bijlage.
De oranje letters zijn er in de query van JEC bijgekomen.
Komt dit in de beurt? Zie bijlage.
De oranje letters zijn er in de query van JEC bijgekomen.
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
cType = Table.TransformColumnTypes(Source,{{"ShipmentDate", type date}}),
cols = Table.ColumnNames(cType),
grp = Table.Group(cType, {"ArticleCode"}, {{"Amount", each let tbl =_ in Table.SelectRows(tbl, each [ShipmentDate] = List.Max(tbl[ShipmentDate]))}}),
delCol = Table.RemoveColumns(grp,{"ArticleCode"}),
out = Table.ExpandTableColumn(delCol, "Amount", cols, cols),
[COLOR="#FFA500"]Rijengegroepeerd = Table.Group(out, {"ShipmentDate", "ArticleCode", "VendorCode", "Vendor", "Article"}, {{"Total QuantityOrdered", each List.Sum([QuantityOrdered]), type number}, {"Total Amount", each List.Sum([Amount]), type number}, {"Total QuantityReceivedCalc", each List.Sum([QuantityReceivedCalc]), type number}, {"Total QuantityToReceiveCalc", each List.Sum([QuantityToReceiveCalc]), type number}})[/COLOR]
in
[COLOR="#FFA500"]Rijengegroepeerd[/COLOR]