Jak řadit a filtrovat v Power Query - Excel

Úvodem do řazení a filtrování v PowerQuery

Kromě načtení a úpravy dat potřebujete mnohdy v PowerQuery i filtrovat a řadit. Jak na filtrování a řazení se podíváme v tomto článku. Přepokládám, že v Excel máte k dispozici PowerQuery a umíte data do PowerQuery načíst. V tomto článku se tedy podívám jen na filtrování a řazení, navíc bude článek z důvodu přehlednosti rozdělen na jednotlivé kapitoly

• Zdrojová data - co budeme řadit a filtrovat
• Jak řadit texty, čísla, datumy
• Jak řadit data dle dvou sloupců a více zároveň
• Jak řadit dle svého - v přípravě
• Řadit rozdíl Excel a PowerQuery - v přípravě
• Filtrovat číslo
• Filtrovat texty - jak nahradit hvězdičky a otazníky a jak na další specifické znaky
• Filtrovat chybové hodnoty
• Ostatní - parametr, více sloupců
• Podklady pro M-language - jak na M-kód
• Související články
• Ke stažení

Zdrojová data

Pro řazení potřebujeme tabulku, která obsahuje data. Aby šlo ukázat co nejvíce alternativ, moje demo tabulka sloupec textové, číselný, datumový, časový, dva sloupce obsahující chyby, abecedu, atd.

Tuto tabulku (nebo podobnou tabulku sešit stačí načíst do PowerQuery). Jak na to se můžete dozvědět v článku: Jak načíst excelovský sešit do PowerQuery

Jak řadit

Klasické řazení, které znáte z Excel, asi nemuím připomínat. Podobně můžete řadit i v PowerQuery. Vyberete sloupec a zda chcete seřadit vzestupně (A až Z, 0 až "nekonečno") nebo sestupně (Z - A , "nekonečno" - 0 potažmo "mínus nekonečno"):

Následně se seřadí záznamy (celé řádky) dle příslušného sloupce. Řadí se vždy celé řádky (což je logické, ale pro jistotu připomenu).

Poznámka: Lze řadit o podle více sloupců, ale o tom v následující kapitole.

¨

Co abeceda

Co text čísla, co písmenko Ch, co velká a malá písmena. Zkoušejte, klidně si vytvořte sloupec s písmen znaků a čísel a testujte. Případně si můžete nastavit vlastní způsob řazení, ale o tom také v samostatné kapitole.

Jak řadit data více sloupců

Jak seřadit více sloupců "nakliknutím" (využitím myši) nevím (snad někdo poradí v komentářích). Protože mám rád M-language (M-jazyk), tak řešení je jednoduché, přidat do kódu další sloupec, podle kterého se bude řadit. Nebo-li do kódu, který se objeví po "nakliknutí" požadavku na seřazení Příjmení:

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}})

Stačí doplnit o {"Jméno", Order.Descending} kde je jméno sloupce a způsob řazení sestupně a je hotovo

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}, {"Jméno", Order.Descending}})

Jak řadit podle svých kritérií

V přípravě. Řazení podle vlastních kritérií (seznamů) je nepatrně složitějši, dokud nebude v Power Query volba řadit dle ... (Na druhou stranu, kdo prakticky využijete? Kdo ano již určitě zvládáte M-language a vlastní seznamy atd.).

Řadit rozdíl Excel a PowerQuery

Power Query řadí jinak než jste zvyklí z klasikého Excelu. Nejednodužsí pro pochopení je vužít zdraovaá data jako tabulku písmen (a jiných znaků), které si seřadíte v Excel a následně pro porovnání i v Power Query. Pozor duležité je v PowerQuery i formát dat e sloupci (zda jde o kombinaci čísel a textu, případně o text).

Pro ukázku:

• Excel 0 > 1 > 9 > ! > 0 > 9 > A > a > Ä > ä > á > c > C > ch > CH > Z
• Query 0 > 1 > 9 > ! > 0 > 9 > A > C > CH > Ch < Z > a > c > ch > z > Ä > á > ä > é
• Query - text ! > 0 > 9 > A > C > CH > Ch > Z > a > c > ch > z > Ä > á > ä > é

Ukázka řazení

Filtrovat číslo

Na první pohled jednoduchá volba, ale na ten druhý, kromě oblíbeného čísla (42) si můžete filtrovat, větší než, menší než atd.

Filtrovat texty - jak nahradit hvězdičky a otazníky

Klasický oblíbený filtr, zaškrtnete si co potřebujete mít zobrazeno. Zde není třeba představovat.

