LuckyTemplates Power Query: dubbele rijen verwijderen

In deze blog laat ik je zien hoe je dubbele rijen in de LuckyTemplates Power Query verwijdert op basis van voorwaarden. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Dit voorbeeld komt uit een vraag die gesteld is op het . Het lid heeft transactiegegevens en wil rijen verwijderen die elkaar opheffen.

Er zijn twee verschillende soorten transacties, één voor inkomend en één voor uitgaand. Ze heeft ook het aantal bewegingen toegevoegd. En op basis daarvan formuleerde ze drie scenario's.

Scenario één is gebaseerd op twee rijen. Als ze elkaar opheffen, moeten beide rijen worden verwijderd. Als dat niet het geval is, moeten beide rijen behouden blijven.

Scenario's twee en drie gaan uit van een combinatie van rijen die elkaar deels opheffen. Die rijen moeten worden verwijderd op basis van first-in-first-out, zodat alleen de laatste inkomende transacties worden bewaard.

Kortom, we herhalen dezelfde logica keer op keer. We zouden in staat moeten zijn om één enkele aanpak te ontwerpen die past bij al deze scenario's.

Enkele van de dingen die we gaan doen, zijn een kolom toevoegen met absolute hoeveelheden en groeperen door "een hoeveelheidsbalanskolom toe te voegen" en een geneste tabel met behulp van de optie "alle rijen". We transformeren ook die geneste tabellen, sorteren het bewegingstype en de boekingsdatum, maken een lopend totaal en behouden alleen rijen op basis van een voorwaarde.

Ik heb dit op een gemiddeld niveau beoordeeld omdat de meeste transformaties die we hier kunnen doen, zijn door de gebruikersinterface te gebruiken. Met dat gezegd, gaan we naar de LuckyTemplates Power Query.

Inhoudsopgave

• Kolom toevoegen met absolute hoeveelheden in de Power Query
• Groeperen op ID-index en absolute hoeveelheid
• Geneste tabellen transformeren in LuckyTemplates Power Query
  • Sorteerbewegingstype en boekingsdatum
  • Een lopend totaal maken
  • Rijen filteren op basis van een voorwaarde
• Conclusie

Kolom toevoegen met absolute hoeveelheden in de Power Query

We beginnen met het toevoegen van een kolom met absolute hoeveelheden. Selecteer de kolom Hoeveelheid en ga vervolgens op het lint of tabblad Kolom toevoegen naar Wetenschappelijk en klik vervolgens op Absolute waarde .

Ik ga deze kolom hernoemen in de formulebalk.

Groeperen op ID-index en absolute hoeveelheid

We kunnen nu groeperen op ID-index en absolute hoeveelheid. Selecteer ID Index , houd vervolgens shift of control ingedrukt om meervoudig te selecteren en klik vervolgens op Abs aantal (absolute hoeveelheid).

Je vindt Groeperen op op het tabblad Transformeren.

Maar je kunt het ook vinden op het tabblad Start.

Klik erop en nu gaan we een nieuwe kolom toevoegen. Het is geen "telling", maar het wordt de "som" van de hoeveelheid. Laten we het dus hernoemen naar Qty (quantity) Balance . Het wordt een som van de hoeveelheidskolom. En dan voegen we nog een aggregatie toe, maar het wordt een speciale, dus we selecteren Alle rijen . Laten we deze kolom ook een naam geven ( AllRows ). Druk vervolgens op OK.

Als we in de witte ruimte in de tabel klikken, zien we een voorbeeld van de geneste tabel hieronder. We zien dat de hoeveelheden voor deze geneste tabel elkaar opheffen en dat het aantalsaldo nul is.

Als we naar de volgende geneste tabel kijken (dus opzij klikken in de witte ruimte van de volgende rij), zien we dat de hoeveelheidskolom elkaar niet opheft en dat een hoeveelheidssaldo groter is dan nul.

Laten we een aangepaste kolom toevoegen, zodat we die later kunnen gebruiken voor onze geneste tabeltransformaties. Selecteer dus Aangepaste kolom toevoegen .

Laten we die kolom hernoemen naar Transformaties en we voegen nul (0) toe als tijdelijke aanduiding. Klik vervolgens op OK.

Geneste tabellen transformeren in LuckyTemplates Power Query

We zijn nu klaar om de logica te maken voor het transformeren van die geneste tabellen. Maar we willen er niet een heleboel schrijven, en dat is ook niet nodig.

We kunnen de gebruikersinterface grotendeels gebruiken. Als we de logica voor die transformaties in een aparte query bouwen, was het meest uitgebreide scenario dat we hadden het scenario met drie of vier rijen.

Als ik nu aan de rechterkant in de laatste rij klik, zie ik dat deze tabel vier rijen heeft. We kunnen dit dus gebruiken om onze transformaties op te bouwen.

Klik met de rechtermuisknop op de zijkant in de witte ruimte en selecteer Toevoegen als nieuwe query .

We zien dat de geneste tabel nu is uitgebreid en is toegevoegd als een nieuwe query en we kunnen deze gebruiken om de logica op te bouwen die we nodig hebben.

Sorteerbewegingstype en boekingsdatum

Nu moeten we eerst sorteren op Bewegingstype . Laten we aflopend kiezen . Zo staan ​​de uitgaande transacties altijd bovenaan.

