LuckyTemplates Power Query: Odstranění duplicitních řádků

V tomto blogu vám ukážu, jak odstranit duplicitní řádky v Power Query LuckyTemplates na základě podmínek. Celé video tohoto tutoriálu si můžete prohlédnout ve spodní části tohoto blogu.

Tento příklad je z otázky, která byla vznesena na. Člen má transakční data a chce odstranit řádky, které se navzájem ruší.

Existují dva odlišné typy transakcí, jeden pro příchozí a jeden pro odchozí. Také přidala počet pohybů. A na základě toho zformulovala tři scénáře.

Scénář jedna je založen na dvou řádcích. Pokud se navzájem vyruší, měly by být odstraněny oba řádky. Pokud tomu tak není, měly by být zachovány oba řádky.

Scénáře dva a tři jsou založeny na kombinaci řádků, které se částečně navzájem ruší. Tyto řádky by měly být odstraněny na principu první dovnitř, první ven, aby byly zachovány pouze nejnovější příchozí transakce.

V podstatě stále dokola opakujeme stejnou logiku. Měli bychom být schopni navrhnout jediný přístup, který by vyhovoval všem těmto scénářům.

Některé z věcí, které uděláme, je přidání sloupce s absolutními množstvími a seskupení podle „přidat sloupec množstevní rovnováhy“ a vnořenou tabulku pomocí možnosti „všechny řádky“. Také transformujeme tyto vnořené tabulky, seřadíme typ pohybu a datum zaúčtování, vytvoříme průběžný součet a ponecháme řádky pouze na základě podmínky.

Hodnotil jsem to na středně pokročilé úrovni, protože většina transformací, které zde můžeme provést, je pomocí uživatelského rozhraní. S tím, co bylo řečeno, pojďme k Power Query LuckyTemplates.

Obsah

• Přidání sloupce s absolutními množstvími v Power Query
• Seskupit podle ID indexu a absolutního množství
• Transformace vnořených tabulek v Power Query LuckyTemplates
  • Typ řazení a datum zaúčtování
  • Vytvoření průběžného součtu
  • Filtrování řádků Na základě podmínky
• Závěr

Přidání sloupce s absolutními množstvími v Power Query

Začneme přidáním sloupce s absolutními veličinami. Vyberte sloupec Množství , poté na pásu nebo kartě Přidat sloupec přejděte na Vědecké a poté klikněte na Absolutní hodnota .

Přejmenuji tento sloupec v řádku vzorců.

Seskupit podle ID indexu a absolutního množství

Nyní můžeme seskupit podle ID indexu a absolutního množství. Vyberte ID Index , poté podržte klávesu Shift nebo Ctrl pro vícenásobný výběr a poté klikněte na Abs qty (absolutní množství).

Seskupit podle najdete na kartě Transformace.

Najdete jej však také na kartě Domů.

Klikněte na něj a nyní přidáme nový sloupec. Není to „počet“, ale bude to „součet“ množství. Přejmenujme jej tedy na Qty (množství) Balance . Bude to součet sloupce množství. A pak přidáme další agregaci, ale bude to speciální, takže vybereme Všechny řádky . Pojmenujme také tento sloupec ( AllRows ). Poté stiskněte tlačítko OK.

Klikneme-li do bílého místa v tabulce, uvidíme níže náhled vnořené tabulky. Vidíme, že množství pro tuto vnořenou tabulku se navzájem ruší a bilance množství je nulová.

Pokud se podíváme na další vnořenou tabulku (takže klikneme na stranu do prázdného místa dalšího řádku), vidíme, že sloupec množství se navzájem neruší a zůstatek množství je větší než nula.

Pojďme přidat vlastní sloupec, abychom jej mohli později použít pro transformace vnořených tabulek. Vyberte tedy Přidat vlastní sloupec .

Přejmenujme ten sloupec naa přidáme nulu (0) jako zástupný symbol. Potom klepněte na tlačítko OK.

Transformace vnořených tabulek v Power Query LuckyTemplates

Nyní jsme připraveni vytvořit logiku pro transformaci těchto vnořených tabulek. Ale hromadu jich psát nechceme a ani nepotřebujeme.

Z velké části můžeme používat uživatelské rozhraní. Pokud vytvoříme logiku pro tyto transformace v samostatném dotazu, nejpropracovanější scénář, který jsme měli, byl ten se třemi nebo čtyřmi řádky.

Nyní, když kliknu na pravou stranu v posledním řádku, vidím, že tato tabulka má čtyři řádky. Takže to můžeme použít k vytvoření našich transformací.

Klikněte pravým tlačítkem na stranu v prázdném prostoru a vyberte Přidat jako nový dotaz .

Vidíme, že vnořená tabulka se nyní rozšířila a byla přidána jako nový dotaz a můžeme to použít k vytvoření logiky, kterou potřebujeme.

