Dynamická práce s řádky v Editoru Power Query

Úvodem do dynamické práce s řádky v Editoru Power Query

Představte si, že máte tabulku, ze které potřebujete získat data, které jsou ve středu. Bohužel pozice dat se mění, ohraničují jí konkrétní slova (ID, Celkem). Jak na to s využitím Editoru Power Query se dozvíte v tomto článku.

Ukázka, kdy potřebujete zobrazit pouze šedou oblast:

Pro přehlednost je článek rozdělen na kapitoly:

• Data - která upravíme
• Načtení do Editoru Power Query
• Nalezení řádku od a řádku do
• Vyfiltrování oblasti
• Další úpravy a vylepšení
• Související články
• Ke stažení

Data

K dispozici mějme v Excel tabulku, kde potřebujete zjistit (zobrazit) prostřední čas dat. Kdy víte že tabulka začíná/končí pokud je ve v první sloupci:

• Začátek - obsahuje slovo/text ID, a zároveň chcete i tento řádek
• Konec - obsahuje slovo Celkem. Tento řádek ve výpise dat nepotřebujete.

Z důvodu zjednodušení se v prvním sloupci nacházejí tato slova (ID, Celkem) pouze jednou.

Načtení do Editoru Power Query

Tabulku načteme do Editoru Power Query, jelikož neznáme kolik řádku tabulka může obsahovat, navíc některé řádky mohou být prazdné. Proto označíme sloupce od A do D (pokud by sloupců bylo více tak samozdřejmě jich označíte příslušný počet ).

V Excelu na pásu karet Data vyberte Z tabulky nebo oblasti

V zobrazeném dialogovém okně Vytvořit tabulku pak stačí klik na OK.

Data se načtou do Editoru Power Query

Následně pro první sloupec odstraňte hodnoty null. Klik na malou šipku a odškrtnout položku null a klik na OK, protože nevíte jak je tabulka velká a řádky, které v prvním sloupci neobsahují hodnoty nepotřebujete.

Nalezení řádku od a řádku do

Protože potřebujete najít v prvním sloupci názvy (ID a Celkem), pro jistotu nejprve první sloupec převeďte na text (pokud ještě nemáte). Klik na ikonku ABC123, a vyberte Text.

Protože budete potřebovat čísla řádku, přidejte číslování řádku. Na pásu karet Přidaní sloupce rozbalit ikonu Indexový sloupec a vyberte Od 0. Za mně číslování od nuly je jednodušší, ale klidně můžete začít řádky číslovat od jedničky.

Máte přidány sloupec Index

Máte-li hotovo, tak pro první sloupec naleznete číslo pro řádek s textem "ID" a zároveň číslo řádku pro hodnotu v prvním sloupci s textem "Celkem".

Rozklikněte šipku u prvního sloupce a vyberte Filtr textu a následně Začíná na...

V dialogovém okně Filtrovat řádky zadáte:

• má na začátku a zadejte ID
• Nebo
• má na začátku a zadejte Celkem

Výsledkem bude:

Tak a máte čísla řádku mezi kterými, potřebujete mít data. Následně se podíváme na vyfiltrování oblasti.

Vyfiltrování oblasti

Čísla máte, teď potřebujete je vyfiltrovat, ale v základu Power Query Editor postupuje, krok za krokem a teď už nevidíte ostatní data, ale můžete použít fintu a opět budete vidět všechna data.

Teď bude poptřeba trochu vstupovat do M-kódu a bude se hodit mít zobrazen řádek vzorců. Pokud jej ještě nevidíte, můžete řádek vzorců zobrazit na pásu karet Zobrazení a zakliknout Řádek vzorců

Jelikož potřebujete opět vidět všechny data, klik na ikonu fx v řádků vzorců.

Na první pohled vypadá, že se nic nestalo, ale přidal se nový krok Vlastní1 a přidal se do řádku vzorců text (vzorec):

= #"Filtrované řádky1"

Tento potřebujete přepsat, ale ať to máte snadnější, tak nejprve přepíšete v použitém postupu krok Filtrované řádky na Filtr přes pravé tlačítko mto krokem nad vybrat přejmenovat na "Filtr".

