LuckyTemplates Power Query: Fjernelse af duplikerede rækker

I denne blog vil jeg vise dig, hvordan du fjerner duplikerede rækker i LuckyTemplates Power Query baseret på betingelser. Du kan se den fulde video af denne tutorial nederst på denne blog.

Dette eksempel er fra et spørgsmål, der blev rejst på. Medlemmet har transaktionsdata og ønsker at fjerne rækker, der annullerer hinanden.

Der er to forskellige transaktionstyper, en for indgående og en for udgående. Hun har også tilføjet bevægelsestælling. Og ud fra det formulerede hun tre scenarier.

Scenarie et er baseret på to rækker. Hvis de ophæver hinanden, skal begge rækker fjernes. Hvis det ikke er tilfældet, skal begge rækker beholdes.

Scenarier to og tre er baseret på en kombination af rækker, der delvist annullerer hinanden. Disse rækker bør fjernes efter først-ind-først-ud-basis, så kun de seneste indgående transaktioner bevares.

Grundlæggende gentager vi den samme logik igen og igen. Vi bør være i stand til at designe en enkelt tilgang, der passer til alle disse scenarier.

Nogle af de ting, vi skal gøre, er at tilføje en kolonne med absolutte mængder og gruppere efter "tilføj en mængdebalancekolonne" og en indlejret tabel ved hjælp af "alle rækker"-indstillingen. Vi vil også transformere de indlejrede tabeller, sortere bevægelsestypen og bogføringsdatoen, oprette en løbende total og kun beholde rækker baseret på en betingelse.

Jeg har vurderet dette på et mellemniveau, fordi de fleste af de transformationer, vi kan gøre her, er ved at bruge brugergrænsefladen. Med det sagt, lad os gå over til LuckyTemplates Power Query.

Indholdsfortegnelse

• Tilføjelse af kolonne med absolutte mængder i Power-forespørgslen
• Gruppér efter ID-indeks og absolut mængde
• Transformering af indlejrede tabeller i LuckyTemplates Power Query
  • Sortering af bevægelsestype & bogføringsdato
  • Oprettelse af en løbende total
  • Filtrering af rækker baseret på en tilstand
• Konklusion

Tilføjelse af kolonne med absolutte mængder i Power-forespørgslen

Vi starter med at tilføje en kolonne med absolutte mængder. Vælg kolonnen Antal , derefter på båndet eller fanen Tilføj kolonne , gå til Videnskabelig , og klik derefter på Absolut værdi .

Jeg vil omdøbe denne kolonne i formellinjen.

Gruppér efter ID-indeks og absolut mængde

Vi kan nu gruppere efter ID-indeks og absolut mængde. Vælg ID-indeks , hold derefter shift eller kontrol nede for at vælge flere, og klik derefter på Abs qty (absolut mængde).

Du finder Grupper efter på fanen Transformer.

Men du kan også finde det på fanen Hjem.

Klik på den, og nu tilføjer vi en ny kolonne. Det er ikke en "optælling", men det vil være "summen" af mængden. Så lad os omdøbe det til Antal (mængde) Balance . Det vil være en sum af kolonnen mængde. Og så tilføjer vi endnu en samling, men det bliver en speciel, så vi vælger Alle rækker . Lad os også give denne kolonne et navn ( AllRows ). Tryk derefter på OK.

Hvis vi klikker i det hvide rum i tabellen, ser vi en forhåndsvisning af den indlejrede tabel nedenfor. Vi ser, at mængderne for denne indlejrede tabel ophæver hinanden, og mængdebalancen er nul.

Hvis vi ser på den næste indlejrede tabel (så klik til side i det hvide rum i næste række), ser vi, at mængdekolonnen ikke ophæver hinanden, og en mængdebalance er større end nul.

Lad os tilføje en brugerdefineret kolonne, så vi kan bruge den til vores indlejrede tabeltransformationer senere. Så vælg Tilføj tilpasset kolonne .

Lad os omdøbe den kolonne tilog vi tilføjer nul (0) som en pladsholder. Klik derefter på OK.

Transformering af indlejrede tabeller i LuckyTemplates Power Query

Vi er nu klar til at skabe logikken til at transformere de indlejrede tabeller. Men vi vil ikke skrive en masse af dem, og det behøver vi ikke.

Vi kan bruge brugergrænsefladen til det meste. Hvis vi bygger logikken for disse transformationer i en separat forespørgsel, var det mest omfattende scenarie, vi havde, det med enten tre eller fire rækker.

Hvis jeg nu klikker på højre side i den sidste række, ser jeg, at denne tabel har fire rækker. Så vi kan bruge dette til at bygge vores transformationer.

Højreklik på siden i det hvide rum, og vælg Tilføj som ny forespørgsel .

Vi ser, at den indlejrede tabel nu er udvidet og blevet tilføjet som en ny forespørgsel, og vi kan bruge denne til at bygge den logik, vi har brug for.

