LuckyTemplates Power Query: Ta bort dubbletter av rader

I den här bloggen kommer jag att visa dig hur du tar bort dubbletter av rader i LuckyTemplates Power Query baserat på villkor. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Detta exempel är från en fråga som togs upp på. Medlemmen har transaktionsdata och vill ta bort rader som tar bort varandra.

Det finns två distinkta transaktionstyper, en för inkommande och en för utgående. Hon har också lagt till antalet rörelser. Och utifrån det formulerade hon tre scenarier.

Scenario ett är baserat på två rader. Om de tar bort varandra ska båda raderna tas bort. Om så inte är fallet bör båda raderna behållas.

Scenario två och tre är baserade på en kombination av rader, som delvis tar bort varandra. Dessa rader bör tas bort på en först-in-först-ut-basis så att endast de senaste inkommande transaktionerna behålls.

I grund och botten upprepar vi samma logik om och om igen. Vi borde kunna utforma ett enda tillvägagångssätt för att passa alla dessa scenarier.

Några av de saker vi ska göra är att lägga till en kolumn med absoluta kvantiteter och gruppera efter "lägg till en kvantitetsbalanskolumn" och en kapslad tabell med alternativet "alla rader". Vi kommer också att omvandla de kapslade tabellerna, sortera rörelsetypen och postningsdatumet, skapa en löpande summa och bara behålla rader baserat på ett villkor.

Jag har betygsatt detta på en mellannivå eftersom de flesta av de transformationer som vi kan göra här är genom att använda användargränssnittet. Med det sagt, låt oss gå över till LuckyTemplates Power Query.

Innehållsförteckning

• Lägger till kolumn med absoluta kvantiteter i Power Query
• Gruppera efter ID-index & absolut kvantitet
• Transformera kapslade tabeller i LuckyTemplates Power Query
  • Sortering av rörelsetyp & bokföringsdatum
  • Skapa en löpande summa
  • Filtrera rader baserade på ett tillstånd
• Slutsats

Lägger till kolumn med absoluta kvantiteter i Power Query

Vi börjar med att lägga till en kolumn med absoluta kvantiteter. Välj kolumnen Kvantitet och sedan på menyfliksområdet eller fliken Lägg till kolumn , gå till Vetenskapligt och klicka sedan på Absolut värde .

Jag kommer att byta namn på den här kolumnen i formelfältet.

Gruppera efter ID-index & absolut kvantitet

Vi kan nu gruppera efter ID-index och absolut kvantitet. Välj ID-index , håll sedan ned shift eller kontroll för att välja flera gånger och klicka sedan på Abs qty (absolut kvantitet).

Du hittar Gruppera efter på fliken Omvandla.

Men du kan också hitta den på fliken Hem.

Klicka på den och nu ska vi lägga till en ny kolumn. Det är inte en "räkning", men det kommer att vara "summan" av kvantiteten. Så låt oss döpa om det till Kvantitet (kvantitet) Balans . Det kommer att vara summan av kolumnen kvantitet. Sedan lägger vi till ytterligare en sammanställning, men det kommer att bli en speciell sådan, så vi väljer Alla rader . Låt oss också ge den här kolumnen ett namn ( AllRows ). Tryck sedan på OK.

Om vi ​​klickar i det vita utrymmet i tabellen ser vi en förhandsvisning av den kapslade tabellen nedan. Vi ser att kvantiteterna för denna kapslade tabell tar bort varandra och kvantitetsbalansen är noll.

Om vi ​​tittar på nästa kapslade tabell (så klicka åt sidan i det vita utrymmet på nästa rad), ser vi att kvantitetskolumnen inte tar bort varandra och ett kvantitetssaldo är större än noll.

Låt oss lägga till en anpassad kolumn så att vi kan använda den för våra kapslade tabelltransformationer senare. Så välj Lägg till anpassad kolumn .

Låt oss byta namn på den kolumnen tilloch vi lägger till noll (0) som en platshållare. Klicka sedan på OK.

Transformera kapslade tabeller i LuckyTemplates Power Query

Vi är nu redo att skapa logiken för att transformera de kapslade tabellerna. Men vi vill inte skriva ett gäng av dem, och det behöver vi inte.

Vi kan använda användargränssnittet för det mesta. Om vi ​​bygger logiken för dessa transformationer i en separat fråga, var det mest utarbetade scenariot som vi hade det med antingen tre eller fyra rader.

Om jag nu klickar på höger sida i sista raden ser jag att den här tabellen har fyra rader. Så vi kan använda detta för att bygga våra transformationer.

Högerklicka på sidan i det vita utrymmet och välj Lägg till som ny fråga .

Vi ser att den kapslade tabellen nu har utökats och lagts till som en ny fråga och vi kan använda denna för att bygga den logik vi behöver.