Výsledek

Přesuneme se na poslední krok Vlastní1 - (onen poslední přidaný vzorec)

= #"Filtrované řádky1"

A přepíšete ho na:

=Filtr

A obdržíte (uvidíte) zase všechny data.

Perfektní co? Teď už zbýva jen vyfiltrovat...

Odstranit řádky za Celkem včetně

Pokud si pamatujete ještě, kdy jste měli pouze dva řádky (pokud ne mrkněte v článku výše, nebo se podívejte na předchozí kroky v Editoru). Teď víte, že číslo řádku na kterém se ono slovo nachází je 11. Takže postačí když ponecháte data po 11 řádek.

Zvolte na pásu karet Domů - Zachovat řádky - Zachovat horní řádky a do dialogového okna zadáte 11.

Přidá se krok Zachované první řádky, který obsahuje vzorec:

= Table.FirstN(Vlastní1,11)

Jistě vám dojde, že ta 11 by se měla měnit dynamicky. V tom máte pravdu. Číslo řádku víte, že je v kroku Filtrované řádky 1. Pro snadnější použití tento krok přejmenujete na Radky (využitím pravého tlačítka a přejmenovat, viz výše).

Tak a teď zbývá v posledním kroku nahradit vzorec = Table.FirstN(Vlastní1,11) za:

= Table.FirstN(Vlastní1,Radky[Index]{1})

Neboli číslo 11 vyměníte za

Radky[Index]{1}

Kde Radky je název kroku na který se odkazujete, [Index] slov v hranatých závorkách je název sloupec a {1} číslo v složených závorkách je číslo řádku, z kterého chcete hodnotu (chceme hodnotu z druhého řádku, proto jednička, jelikož se řádky číslují od nuly).

Samozřejmě se na výstupu nezměnilo. Stále vidíte data bez řádsku začinajicího Celkem a dále. Jen se zobrazují dynamicky (když se změní zdroj, přepočtou se čísla řádku a máte zobrazeno co potřebujete).

Odstranit řádky k ID

Odstranit první řádky provedeme podobně. Potřebujete odstranit prvních 6 (pokud nevěříte můžete se podívat výše).

Vyberete z menu (pásu karet) Domů - Odebrat řádky - Odebrat horní řádky a do dialogového okna zadat číslo 6.

Potěšilo vás? Je vidět i řádek s ID.

Samozřejmě, že ve stejném duchu potřebujete upravít vzorec tak, ať funguje dynamicky na základě pozice ID:

= Table.Skip(#"Zachované první řádky",6)

Teď jen přepíšete číslo 6 vzorcem:

Radky[Index]{0}

Nebo-li výsledný vzorec bude vypadat:

= Table.Skip(#"Zachované první řádky",Radky[Index]{0})

Název kroku, název sloupce a číslo řádku.

Máte hotovo. Teď je postačí uložit a zavřít.

Další úpravy a vylepšení

Tipy na další úpravy a vylepšení kódu (před uložením):

• První řádek jak záhlaví
• Typ pro sloupce
• Zkrácení kódu

První řádek jak záhlaví

Na pásu karet Domů a Použít první řádek jako záhlaví

= Table.PromoteHeaders(#"Odebrané horní řádky", [PromoteAllScalars=true])

Zkrácení kódu

Typy hodnot ve sloupci, ať je datum jako datum a čísla jako čísla. Což určitě zvládnete sami.

Zkrácení kódu

Zkrácení a vylepšení kódu bude předmětem samostatného článku.

Stejně tak v dalších článcích zmíním, proč jsme kroky přejmenovávali na jednoslovné a nenechávali dvouslovné s mezerou.

Poznámka: Třeba v některé z novějších verzí Editoru Microsoft upraví a bude používat pro názvy kroků velbloudí konvenci, neboli názvy kroku bez mezer například: "CisloRadkuPoslemni" atd.

Související články

Další související články:

• Možností načítání dat do Power Query Editoru
• Práce s řádky v Editoru Power Query

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 se zmínit v komentářích.

Článek byl aktualizován: 13.01.2022 20:08

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