Sortering af bevægelsestype & bogføringsdato

Nu, den første ting, vi skal gøre, er at sortere på Movement Type . Lad os vælge faldende . På denne måde vil de udgående transaktioner altid være øverst.

Dernæst sorterer vi bogføringsdatoen som stigende , og sørger for, at hvis vi udelader rækker, vil det altid ske efter FIFO-princippet (først ind først ud).

Oprettelse af en løbende total

For at identificere de rækker, som vi vil fjerne, vil jeg tilføje en løbende total for at deaktivere, og vi kan bruge en List.FirstN til den opgave. Denne funktion opretter en liste baseret på en anden liste, hvor de øverste elementer opbevares ud fra et bestemt nummer eller et kriterium.

Lad os gå tilbage til vores forespørgsel. Vi skal bruge to ting til List.FirstN. Først har vi brug for den liste, og den liste er vores mængde kolonne. Hvis jeg højreklikker på kolonneoverskriften Mængde, får vi muligheden for at tilføje som NewQuery .

Og her ser vi koden, som vi skal bruge for at generere den liste. I det anvendte trin kan du se, at det peger på det sidste trin og derefter identificerer kolonnen i disse parenteser.

Den anden del, som vi har brug for til List.FirstN, er et tal til at identificere, hvor mange numre, der skal beholdes fra denne liste. Vi kan bruge indekset til det, så jeg vil tilføje en indekskolonne fra en (1) for denne række.

Så for nummer 1 vil det beholde tallet på den første række i kolonnen Antal, og så videre. Og det kan vi nu opsummere.

Lad os tilføje en brugerdefineret kolonne og kalde den Løbende total . Vi bruger List.FirstN her, og vi kan se, at den første parameter var denne liste, kolonnen Antal. Vi skal pege på det sidste trin, og det sidste trin er det tilføjede indeks .

Vi har identificeret den kolonne, vi ønsker, og det er vores kolonne for mængde. Vores optælling er i kolonnen Indeks.

Nu returnerer den en liste. Hvis vi klikker videre til siden i det hvide rum, kan vi se indholdet af den liste. For den første rekord holdt den kun den øverste række fra den liste. For den anden rekord beholdt den de to øverste rækker.

Nu skal vi bare opsummere disse mængder, og vi kan bruge Lists.Sum til at gøre det. Så jeg vil tilføje det i formellinjen. Og jeg har også tænkt mig at sætte Type .

Filtrering af rækker baseret på en tilstand

Med denne løbende total kan vi identificere de rækker, som vi vil beholde. Vi ønsker kun at beholde rækker, der er større end nul, så lad os tilføje en filterbetingelse.

Vi kan nu fjerne vores hjælpekolonner. Vælg Indeks og løbende total, og klik derefter på Fjern kolonner.

I den avancerede editor kan vi nu kopiere den kode, vi har oprettet. Jeg åbner , og vi kan se, at dette er vores splittrin. Så vi kan vælge og kopiere alt under det.

Lad os nu gå tilbage til vores oprindelige forespørgsel. Åbn igen Advanced Editor. Og i stedet for pladsholderen, lad os flytte til en ny linje. Jeg vil bruge udtrykket " lad ", fordi det kan fange værdier fra mellemliggende beregninger i variable. Så "lad" og gå derefter til en ny linje og indsæt mine transformationer. Jeg vil også erklære Type .

Den fremhævede kode ovenfor peger på vores udvidede indlejrede tabel, som er placeret i kolonnen Alle rækker. Så jeg vil pege på det ved at erstatte denne fremhævede kode til AllRows .

Så vores transformationskolonne har nu indlejrede tabeller. Vi bygger vores forespørgsel baseret på den sidste række, ikke? Og det indeholdt fire rækker, da vi startede og kun indeholdt to. Vi har også den mængde balanceret nul, ikke? Og nu har den et tomt bord.

Vi kan fjerne dem ved at filtrere på kolonnen Mængdebalance. Vi ønsker ikke, at mængdebalancen er lig nul.

Og så vil jeg vælge min transformationskolonne og fjerne andre kolonner. Jeg kan nu udvide mine indlejrede transformationstabeller. Brug ikke det originale kolonnenavn som præfiks, og tryk på OK.

På fanen Transformer skal du vælge Datatype , og vi er alle færdige. Og det er sådan, du rydder op i transaktionsdata.

Konklusion

I denne tutorial viste jeg dig, hvordan du rydder op i transaktionsdata, specifikt fjerner duplikerede rækker. Dette er en fantastisk teknik, du kan bruge, når du bruger LuckyTemplates Power Query.

Jeg håber, du nød denne. Se den fulde videotutorial nedenfor for flere detaljer. Tjek linkene nedenfor for mere relateret indhold omkring LuckyTemplates Power Query Editor.

Skål!

Melissa