Sortering av rörelsetyp & bokföringsdatum

Nu, det första vi behöver göra är att sortera på Movement Type . Låt oss välja fallande . På så sätt kommer de utgående transaktionerna alltid att vara i topp.

Därefter sorterar vi postningsdatumet som stigande , och ser till att om vi utelämnar rader kommer det alltid att göras enligt FIFO-principen (först in först ut).

Skapa en löpande summa

För att identifiera raderna som vi ska ta bort, kommer jag att lägga till en löpande summa för att inaktivera, och vi kan använda en List.FirstN för den uppgiften. Denna funktion skapar en lista baserad på en annan lista, där de översta objekten hålls baserat på ett specifikt nummer eller på ett kriterium.

Låt oss gå tillbaka till vår fråga. Vi behöver två saker för List.FirstN. Först behöver vi den listan, och den listan är vår kvantitetskolumn . Om jag högerklickar på kolumnrubriken Kvantitet får vi alternativet Lägg till som NewQuery .

Och här ser vi koden som vi behöver för att generera den listan. I det tillämpade steget kan du se att det pekar på det sista steget och sedan identifierar kolumnen inom dessa parenteser.

Den andra delen som vi behöver för List.FirstN är ett nummer för att identifiera hur många nummer som ska behållas från den listan. Vi kan använda indexet för det, så jag ska lägga till en indexkolumn från en (1) för den här raden.

Så för nummer 1 kommer det att behålla numret på den första raden i kolumnen Kvantitet och så vidare. Och nu kan vi sammanfatta det.

Låt oss lägga till en anpassad kolumn och kalla den löpande summa . Vi använder List.FirstN här, och vi kan se att den första parametern var den listan, kolumnen Kvantitet. Vi måste peka på det sista steget, och det sista steget är Added Index .

Vi identifierade kolumnen som vi vill ha, och det är vår kvantitetskolumn. Vår räkning finns i kolumnen Index.

Nu returnerar den en lista. Om vi ​​klickar till sidan i det vita utrymmet kan vi se innehållet i den listan. För det första rekordet höll den bara den översta raden från den listan. För det andra rekordet behöll den de två översta raderna.

Nu behöver vi bara summera dessa kvantiteter och vi kan använda Lists.Sum för att göra det. Så jag ska lägga till det i formelfältet. Och jag ska också lägga Type .

Filtrera rader baserade på ett tillstånd

Med denna löpande summa kan vi identifiera de rader som vi vill behålla. Vi vill bara behålla rader som är större än noll, så låt oss lägga till ett filtervillkor.

Vi kan nu ta bort våra hjälpkolumner. Välj Index och löpande total och klicka sedan på Ta bort kolumner.

I Advanced Editor kan vi nu kopiera koden som vi har skapat. Jag öppnar , och vi kan se att detta är vårt delade steg. Så vi kan välja och kopiera allt under det.

Låt oss nu gå tillbaka till vår ursprungliga fråga. Återigen, öppna Advanced Editor. Och istället för platshållaren, låt oss flytta till en ny rad. Jag kommer att använda uttrycket " låt " eftersom det kan fånga värden från mellanliggande beräkningar i variabler. Så, "låt" och gå sedan till en ny rad och klistra in mina transformationer. Jag kommer också att deklarera Typ .

Den markerade koden ovan pekar på vår utökade kapslade tabell, som placeras i kolumnen Alla rader. Så jag ska peka på det genom att ersätta denna markerade kod till AllRows .

Så vår transformationskolumn har nu kapslade tabeller. Vi bygger vår fråga baserat på den sista raden, eller hur? Och som innehöll fyra rader när vi började och innehöll bara två. Vi har också dessa kvantitetsbalanserade noll, eller hur? Och nu har det ett tomt bord.

Vi kan ta bort dessa genom att filtrera på kolumnen Kvantitetssaldo. Vi vill inte ha kvantitetsbalansen lika med noll.

Och sedan ska jag välja min transformationskolumn och ta bort andra kolumner. Jag kan nu utöka mina transformationskapslade tabeller. Använd inte det ursprungliga kolumnnamnet som prefix och tryck på OK.

Välj Datatyp på fliken Transformera så är vi klara. Och det är så du rensar transaktionsdata.

Slutsats

I den här handledningen visade jag dig hur du städar upp transaktionsdata, specifikt tar bort dubbletter av rader. Detta är en utmärkt teknik du kan använda när du använder LuckyTemplates Power Query.

Jag hoppas att du gillade den här. Se hela videohandledningen nedan för mer information. Kolla in länkarna nedan för mer relaterat innehåll kring LuckyTemplates Power Query Editor.

Skål!

Melissa