Filtrování v Power Query

Úvodem

Rychlý úvod do řazení a filtrování jsem již sepsal. V tomto článku se podíváme na řazení hlouběji. Pro přehlednost je článek rozdělen na kapitoly:

• Zdrojová data - bez dat to nejde (využijeme slova se základem sto)
• Slova se sto - ruční "zakliknutí" a možný problém
• Filtrování dle požadavků (chceme rozlišovat podle velikosti písmen):
  • jen slovo "sto" nebo nesmí být slovo sto
  • začíná na "sto" nebo nezačíná
  • obsahuje "sto" nebo neobsahuje
  • končí "sto" nebo nekončí
• Filtrovat bez ohledu na velikost písmen
• Další možností řazení - v přípravě
• Související články
• Ke stažení

Zdrojová data

Bez dat to nejde, mějme malou tabulku, které obsahuje slova (ano využijeme slova se základem sto) a také jiná slova, případně budeme mít v zásobě další, které pak přidáme a aktualizujeme, ať můžeme zkontrolovat co se bude dít, pokud dojde k aktualizaci dat.

sto stovka stokoruna dvěstovky stoka město desítka

Pak přidáme do tabulky slova:

STO Sto třistovky dvacítka

V ukázce tabulka obsahuje více sloupců, třeba pro další testy s filtry a filtrováním. Nás bude zatím zajímat jen první.

Slova se sto

V prvním kroku využijeme ruční "odkliknutí" z výběru, kdy budeme chít mít vyfiltrovány slova obsahující sto (to jsou všechna, kromě posledního). Tak vás napadne snadná cesta odkliknout pouze slovo "desítka":

• desítka

Kdo je pozornější, tak už vidí v řádků vzorců problém.

Neboli kód:

= Table.SelectRows(#"Změněný typ", each ([Slova] <> "desítka"))

Neboli, Power Query si ulehčil práci a řekl vše kromě desítka. Pokud budeme do tabulky postupně přidávat slova (STO, Sto, třistovky) a aktualizovat, tak na první pohled nenarazíme na chybu. (možná někdo u prvního slova STO, si řekne, že by se měla respekovat velká a malá písmena a přesto je zahrnuto).

Po přidání slova "dvacítka", tak i tato se zahrne ač ve slově není základ "sto" tj. tyto tři písmena neobsahuje.

To protože se zobrazí vše kromě slova "desítka", kterou jste jako první odklikly, že ve výběru nechcete.

Potažmo pokud vyberete více slov může být kód doplněn o and a další název slova v příslušném sloupci např.

= Table.SelectRows(#"Změněný typ", each ([Slova] <> "desítka" and [Slova] <> "dvacítka" and [Slova] <> "třicítka"))

Případně mže vypadat, která slova chcete mít ve filtru:

= Table.SelectRows(#"Změněný typ", each ([Slova] = "sto" or [Slova] = "stoka" or [Slova] = "stokoruna" or [Slova] = "dvěstovky" or [Slova] = "stoka" or [Slova] = "město"))

"Zaklikávání" je nejméně vhodný způsob (ano může existovat, nějaká výjímka, kdy se bude hodit "zaklikávání" více).

Filtrování dle požadavků, kdy chceme rozlišovat velikosti písmen

V další kapitole se podíváme i pokud na velikosti (písmen) nezáleží.

Než vybereme, musíme se rozhodnout, zda slovo sto má být ve slov na začátku (stoka, sto, stovka, ...), na konci (sto, město, toběsto, dvěsto, ...), nebo má obsahovat (stoka, sto, stovka, město, toběsto, dvěsto, ...).

Lepší volba je zvolit nejprve Filtr textu a následně zvolit:

• Začíná na...
• Nezačíná na... budeme-li chtít filtrovat slova která nezačínají na zvolené slovo
• Končí na...
• Nekončí na...
• Obsahuje...
• Neobsahuje...

Ještě je možnost vybrat:

• Je rovno...
• Není rovno...

Které představuje druhou možnost jak "zaklikávat" co mají/nemají vyfiltrované řádky obsahovat.

Budete-li chtít slova která: Začíná na... vypadá kód:

= Table.SelectRows(#"Změněný typ", each Text.StartsWith([Slova], "sto"))

Zde se vyfiltrují slova která začínají na sto (sto s malými písmeny) - tj. sto, stovka, stokoruna, stoka, pokud přidáte slova "stovečka", "stoka" (tak se zobrazí), slovo město již ne (sto není na začátku), stejně tak STO (protože je psáno velkými písmeny).

Budete-li chtít slova která: Nezačíná na... vypadá kód:

= Table.SelectRows(#"Změněný typ", each not Text.StartsWith([Slova], "sto"))

Podobně pro další, které končí, nekončí, začínají, nezačínají:

= Table.SelectRows(#"Změněný typ", each Text.EndsWith([Slova], "sto")) = Table.SelectRows(#"Změněný typ", each not Text.EndsWith([Slova], "sto")) = Table.SelectRows(#"Změněný typ", each Text.Contains([Slova], "sto")) = Table.SelectRows(#"Změněný typ", each not Text.Contains([Slova], "sto"))

Super, ale co když máte požadavek, že na velikosti nezáleží?

Filtrovat bez ohledu na velikost písmen

Napadnout vás může otázka, co ale když nechci, aby se rozlišovala velikost písmen. Někoho napadne nejprve převést písmena ve všech slovech na malá a následně filtrovat, ale co když půjde o Jména, kódy, názvy ulic atd? Ok duplikovat sloupec a filtrovat podle duplicitního. Což je ale několik zbytečných kroků (zpomalení, znepřehlednění) ...

M-kód

Co je geniálního na M-ku tak je skutečnost, že funkce, které se dají "nakliknout" mají více parametru, které sice "nakliknutím" nezměníte, ale když vstoupíte do řádků vzoprců (nebo M-kódu), tak je změnit/upravit/vylepšit můžete.

Syntaxe

Pokud se podíváme na syntaxi výše uvedených funkcí, zjistíte, že obsahují ještě volitelný argument comparer, kterým určíte, že funkce mají ignorovat velikost písmen pokud vložíte do tohoto parametru hodnotu: Comparer.OrdinalIgnoreCase.

Text.StartsWith

Text.StartsWith( text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Text.EndsWith

Text.EndsWith( text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Text.Contains

Text.Contains( text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

Ukázky

Jak by vypadalo, pokud se nebude rozlišovat velikost písmen.

Do kódu přidáte (funkce) přidáte Comparer.OrdinalIgnoreCase, výhoda můžete rovnou do příkazového řádku. Pozor na čáku oddělující tento argument.

= Table.SelectRows(#"Změněný typ", each Text.StartsWith([Slova], "sto" , Comparer.OrdinalIgnoreCase))

Pro porovnání bez Comparer.OrdinalIgnoreCase

= Table.SelectRows(#"Změněný typ", each Text.StartsWith([Slova], "sto" ))

Najednou vidíte ve filtru i slovo "STO" samozřejmosti by se vyfiltrovalo i slovo "Stoka", "Sto" atd.

Další možností řazení

Využít List.Contains a List ve kterém máte seznam hodnot které (ne)chce mít ve filtru.

= Table.SelectRows(Zdroj, each List.Contains ({"A1", "B1", "C1"} ,[Výrobek]) )

Případně k filtrování využít Joinování.

Využití pomocného sloupce na texty, čísla atd.

Tyto metody ještě rozpracují (pokud bude zájem) v tomto článku, nebo se na ně podívám v samostatném článku.

Související články

Nenalezena odpověď, možná je sepsáno v souvisejícím článku:

• Jak řadit a filtrovat v Power Query
• Struktura M-kódu
• Aktualizace dat...
• ...

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émy, máte tip na vylepšení nebo doplnění článku, můžete zmínit v komentářích.

Článek byl aktualizován: 20.06.2023 08:44

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