Typ řazení a datum zaúčtování

Nyní, první věc, kterou musíme udělat, je seřadit podle typu pohybu . Zvolme sestupně . Tímto způsobem budou odchozí transakce vždy nahoře.

Dále seřadíme Datum zaúčtování jako vzestupně , přičemž zajistíme, že pokud vynecháme řádky, bude to vždy provedeno podle principu FIFO (first in first out).

Vytvoření průběžného součtu

Abychom identifikovali řádky, které se chystáme odstranit, přidám průběžný součet pro zakázání a pro tento úkol můžeme použít List.FirstN . Tato funkce vytvoří seznam založený na jiném seznamu, kde jsou top položky vedeny na základě konkrétního čísla nebo kritéria.

Vraťme se k našemu dotazu. Pro List.FirstN potřebujeme dvě věci. Nejprve potřebujeme tento seznam a tento seznam je náš sloupec Množství . Pokud kliknu pravým tlačítkem na záhlaví sloupce Množství, dostaneme možnost Přidat jako nový dotaz .

A zde vidíme kód, který potřebujeme k vygenerování tohoto seznamu. V použitém kroku můžete vidět, že ukazuje na poslední krok a pak identifikuje sloupec v těchto závorkách.

Druhá část, kterou potřebujeme pro List.FirstN, je číslo, které identifikuje, kolik čísel si z tohoto seznamu ponechat. Můžeme k tomu použít index, takže pro tento řádek přidám sloupec indexu od jednoho (1).

Takže pro číslo 1 zůstane číslo na prvním řádku ve sloupci Množství a tak dále. A nyní to můžeme shrnout.

Pojďme přidat vlastní sloupec a nazvěme jej Průběžný součet . Použijeme zde List.FirstN a vidíme, že prvním parametrem byl tento seznam, sloupec Množství. Musíme ukázat na poslední krok a tím posledním krokem je Added Index .

Identifikovali jsme sloupec, který chceme, a to je náš sloupec Množství. Náš počet je ve sloupci Index.

Nyní vrátí seznam. Pokud klikneme na stranu do bílého prostoru, můžeme vidět obsah tohoto seznamu. Pro první záznam si ponechal pouze horní řádek z tohoto seznamu. Pro druhý rekord si ponechalo první dvě řady.

Nyní vše, co musíme udělat, je sečíst tato množství a můžeme k tomu použít Lists.Sum . Takže to přidám do řádku vzorců. A taky dám Type .

Filtrování řádků Na základě podmínky

Pomocí tohoto průběžného součtu můžeme identifikovat řádky, které chceme zachovat. Chceme zachovat pouze řádky, které jsou větší než nula, takže přidáme podmínku filtru.

Nyní můžeme odstranit naše pomocné sloupce. Vyberte Index a Průběžný součet a poté klikněte na Odebrat sloupce.

V pokročilém editoru nyní můžeme zkopírovat kód, který jsme vytvořili. Otevřu a uvidíme, že toto je náš dělený krok. Takže můžeme vybrat a zkopírovat vše pod tím.

Nyní se vraťme k původnímu dotazu. Znovu otevřete Rozšířený editor. A místo zástupného symbolu se přesuneme na nový řádek. Použiji výraz „ let “, protože dokáže zachytit hodnoty z mezivýpočtů v proměnných. Takže „nechte“ a pak přejděte na nový řádek a vložte své transformace. Také deklaruji Typ .

Zvýrazněný kód výše ukazuje na naši rozšířenou vnořenou tabulku, která je umístěna ve sloupci Všechny řádky. Takže na to poukážu tím, že tento zvýrazněný kód nahradím AllRows .

Takže náš transformační sloupec má nyní vnořené tabulky. Náš dotaz vytváříme na základě posledního řádku, že? A to obsahovalo čtyři řady, když jsme začínali, a obsahovalo pouze dva. Taky máme ty kvantity vyrovnané nulou, že? A teď má prázdný stůl.

Můžeme je odstranit filtrováním ve sloupci Zůstatek množství. Nechceme, aby se množstevní bilance rovnala nule.

A pak vyberu svůj transformační sloupec a odstraním ostatní sloupce. Nyní mohu rozšířit své transformační vnořené tabulky. Nepoužívejte původní název sloupce jako předponu a stiskněte OK.

Na kartě Transformace vyberte Typ dat a máme hotovo. A takto vyčistíte transakční data.

Závěr

V tomto tutoriálu jsem vám ukázal, jak vyčistit transakční data, konkrétně odstranit duplicitní řádky. Toto je skvělá technika, kterou můžete použít při používání LuckyTemplates Power Query.

Doufám, že se vám to líbilo. Podívejte se na kompletní video tutoriál níže, kde najdete další podrobnosti. Podívejte se také na odkazy níže, kde najdete další související obsah kolem LuckyTemplates Power Query Editor.

Na zdraví!

Melissa