Vervolgens sorteren we de Boekingsdatum als oplopend , waarbij we ervoor zorgen dat als we rijen weglaten, dit altijd gebeurt volgens het FIFO-principe (first in, first out).

Een lopend totaal maken

Om de rijen te identificeren die we gaan verwijderen, voeg ik een lopend totaal toe om uit te schakelen, en we kunnen een List.FirstN gebruiken voor die taak. Deze functie maakt een lijst aan op basis van een andere lijst, waarbij de topitems worden bijgehouden op basis van een specifiek aantal of op basis van een criterium.

Laten we teruggaan naar onze vraag. We hebben twee dingen nodig voor List.FirstN. Eerst hebben we die lijst nodig, en die lijst is onze kolom Hoeveelheid . Als ik met de rechtermuisknop op de kolomkop Aantal klik, krijgen we de optie Toevoegen als NewQuery .

En hier zien we de code die we nodig hebben om die lijst te genereren. In de toegepaste stap kunt u zien dat deze naar de laatste stap verwijst en vervolgens de kolom tussen die haakjes identificeert.

Het tweede deel dat we nodig hebben voor List.FirstN is een nummer om aan te geven hoeveel nummers we van die lijst moeten houden. Daar kunnen we de index voor gebruiken, dus ik ga een indexkolom toevoegen van één (1) voor deze rij.

Dus voor nummer 1 blijft het nummer op de eerste rij in de kolom Aantal staan, enzovoort. En dat kunnen we nu samenvatten.

Laten we een aangepaste kolom toevoegen en deze Lopend totaal noemen . We gebruiken hier List.FirstN en we kunnen zien dat de eerste parameter die lijst was, de kolom Aantal. We moeten naar de laatste stap wijzen, en die laatste stap is de Toegevoegde Index .

We hebben de kolom geïdentificeerd die we willen, en dat is onze kolom Aantal. Onze telling staat in de kolom Index.

Nu retourneert het een lijst. Als we aan de zijkant in de witte ruimte klikken, kunnen we de inhoud van die lijst zien. Voor het eerste record behield het alleen de bovenste rij van die lijst. Voor het tweede record behield het de bovenste twee rijen.

Nu hoeven we alleen maar deze hoeveelheden op te tellen en we kunnen Lists.Sum gebruiken om dat te doen. Dus ik ga dat toevoegen in de formulebalk. En ik ga ook Type .

Rijen filteren op basis van een voorwaarde

Met dit lopende totaal kunnen we de rijen identificeren die we willen behouden. We willen alleen rijen behouden die groter zijn dan nul, dus laten we een filtervoorwaarde toevoegen.

We kunnen nu onze helperkolommen verwijderen. Selecteer Index en Lopend totaal en klik vervolgens op Kolommen verwijderen.

In de geavanceerde editor kunnen we nu de code kopiëren die we hebben gemaakt. Ik open de geavanceerde editor en we kunnen zien dat dit onze gesplitste stap is. We kunnen dus alles daaronder selecteren en kopiëren.

Laten we nu teruggaan naar onze oorspronkelijke vraag. Open nogmaals de geavanceerde editor. En laten we in plaats van de tijdelijke aanduiding naar een nieuwe regel gaan. Ik ga de " let "-expressie gebruiken omdat die waarden van tussentijdse berekeningen in variabelen kan vastleggen. Dus, "let" en ga dan naar een nieuwe regel en plak mijn transformaties. Ik zal ook Type declareren .

De gemarkeerde code hierboven verwijst naar onze uitgebreide geneste tabel, die in de kolom Alle rijen is geplaatst. Dus daar ga ik naar wijzen door deze gemarkeerde code te vervangen door AllRows .

Dus onze transformatiekolom heeft nu geneste tabellen. We bouwen onze query op basis van de laatste rij, toch? En dat bevatte vier rijen toen we begonnen en bevatte slechts twee. We hebben ook die hoeveelheid gebalanceerd nul, toch? En nu heeft het een lege tafel.

We kunnen deze verwijderen door te filteren op de kolom Hoeveelheidssaldo. We willen niet dat het kwantiteitssaldo gelijk is aan nul.

En dan ga ik mijn transformatiekolom selecteren en andere kolommen verwijderen. Ik kan nu mijn geneste transformatietabellen uitbreiden. Gebruik niet de oorspronkelijke kolomnaam als voorvoegsel en druk op OK.

Selecteer op het tabblad Transformeren Gegevenstype en we zijn klaar. En zo ruim je transactiegegevens op.

Unpivot en Pivot Basics in LuckyTemplates – Query-editor Bekijk
mijn praktijktips bij het gebruik van de LuckyTemplates Advanced Editor
Geavanceerde transformaties in LuckyTemplates

Conclusie

In deze zelfstudie heb ik je laten zien hoe je transactiegegevens kunt opschonen, met name het verwijderen van dubbele rijen. Dit is een geweldige techniek die u kunt gebruiken bij het gebruik van de LuckyTemplates Power Query.

Ik hoop dat je deze leuk vond. Bekijk de volledige video-tutorial hieronder voor meer details. Bekijk ook de links hieronder voor meer gerelateerde inhoud rond LuckyTemplates Power Query Editor.

Proost!

Melissa