• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Laatste aankoopdatum per product in Power Query

  • Onderwerp starter Onderwerp starter HWV
  • Startdatum Startdatum
Status
Niet open voor verdere reacties.
Hallo,

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]
 

Bijlagen

Hoi Peter,

Dank voor je reactie.
Ik ga kijken hoe dit werkt, en of ik dit zo in mijn orginele bestand kan verwerken.
Zoals ik nu ziet kan ik hier zeker verder op bouwen.

Groet Henk
 
Heel lang geleden was het al mogelijk in Excel querytables op te nemen.
Die konden betrekking hebben op interne en externe gegevens.
In het voorbeeld:

- staan de basisgegevens in sheet1
- wordt eerst een tabel in sheet2 gemaakt met per artikelcode de laatste besteldatum
- wordt een tweede tabel in sheet3 gemaakt die het totaal aantal bestellingen en geleverde zaken per artikelcode op de laatste besteldatum bevat
- de derde tabel in sheet4 haalt uit de oorspronkelijke gegevenstabel alle overige gewenste velden bij de gegevens van artikelcode en laatste besteldatum

Dit is de code:
Code:
Sub M_snb()
  c00 = ThisWorkbook.FullName
  c01 = Replace(c00, Dir(c00), "")
  c01 = "ODBC;DSN=Excel_xlsb;DBQ=" & c00 & ";DefaultDir=" & c01 & ";DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
    
  For j = 0 To 2
    Set it = Array(Sheet2, Sheet3, Sheet4)(j)
    With it.ListObjects.Add(0, c01, , , it.Cells(1)).QueryTable
      c02 = "SELECT A.ArticleCode, Max(A.ShipmentDate) as maxdate FROM [Blad1$] A Group BY A.ArticleCode"
      If j = 1 Then c02 = "SELECT  A.ArticleCode, sum(A.Amount) as amount, sum(A.QuantityOrdered) as quantity FROM [Blad1$] A , [Blad2$] B WHERE A.ArticleCode = B.ArticleCode AND A.ShipmentDate = B.maxdate Group BY A.ArticleCode"
      If j = 2 Then c02 = "SELECT DISTINCT A.ArticleCode, A.ShipmentDate, A.VendorCode, A.Vendor, A.Article, C.amount, C.quantity FROM [Blad1$] A , [Blad2$] B, [Blad3$] C  WHERE A.ArticleCode = B.ArticleCode AND A.ShipmentDate = B.maxdate AND A.ArticleCode = C.ArticleCode"
      .CommandText = c02
      .Refresh False
      If j = 2 Then .ListObject.DataBodyRange.Columns(6).Resize(, 2).NumberFormat = "0"
    End With
  Next
End Sub

De tabellen hoeven slechts 1 keer gemaakt te worden (m.a.w. de macro hoeft slechts eenmaal uitgevoerd te worden).
Zodra de gegevens in Sheet1 inhoudelijk wijzigen kunnen de overige tabellen eenvoudig ververst worden.

PS. Wanneer er een ongefilterde koppeling naar externe gegevens wordt gelegd kan met VBA (met gebruik van een dictionary) de gewenste tabel in een vloek en een zucht worden geproduceeerd.

Code:
Sub M_snb_000()
  sn = Sheet1.ListObjects(1).DataBodyRange
  
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
      .Item(sn(j, 5)) = Application.Max(sn(j, 2), .Item(sn(j, 5)))
    Next
    For j = 1 To UBound(sn)
      If IsArray(.Item(sn(j, 5))) Then
         sp = .Item(sn(j, 5))
         sp(4) = sp(4) + sn(j, 7)
         sp(5) = sp(5) + sn(j, 14)
         .Item(sn(j, 5)) = sp
      ElseIf .Item(sn(j, 5)) = sn(j, 2) Then
        .Item(sn(j, 5)) = Array(sn(j, 5), sn(j, 2), sn(j, 3), sn(j, 6), sn(j, 7), sn(j, 14))
      End If
    Next
    
    Sheet1.Cells(36, 1).Resize(.Count, 6) = Application.Index(.items, 0, 0)
  End With
End Sub
 

Bijlagen

Laatst bewerkt:
@snb, mooi gedaan:thumb:

Het gebruiksgemak komt naar mijn mening niet in de buurt van de Query die je kunt maken in Power Query, wat logisch is na een x aantal jaren ontwikkelingen.
De output kan je met een paar klikken bereiken, zonder kennis te hebben van programmeren. Werk je er wel eens mee?

Voor deze oplossing moet je eerst de connection string zien te vinden (als je niet weet waar je moet zoeken kan het een flinke zoektocht zijn) + je hebt kennis van SQL nodig.
 
Laatst bewerkt:
@JEC

Je kunt dit eenvoudig met de macrorecorder opnemen.
SQL-kennis is niet vereist
Als je wil stroomlijnen iets meer kennis nodig.
De eenvoud van Powerquery blijkt niet uit de lengte van deze draad.
 
@SNB

De eenvoud van Powerquery blijkt niet uit de lengte van deze draad.

Klopt!!
Het blijkt dat TS voortdurend met verschillende bestanden (soms zelfs verkeerde) en diverse formaten komt, wat zowel voor Power Query als VBA problematisch wordt.
 
De SQL regels voor de gewenste output komen niet uit de recorder rollen
 
Dat valt veel mee met de SQL-wizard.
Maak maar eens een querytable en bekijk de eigenschappen.
VBA-aanpak toegevoegd in vorige post.
 
Laatst bewerkt:
Daar wordt slechts een import Query gegeven. Groeperen/linken en filteren moet je zelf doen
Office 365:

Code:
=LET(
   r;T_1;
   d;INDEX(r;;5);
   u;UNIQUE(d);
   m;MAXIFS(INDEX(r;;2);d;u);
   qty;SUMIFS(INDEX(r;;7);d;u);
   v;SUMIFS(INDEX(r;;14);d;u);
   t;INDEX(r;MATCH(u;d;0);{3\6});
   HSTACK(u;m;t;qty;v)
)
 
Goedemorgen,

Bedankt allemaal voor de input, maar kan dit allemaal niet meer volgen dit gaat mij een brug te ver.
Wat ik tot nu toe heb, werkt voor mij en zal zeker nog geoptimalisseerd kunnen worden.
Maar voor nu voldoet het, en is mijn bestand van 3,5 mb naar 419kb gegaan dus mijn doel is al bereikt.

:thumb: Henk :thumb:
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan