LuckyTemplates Power Query: Fjerning av dupliserte rader

I denne bloggen skal jeg vise deg hvordan du fjerner dupliserte rader i LuckyTemplates Power Query basert på forhold. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Dette eksemplet er fra et spørsmål som ble reist på. Medlemmet har transaksjonsdata og ønsker å fjerne rader som kansellerer hverandre.

Det er to distinkte transaksjonstyper, en for innkommende og en for utgående. Hun har også lagt til antall bevegelser. Og ut ifra det formulerte hun tre scenarier.

Scenario en er basert på to rader. Hvis de kansellerer hverandre, bør begge radene fjernes. Hvis det ikke er tilfelle, bør begge radene beholdes.

Scenario to og tre er basert på en kombinasjon av rader, som delvis kansellerer hverandre. Disse radene bør fjernes på en først-inn-først-ut-basis, slik at bare de siste innkommende transaksjonene beholdes.

I utgangspunktet gjentar vi den samme logikken om og om igjen. Vi bør være i stand til å designe en enkelt tilnærming for å passe alle disse scenariene.

Noen av tingene vi skal gjøre er å legge til en kolonne med absolutte mengder og gruppere etter "legg til en mengdesaldokolonne" og en nestet tabell ved å bruke alternativet "alle rader". Vi vil også transformere de nestede tabellene, sortere bevegelsestypen og posteringsdatoen, lage en løpende total og bare beholde rader basert på en betingelse.

Jeg har vurdert dette på et mellomnivå fordi de fleste transformasjonene vi kan gjøre her er ved å bruke brukergrensesnittet. Med det sagt, la oss gå over til LuckyTemplates Power Query.

Innholdsfortegnelse

• Legger til kolonne med absolutte mengder i Power Query
• Grupper etter ID-indeks og absolutt antall
• Transformering av nestede tabeller i LuckyTemplates Power Query
  • Sortering av bevegelsestype og konteringsdato
  • Opprette en løpende total
  • Filtrering av rader basert på en tilstand
• Konklusjon

Legger til kolonne med absolutte mengder i Power Query

Vi starter med å legge til en kolonne med absolutte mengder. Velg Antall- kolonnen, deretter på Legg til kolonne- båndet eller -fanen, gå til Scientific , og klikk deretter på Absolutt verdi .

Jeg skal gi nytt navn til denne kolonnen i formellinjen.

Grupper etter ID-indeks og absolutt antall

Vi kan nå gruppere etter ID-indeks og absolutt antall. Velg ID-indeks , hold deretter nede shift eller kontroll for å multi-velge, og klikk deretter på Abs quty (absolutt mengde).

Du finner Grupper etter på Transformer-fanen.

Men du kan også finne den på Hjem-fanen.

Klikk på den og nå skal vi legge til en ny kolonne. Det er ikke en "telling", men det kommer til å være "summen" av mengden. Så la oss gi det nytt navn til Kvantum (kvantitet) Balanse . Det kommer til å være summen av mengdekolonnen. Og så legger vi til en ny aggregering, men den kommer til å bli en spesiell, så vi velger Alle rader . La oss også gi denne kolonnen et navn ( AllRows ). Trykk deretter OK.

Hvis vi klikker i det hvite rommet i tabellen, ser vi en forhåndsvisning av den nestede tabellen nedenfor. Vi ser at mengdene for denne nestede tabellen opphever hverandre og mengdebalansen er null.

Hvis vi ser på den neste nestede tabellen (så klikk av til side i det hvite rommet i neste rad), ser vi at mengdekolonnen ikke opphever hverandre og en mengdebalanse er større enn null.

La oss legge til en egendefinert kolonne slik at vi kan bruke den for nestede tabelltransformasjoner senere. Så velg Legg til egendefinert kolonne .

La oss gi nytt navn til den kolonnen tilog vi legger til null (0) som en plassholder. Klikk deretter OK.

Transformering av nestede tabeller i LuckyTemplates Power Query

Vi er nå klare til å lage logikken for å transformere de nestede tabellene. Men vi vil ikke skrive en haug av dem, og vi trenger ikke.

Vi kan bruke brukergrensesnittet for det meste. Hvis vi bygger logikken for disse transformasjonene i en separat spørring, var det mest forseggjorte scenariet vi hadde det med enten tre eller fire rader.

Nå, hvis jeg klikker på høyre side i den siste raden, ser jeg at denne tabellen har fire rader. Så vi kan bruke dette til å bygge våre transformasjoner.

Høyreklikk på siden i det hvite området og velg Legg til som ny spørring .

Vi ser at den nestede tabellen nå er utvidet og lagt til som en ny spørring, og vi kan bruke denne til å bygge logikken vi trenger.

