Laatste aankoopdatum per product in Power Query

[HWV]

Goedemiddag,

Op mijn vorige vraag heb ik een antwoord van JEC. gekregen, die het goed doet in Power Query om de laatste datum te krijgen.

https://www.helpmij.nl/forum/showthread.php/967531-PQ-weergeven-laatste-prijs-aan-de-hand-van-datum

Nu wil ik dit toepassen in een ander bestand, maar krijg het niet voor elkaar om de juiste gegevens te krijgen.
Van de tabel wil ik per "ArticleCode" de laatste "ShipmentDate" weten.
Ik had de code aangepast van JEC, maar dan gaf hij niet de laatste datum en een foutmelding op datum.
Plus dat we maar een paar kolommen zien, en ik zou de gehele rij in de tabel willen behouden.
Ik zie niet wat ik fout doet, dus alle hulp is welkom.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    delCols = Table.SelectColumns(Source,{"ArticleCode", "Article", "Amount", "ShipmentDate", "QuantityOrdered"}),
    grp = Table.Group(delCols, {"ArticleCode", "Article"}, {{"Amount", each  let tbl =_ in Table.SelectRows(tbl, each [ShipmentDate] = List.Max(tbl[ShipmentDate]))}}),
    exp = Table.ExpandTableColumn(grp, "Amount", {"Amount", "ShipmentDate", "QuantityOrdered"}),
    #"Geëxtraheerde datum" = Table.TransformColumns(exp,{{"ShipmentDate", DateTime.Date, type date}}),
    #"Rijen gegroepeerd" = Table.Group(#"Geëxtraheerde datum", {"ArticleCode", "Article", "Amount", "ShipmentDate"}, {{"QuantityOrdered", each List.Sum([QuantityOrdered]), type number}})
in
    #"Rijen gegroepeerd"

 

[JEC.]

Probeer het eens met deze. Volgens mij klopt de QuantityOrdered van artikel 3310014 niet in je voorbeeldje

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)
in
    out

 

[snb]

Ga in de tabel op de productcode staan en gebruik:

Sub M_snb()
   With Sheet1.ListObjects(1).DataBodyRange
     .AutoFilter 5, ActiveCell
     .AutoFilter 2, Format(Application.Subtotal(104, .Columns(2)), "dd/mm/yyyy")
    End With
End Sub

 

[HWV]

Dank je JEC, het is weer gelukt

SNB, dit is een onderdeel van een groter geheel wat ik eigenlijk zonder VBA wil bereiken.
Ik kom hier weer een stap dichter bij mijn doel wat ik wil bereiken.

Dank

Henk:thumb:

 

[snb]

Het is efficiënter een database intact te laten en gebruik te maken van de ingebouwde faciliteiten om daaruit selekties te maken, dan een programmeertaal (powerquery) te gebruiken om gegevens dubbel in een werkblad te zetten.

 

[JEC.]

Vaak gaat het om import van gegevens vanuit een andere bron. Dan is het gebruik van Power Query zeer effectief/efficiënt

 

[snb]

Dat is hier niet het geval.

 

[JEC.]

Wie zegt dat

 

[HWV]

Klopt idd, ik haal de gegevens uit onze data warehouse via sql. Op deze manier is PQ voor mij de beste optie.

Het voordeel is ook dat het bestand niet al te groot wordt met PQ, wat ik nu wel heb en die ik nu aan het ombouwen ben.

Groet Henk

 

[JEC.]

Effectief dus!:thumb:

 

[HWV]

Nog een aanvullende vraag

In het voorbeeld bestand heb ik nu 4 tabelen staan.

Tabel 1, Orginele data inkoop gegevens met aantal en data
Tabel 2, Unieke data via PQ over tabel 1 met laatste inkoopdatum en prijs
Tabel 3, Artikelgegevens die ik normaal gesproken via SQL naar PQ haal
Tabel 4, de uitkomst van samenvoegen tabel 3 en 4

Hier zit ook gelijk mijn probleem.
In het voorbeeld bestand gaat het goed het samenvoegen van tabel 3 en 4, hier komen de juiste gegevens uit.
Enkel als ik mijn artikelbestand via SQL naar PQ haalt, dan slaat hij artikelnummers over.
We gebruiken artikelnummers, zoals ALDI5215, 1151440, 3310014, EMB-0005 enz dus tekst en getal doorelkaar.

Hoe kan ik er voor zorgen dat ik via samenvoegen wel kan samenvoegen met getallen en tekst.

Henk

 

[JEC.]

Als ik het zo lees, dien je de kolommen van beide tabellen (artikelcodes) om te zetten naar tekst voordat je ze gaat mergen.

 

[HWV]

JEC.

Dit heb ik idd zo ingesteld, dat beide kolomen die moeten matchen dat die op tekst staat.
Toen dat niet lukte had ik any.type gebruikt voor deze kolomen maar met het zelfde resultaat.
Als ik nu haar had, had ik met mijn handen erin gezeten, want ik zou niet weten waarom dit niet werkt.

Henk

 

[JEC.]

Moeilijk te zeggen...

 

[HWV]

Die begrijp ik, omdat ik niet echt een voorbeeld kan laten zien. Dit omdat dit data is die ik via sql binnen haal.
Ik heb nog wel ingesteld dat als er tekst dat deze dan in hoofdletters gezet wordt. Ik krijg dan meer hits, maar ook dubbele waarde’s dus bv Aldi5215 twee regels op met twee verschillende order nummers en verschillende datums. Dus zal ook nog naar de eerste bewerking moeten kijken waarom er dubbele waarde’s in staan.

Morgen maar weer verder puzzelen.
Bedankt voor de reactie

 

[HWV]

Hoi JEC.

Zoals gisteren aangeven had ik ook nog dubbele lijnen in de unieke data.
In de bijlage heb ik een voorbeeld waar hij dubbele regels meeneemt, en die bij mij uiteindelijk problemen geef in de eind data.

Misschien kan jij hier wel in helpen, en misschien kijken wat ik nog verkeerd doet.

Alvast dank

Henk

 

[peter59]

Hallo,

Als ik het zo zie zijn het toch 2 verschillende art. codes?
ALDI5213 en ALDI5313. Of zie ik iets over het hoofd?

 

[HWV]

Verkeerde bestand gebruikt ;-(

Beste,

Ik zie het ook, het verkeerde bestand geupload
Zie de juiste bijlage dat hij dubbele meeneemt.

Komt waarschijnlijk dat het aantal in de deze inkooporder op twee regels staan, met wel het zelfde artikelnummer maar met verschillende aantallen.
Is het mogelijk dat ik dan enkel deze lijnen die dubbel zijn bij elkaar laat optellen.
Anders kom ik natuurlijk niet aan het aantal wat het laatste is gekocht, wel de laatst betaalde prijs en laatste datum van aankoop.

Groet Henk

 

[peter59]

Komt de bijlage nu een klein beetje in de beurt?

 

[HWV]

Ziet er goed uit

Hoi Peter59,

Dit ziet er goed uit en heb even mijn orginele data in gezet, en zo lijkt het id goed te gaan.
Eigenlijk zou ik wel de gehele rij willen bewaren is dat mogelijk.
Ik zie ook dat er een kolom extra is toegevoegd, is dit ook met een rede gebeurd!
Na shipdate is er een lege kolom bij gekomen.

Groet Henk