U filtrování textů v Excel možná znáte hvězdičkovou a otazníkovou konvenci (což na první pohled se vám v PowerQuery nepodaří). Jak na to ale v Power Query.

Můžete v menu využít, a možností jako začíná na, obsahuje, atd.

Obdržíte dialogové okno, kde můžete nastavit požadované parametry (případně přidat další za využití logických spojek A a Nebo):

Pozor PowerQuery rozlišuje malá a velká písmena.

Filtrovat specifické znaky

Jak na hledání specifických znaků se podíváme v samostatném článku. Stejně tak jak využít reguláry. Neboli jak vylepšit klasické filtrování z PowerQuery využitím M-language.

= Table.SelectRows(#"Seřazené řádky", each Text.Contains([Město], "o") or Text.Contains([Město], "O"))

Využít pro speciální filtrování lze o věcí jako přidání sloupce a podmínky, propojení dvou tabulek, funkce, atd. Tyto záležitostí si nechám do samostatného článku (třeba na základě dotazů v komentářích ho připravím přednostně).

Filtrovat chybové hodnoty

Spíše chybové hodnoty chcete odfiltrovat, pracovat můžete s celou tabulkou a pokud se někde chybová hodnota nachází, odfiltruje (schová) se celý řádek. Případně můžete provádět jen nad určitým sloupcem. Podívejme se na oba případy:

• Odstranit chybové řádky z celé tabulky
• Odstranit chybové řádky dle vybraného sloupce

Odstranit chybové řádky z celé tabulky

Rychlejší řešení, prostě se skryjí (odfiltrují) všechny řádky obsahující v některém ze sloupců chybovou hodnotu. Nad celou tabulkou a z menu vybrat odebrat chyby.

Odstranit chybové řádky dle vybraného sloupce

Vyberete požadovaný sloupce a z kontextového menu (pravý klik nad sloupcem) si vyberete odebrat chyby, skryjí se pak jen řádky které mají chybovou hodnotu ve vybraném sloupci.

Ostatní

Co se jinam nevešlo, aneb zajímavé tipy a poznatky, co mě různě napadne, nebo jsem řešil:

• Více sloupců
• Přes parametr
• Neúplný seznam

Více sloupců

Zde není co řešit prostě výběr provedete nad dalším sloupcem a vyberete (vyfiltrujete) si požadované hodnoty. Je jasné, pokud předchozí filtr vyfiltroval hodnoty a již se ve sloupci nenacházejí, tak je nemůžete filtrovat. Pokud máte vyfiltrovány všechny muže (vidíte jen M - nefoli Male), je jasné, že ve sloupci jméno neuvidíte ženské jméno (pominu-li fakt, že by žena náhodou mohla mít mužské jméno).

Přes parametr

Filtrovat, lze i na základě parametru, hodnoty v tabulce Excel a pak jen data aktualizovat. Jak na to jsme sepsal v samostatných článcích, protože způsobů, jak využít volání parametru do PowerQuery (M-language) je několik:

• Dynamické načítaní přes funkci
• Dynamické načítání přes konstantu

Neúplný seznam

V seznamu nejsou k dispozici všechny položky:

Podklady pro M-language

M-language je velice zajímavý, jednoduše použitelný a inspirativní, pokud ho umíte použít, dokážete věci, které se "nakliknutím" provést (zatím) nedají. Například řadit, podle více sloupců atd.

= Table.Sort(Zdroj,{{"Přijmení", Order.Ascending}, {"Jméno", Order.Descending}})

Případně hledat služitejší kombinace, hlavně využitím regulárních výrazů:

= Table.SelectRows(#"Seřazené řádky", each Text.Contains([Město], "o") or Text.Contains([Město], "O"))

Případně za využití pomocného sloupce

= Table.AddColumn(#"Text", "Category", each if List.Count(Splitter.SplitTextByAnyDelimiter({"A","B","C","D"})([Description])) > 1 then "Divize" else null)

O M-language mám sepsány samostatné články:

• Základy M-language

Související články

Související články, které vám pomohou pochopit jak je PowerQuery skvělý nástroj:

• Jak načíst data do PowerQuery - různé způsoby, různá data
• Jak načíst excelovský sešit do PowerQuery
• Základy M-language
• Dynamické načítaní přes funkci
• Dynamické načítání přes konstatntu

¨

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

Máte nějaký tip, co se dá k filtrování a řazení doplnit (třeba jak klikáním seřadit dle více sloupců)? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:02

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ář.