Sortering av bevegelsestype og konteringsdato

Nå, det første vi må gjøre er å sortere på Movement Type . La oss velge synkende . På denne måten vil de utgående transaksjonene alltid være på toppen.

Deretter sorterer vi posteringsdatoen som stigende , og sørger for at hvis vi utelater rader, vil det alltid gjøres i henhold til FIFO-prinsippet (først inn først ut).

Opprette en løpende total

For å identifisere radene som vi skal fjerne, skal jeg legge til en løpende total for å deaktivere, og vi kan bruke en List.FirstN for den oppgaven. Denne funksjonen lager en liste basert på en annen liste, der de øverste elementene holdes basert på et spesifikt nummer eller på et kriterium.

La oss gå tilbake til søket vårt. Vi trenger to ting for List.FirstN. Først trenger vi den listen, og den listen er kolonnen Antall . Hvis jeg høyreklikker på kolonneoverskriften Antall, får vi muligheten til å legge til som NewQuery .

Og her ser vi koden vi trenger for å generere den listen. I det anvendte trinnet kan du se at det peker til det siste trinnet og deretter identifiserer kolonnen i disse parentesene.

Den andre delen vi trenger for List.FirstN er et tall for å identifisere hvor mange tall som skal beholdes fra den listen. Vi kan bruke indeksen til det, så jeg skal legge til en indekskolonne fra en (1) for denne raden.

Så for nummer 1 vil den beholde tallet på den første raden i kolonnen Antall, og så videre. Og det kan vi nå oppsummere.

La oss legge til en egendefinert kolonne, og kalle den Running Total . Vi bruker List.FirstN her, og vi kan se at den første parameteren var den listen, Quantity-kolonnen. Vi må peke på det siste trinnet, og det siste trinnet er Added Index .

Vi identifiserte kolonnen vi vil ha, og det er mengdekolonnen. Vår opptelling er i indekskolonnen.

Nå returnerer den en liste. Hvis vi klikker til siden i det hvite rommet, kan vi se innholdet i den listen. For den første rekorden holdt den bare den øverste raden fra den listen. For den andre rekorden beholdt den de to øverste rekkene.

Nå trenger vi bare å summere disse mengdene, og vi kan bruke Lists.Sum for å gjøre det. Så jeg skal legge det til i formellinjen. Og jeg skal også sette Type .

Filtrering av rader basert på en tilstand

Med denne løpende summen kan vi identifisere radene vi ønsker å beholde. Vi ønsker bare å beholde rader som er større enn null, så la oss legge til en filterbetingelse.

Vi kan nå fjerne hjelpekolonnene våre. Velg Indeks og løpende total, og klikk deretter Fjern kolonner.

I Advanced Editor kan vi nå kopiere koden vi har laget. Jeg åpner , og vi kan se at dette er vårt delte trinn. Så vi kan velge og kopiere alt under det.

La oss nå gå tilbake til vår opprinnelige spørring. Igjen, åpne Advanced Editor. Og i stedet for plassholderen, la oss gå til en ny linje. Jeg kommer til å bruke " la "-uttrykket fordi det kan fange opp verdier fra mellomberegninger i variabler. Så, "la" og gå deretter til en ny linje og lim inn transformasjonene mine. Jeg vil også erklære Type .

Den uthevede koden ovenfor peker til vår utvidede nestede tabell, som er plassert i kolonnen Alle rader. Så jeg skal peke på det ved å erstatte denne uthevede koden til AllRows .

Så vår transformasjonskolonne har nå nestede tabeller. Vi bygger søket vårt basert på den siste raden, ikke sant? Og som inneholdt fire rader da vi startet og bare inneholdt to. Vi har også disse mengden balansert null, ikke sant? Og nå har den et tomt bord.

Vi kan fjerne disse ved å filtrere på Mengdebalanse-kolonnen. Vi vil ikke ha mengdebalansen lik null.

Og så skal jeg velge transformasjonskolonnen min og fjerne andre kolonner. Jeg kan nå utvide de nestede transformasjonstabellene mine. Ikke bruk det opprinnelige kolonnenavnet som prefiks og trykk OK.

På Transform-fanen velger du Datatype , og vi er ferdige. Og det er slik du rydder opp i transaksjonsdata.

Konklusjon

I denne opplæringen viste jeg deg hvordan du rydder opp i transaksjonsdata, spesifikt fjerner dupliserte rader. Dette er en flott teknikk du kan bruke når du bruker LuckyTemplates Power Query.

Jeg håper du likte denne. Se hele videoopplæringen nedenfor for mer informasjon. Sjekk ut koblingene nedenfor for mer relatert innhold rundt LuckyTemplates Power Query Editor.

Jubel!

Melissa