Tweet

Odebrat duplicity - max hodnota v jiném sloupci - M Power Query

Úvodem do odstraňování duplicit

Jak odstranit duplicity v Excel, za využití nástroje Power Query a jazyka M. Výhodou je, že využijete i v Power BI.

Předpokládám, základní znalost práce s Power Query editorem, ale jak na to, když je požadavek na odstranění (potažmo ponechání) konkrétních duplicitních záznamu? V tomto článku se podíváme, jak na to a co může pozlobit (pokud zapomenete, načítaná data budou mít seřazena jak potřebujete).

• Zdrojová data
• Unikáty poslední pokud je vhodně seřazena tabulka
• Unikáty pokud není seřazena - problém
• Unikáty pokud není seřazené - řešení Table.Buffer
• Unikáty pro první nákupy
• Pár tipů na co nezapomenout
• Související články
• Ke stažení

Co potřebuji

Než mnoho slov, tak jeden obrázek řekne (ukáže) více.

Zdrojová data

Než začneme tvořit zobrazovat unikáty, potřebujeme data. K dispozici máme jednoduchou tabulku o zákaznicích. Které mají dva sloupce, jeden je ID zákazníka a druhý je datum prodeje. Máme je ve dvou tvarech

• seřazeny podle datumu prodej - od nejnovějšího datumu po nejstarší datum
• seřazeny podle ID -

Budeme potřebovat ID zákazníka a k němu datum:

• od nejnovějšího datumu (poslední datum projede)
• od nejstaršího (první den prodeje)

Unikáty, poslední pokud je vhodně seřazena tabulka

Začneme jednoduchým postupem v Power Query. Předpokládám, že v Power Query máte tabulku načtenou.

Seřadit není třeba, ale předpokládám, že jsem si seřazení tabulky nevšiml, tak seřadím od největšího. Klik na šipku a seřadit sestupně.

Teď zbývá odebrat duplicity. Pravé tlačítko a odebrat duplicity.

Následně můžete ještě seřadit výsledek podle zákazníka:

Pokud ještě ručně zkontrolujete, tak opravdu odebrání duplicit funguje jak potřebujete. Jen ve skutečnosti úplně nefunguje, což si ukážeme dále. Hlavně pokud původní tabulka nebude seřazena (což se může stát, musíte předpokládat, že seřazeno nemusí být).

Unikáty pokud není seřazena - problém

Předchozí úkol máte za sebou, teď si zkusme unikáty u neseřazené tabulky. Pokud pracujete s Excel víte, že pokud seřadíte a dáte odebrat duplicity, tak se odebírají odzadu, což potřebujeme, takže zkusíme to podobně i v Power Query.

Začneme postupem podobným postupem, jako v předchozím případě. Předpokládám, že v Power Query máte načtenou druhou neseřazenou tabulku.

Seřadit již je potřeba, klik na šipku a seřadit sestupně (což by mělo fungovat).

Odebereme duplicity a na první pohled máme hotovo.

Obě zdrojové tabulky mají shodná data, tak na první pohled je vidět, že výsledek je špatně (poslední nákupy všichni zákaznici provedli ve stejný den 11.5.2022). Ač je požadavek na seřazení tabulky (a tabulku v příslušném kroku vidíte seřazenou), tak pohled do řešení ukazuje, že se nepovedlo.

M-ko funguje geniálně, je totiž líné, takže některé kroky neprovede, pokud k tvorbě výsledku nejsou potřeba (což k ostranění duplicit až tak potřeba není - chcete odstranit duplicity na základě údajů sloupce, ano chcete více, ale více informaci kroku nepředáte). Toto by bylo na delší povídání, khoho zajímá tak viz Table.Distinct a jak funguje Mko na pozadí, proto přeskočme a raději se podiváme dále, jak M-ku říct, to řazení fakt je potřeba.

Unikáty pokud není seřazene seřaď - řešení

Neboli, před další operaci (unikáty) seřazená data načteme do paměti (tím Power Query řekneme, že i tento krok který porvede seřazení je opravdu potřeba), využitím funkce Table.Buffer.

Nejprve musíte vstoupit do Rozšířeného editoru (v menu Domů klik na Rozšířený editor) a upravit řádek s filtrováním. Neboli řádek:

#"Seřazené řádky" = Table.Sort(#"Změněný typ",{{"Datum nákupu", Order.Descending}}), Doplnit o Table.Buffer() nebolí krok Table.Sort(#"Změněný typ",{{"Datum nákupu", Order.Descending}}) obalíte funkci Table.Buffer() : #"Seřazené řádky" = Table.Buffer( Table.Sort(#"Změněný typ",{{"Datum nákupu", Order.Descending}})),

Pozor na závorky!

Klik na OK. Výsledek pak je, jak potřebujete. Obsahuje opravdu poslední nákupy.

Unikáty pro první nákupy

V podobném duchu, můžeme najít i první, tady platí pro obě tabulky, že seřazenou oblast musíte "nabuferovat".

let Zdroj = Excel.CurrentWorkbook(){[Name="Tabulka18"]}[Content], #"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Zákazník", Int64.Type}, {"Datum nákupu", type date}}), #"Seřazené řádky" = Table.Buffer( Table.Sort(#"Změněný typ",{{"Datum nákupu", Order.Ascending}})), #"Odebrané duplicitní položky" = Table.Distinct(#"Seřazené řádky", {"Zákazník"}), #"Seřazené řádky1" = Table.Sort(#"Odebrané duplicitní položky",{{"Zákazník", Order.Ascending}}) in #"Seřazené řádky1"

Výsledek

Pár tipů na co nezapomenout

Při použití Table.Buffer může zrychlit nebo zpomalit spouštění dotazů (záleží, jak jsou sestaveny) z důvodů uložení dat do paměti. Pokud dokážete 100% zaručit, že zdroj dat bude seřazen jak potřebujete, můžete si použití této funkce odpustit a jen zkontrolovat, zda provádění kroků bude rychlejší, bez této funkce.

Související články

Související články o M-jazyku

• Seriál jak na Power BI

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.

Závěrem

Narazili jste na nějaký problém, máte tip na vylepšení nebo doplnění článku, můžete se zmínit v komentářích.

Článek byl aktualizován: 13.05.2022 15:29

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.