Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » Odebrat-duplicity-max-min-hodnota
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Odebrat duplicity, na základě požadavků maximální nebo minimální hodnoty.

Mějte tabulku zákazníku s dnem jejich nákupu. Potřebujete zobrazit první (poslední) provedený nákup každého zákazníka. Nebo-li odebrat duplicity (a z duplicit ponechat požadované). Jak na to v Power Query se dozvíte v tomto článku.

Včetně postupu, jak vyřešit problém, pokud zdrojová tabulka není seřazena jak potřebujete (a co to může způsobit).

Ú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).

Co potřebuji

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

Excel ukázka

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)
Excel data

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.

Excel - serazena tabulka

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ě.

Excel - seradit

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

Excel - seradit

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

Excel - hotovo

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.

Excel - serazena tabulka

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

Excel - seradit

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

Excel - odebráno

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}})), Excel - Rozšířený editor

Pozor na závorky!

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

Excel - Výsledek

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

Excel - 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

Microsoft Excel - stahuj logo

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |