Proč je List.Contains v Power Query pomalý? Rychlejší Lookup Alternativy

Vojtěch Šíma
Aug 24
9 min read

Updated: Aug 25

tl;dr List.Contains je v Power Query pomalý, protože při každém průchodu, třeba při zpracování každého řádku tabulky, prochází seznam od začátku, dokud nenajde shodu. Bufferování trochu pomůže, protože zamezí opakovanému přepočítávání seznamu, ale stále se musí projít každá položka. Chytřejší řešení bude sloučit tabulky pomocí Table.Join. Ale úplně nejlepší, v těch správných případech, bude převést seznam na jeden record, čímž vytvoříš něco jako hash mapu, a pak můžeš hledat skoro v konstantním čase přes Record.FieldOrDefault.

Disclaimer: Celý článek se baví o lookupech, kde má vyhledávací strana unikátní hodnoty.

Je velikost (seznamu) důležitá?

Nejprve si ujasníme, že nemluvím o pěti položkách v seznamu, které žádnou velkou bolest nezpůsobí (nebo jo?). Jde o tisíce položek, které musíš projít, abys našel správnou hodnotu. Abych ti ukázal, proč na velikosti záleží, ukážu svoje testovací výsledky, kde uvidíš, při jaké metodě dostává Power Query největší bídu, a které metody bys měl při větších seznamem opustit.

Proč je můj lookup pomalý?

Pokud máš větší vyhledávací tabulku nebo seznam a lookup aplikuješ na každý řádek, třeba i na stovky tisíc nebo miliony, v podstatě pokaždé znovu procházíš celý seznam. A pokud se lookup bere z externího zdroje a není to parametr nebo statický seznam, může se dokonce stát, že se seznam při každé iteraci znovu vyhodnotí a pak znovu celý projde. Takže první krok, který chceš udělat, je ten seznam nabufferovat. Tím minimálně zamezíš re-evaluaci.

Pokud List.Buffer vyřeší tvoje problémy, cajk. Jenže furt je pomalý a není to uplně vončo, že? List.Contains, List.PositionOf nebo cokoliv, co jsi viděl v nějakém tutoriálu, ti s pár hodnotami v krásném Excelu nebo Power BI funguje skvěle, ale ve skutečnosti pořád dokola projíždí ten samý seznam. Pokud budeš mít štěstí, tvoje hodnotý, které hledáš, budou na začátku, a tím pádem všechno proběhne svižně, ale, takhle štěstí nefunguje, takže stoprocentně počítej s tím, že budou spíše na konci. Nicméně, oká, není to úplně katastrofa, ale proč to vůbec nechávat na náhodě, když existuje lepší řešení?

Příklad: 100 tisíc hodnot v bufferovaném seznamu. Pak 30 tisíc řádků, kde používám List.Contains na hledání shod. První tabulka používala hodnoty z prvních 5 % seznamu. Druhá tabulka sahala na posledních 5 %. Výsledek -> Jedna vteřina proti šedesáti. Takže jop, u List.Contains hraje poloha v seznamu obrovskou roli.

Test: Rychlejší alternativy

Hrál jsem si s několika různými přístupy. Cíl byl jednoduchý: přidat sloupec, který jen řekne, jestli hodnota existuje v lookup seznamu. Možností je asi deset. Některé jsou spolehlivé, jiné spíš… kreativní.

Testovací prostředí

Faktová tabulka: pár sloupců, 1 milion řádků
Lookup tabulka: jeden sloupec, 20 tisíc unikátních řádků
Datový typ: text u obou
Soubory: CSV na lokálním disku
Lookup tabulka: nebyla nahraná (load) ani zahrnutá do refresh procesu
Měření: sledoval jsem, jak dlouho trvá refresh faktové tabulky
Částečné načítání: refresh jsem ručně stopnul na 2:25 a zkontroloval, jaká část z očekávaných 46 MB se stihla načíst
Šum v měření: rozdíly 1 až 2 sekundy jsou nepodstatné, spíš chyba ručního klikání, UI lag nebo nějaké procesy na pozadí

Výsledky jsou přehledně v tabulce, detailně vysvětlené níže.

ID	Přístup	Buffering	Načtený výstup	Čas trvání	Stav
01	Default	n/a	46 MB	10 s	hotovo
02	List.Contains	Table.Buffer	1 MB z 46 MB	1 h 51 m 10 s*	odhad z částečného načítání v 2:25
03	List.Contains	List.Buffer	19 MB z 46 MB	5 m 51 s*	odhad z částečného načítání v 2:25
04	List.PositionOf	List.Buffer	19 MB z 46 MB	5 m 51 s*	odhad z částečného načítání v 2:25
05	List.Select + List.IsEmpty	List.Buffer	7,6 MB z 46 MB	14 m 38 s*	odhad z částečného načítání v 2:25
06	„Single value“ lookup výraz	Table.Buffer	1 MB z 46 MB	1 h 51 m 10 s*	odhad z částečného načítání v 2:25
07	Table.Join LeftOuter	Table.Buffer	46 MB	12 s	hotovo
08	Table.Join LeftOuter	bez bufferu	46 MB	12 s	hotovo
09	Table.Join + rozbalený Column	Table.Buffer	46 MB	11 s	hotovo
10	Table.Join + rozbalený Column	bez bufferu	46 MB	11 s	hotovo
11	Record.Field (try ... otherwise false)	bez bufferu	46 MB	33–34 s	hotovo
12	Record.FieldOrDefault	List.Buffer	46 MB	11 s	hotovo
13	Record.FieldOrDefault	bez bufferu	46 MB	11 s	hotovo

* znamená odhadovaný celkový čas. Refresh jsem ručně stopnul v čase 2:25 a zobrazený čas je dopočítaný odhad, jak dlouho by dokončení pravděpodobně trvalo.

Vysvětlení jednotlivých přístupů

První načtení faktové tabulky bez jakýchkoliv úprav nebo lookupů.
Lookup přes List.Contains, kde nabufferuju tabulku a pak ji převedu na list odkazem na sloupec uvnitř lookup výrazu.
Tabulku převedu na list a nabufferuju, List.Contains pak odkazuje přímo na ten list.
Získám pozici hodnoty v listu. Pokud existuje, vrátí true, jinak false.
Použiju List.Select a pak List.IsEmpty pro true nebo false. Žádný List.Contains.
Výraz ve stylu: table{[lookupField = rowContent]}.
Použiju Table.Join jako LeftOuter, přidám lookup hodnotu a pak Table.Transform změní null na false a nalezené hodnoty na true.
Stejné jako 07, rozdíl je jen v bufferování.
Přidám join sloupec pomocí LeftOuter merge, rozbalím ho a pak stejná transformace jako v 07 nebo 08.
Stejné jako 09, rozdíl je jen v bufferování.
Seznam převedu na hash mapu (record/dictionary). Lookup přes Record.Field, pokud to spadne, vrátí false.
Stejná hash mapa jako 11, ale použiju Record.FieldOrDefault s fallback hodnotou false.
Stejné jako 12, rozdíl je jen v bufferování.

Je vidět, že funkce List dostaly na frak a jednoduché mergy přes GUI nebo ručně psané Mko vycházejí mnohem líp, i když na první pohled vypadají jako zbytečný overhead. Nejlepší výkon nakonec dá převést tabulku na list, ten list změnit na record jako hash mapu nebo slovník a pak použít Record.FieldOrDefault.

Record.FieldOrDefault

Co je tohle za magické stvoření? Na rozdíl od List.Contains neprohledává nic na pozadí. Když si správně připravíš list a převedeš ho na jeden record, žádné skenování se nekoná, jede se rovnou přes mapování klíče. Takže když chceš najít konkrétní hodnotu, víš okamžitě, kam sáhnout.

Představ si to takhle: znáš Shreka, že? A pokud ne, tak si dosaď svůj oblíbený film. Když se tě někdo zeptá: „Hele, který film má hlášku o cibuli, co má vrstvy jako nějaké bytosti?“, okamžitě střelíš odpověď: „To je Shrek, když vysvětluje Oslíkovi, kdo jsou zlobři.“ Proč ti to naskočilo hned? Protože to máš uložený v paměti. Record funguje stejně, data jsou uložená přímo v paměti, takže lookup je instantní.

Teď si vem List.Contains ve stejné situaci. Musel bys projet každý pohádkový film, dokud bys konečně nenarazil na Shreka. A to může bolet. Rychlost závisí na tom, jestli je Shrek na začátku fronty nebo se válí úplně na konci.

Jen pro pořádek; pokud máš v jednoduchém listu jen pár hodnot, klidně použij List.Contains. Výkonový rozdíl bude v takovém případě zanedbatelný.

Upozonění a limitace Record.FieldOrDefault

Jen slepé nahrazení List.Contains za Record.FieldOrDefault ti dotaz nemusí zrychlit, někdy ho to naopak zpomalí. Recordy se chovají hodně podobně jako buffering. Když chceš převést tisíce řádků ze streamovaného zdroje na list, musíš je nejdřív všechny natáhnout a zhmotnit do recordu. Tím ale zabiješ streaming, folding a vůbec všechny fajnové vychytávky M enginu. Jako vždy, musíš se zamyslet nad tvým konkrétním případem, protože tato technika bohužel není bez omezení.

Protože musíš materializovat celý rekord/záznam, velké rekordy můžou sníst celou paměť. Lokálně to asi nepoznáš, ale jakmile dáš takový semantický model do PBI Service, můžeš uplně v klidu zablokovat celou sdílenou kapacitu. Takže bacha na to.

Pokud pracuješ s jednoduchým zdrojem, který jen načteš a použiješ jako lookup tabulku, klidně sáhni po recordu. U streamovaných dat z REST API a složitějších transformací se ale vyplatí opatrnost. Často vyjde líp obyčejný join s pár kroky navíc. Zásada je jasná: nejdřív folding, pak omez řádky, vyždímej maximum z optimalizací a až pak zvaž převod na record. Ale jak ukázaly příklady, při správném použití se dá získat pěkný výkon navíc.

Převod listu na záznam (record)

Chceš si to zkusit sám. Pak je potřeba vědět, jak obyčejný list přetavit do recordu.

Princip je jednoduchý. List se převede na record, kde názvy polí odpovídají hodnotám v listu a každé pole má přiřazenou hodnotu true. Díky tomu elegantně napodobíš chování List.Contains, tedy kontrolu, jestli daná hodnota existuje.

Tady je náš původní list. Hlavní funkce pro převod listu na record je Record.FromList. To je celkem přímočaré. Oříšek nastává až ve chvíli, kdy je potřeba správně nastavit parametry.

Record.FromList(list as list, fields as any) as record
/*
Vrací record na základě listu s hodnotami a množiny polí. Pole lze určit buď jako list textových hodnot, nebo jako record typu. Pokud pole nejsou unikátní, funkce vyhodí chybu.
*/

Tak jak nastavit hodnotu na TRUE a názvy polí na hodnoty z listu

Když zkusíš:

= Record.FromList(list, list)

Dostaneš sice názvy polí podle svého listu, ale hodnoty nebudou TRUE, budou stejné jako názvy polí.

Co třeba tohle:

= Record.FromList({true}, list)

Hezký pokus, ale ne. Musíš dodat list o stejné délce, jako je počet polí. Pokud má list jen jednu hodnotu, funguje to. Jakmile jich má víc, spadne to, protože první parametr musí mít stejný počet položek jako pole. V našem případě tedy čtyři.

Abys nemusel TRUE psát pořád dokola, použij List.Repeat. Ta zopakuje libovolný list hodnot tolikrát, kolikrát jí řekneš. Pro čtyři položky to vypadá takto:

= List.Repeat( {true}, 4)

Teď to udělejme dynamicky. Spočítej, kolik položek má původní list pomocí List.Count, a tenhle počet použij jako hodnotu pro opakování. Výsledek vlož do prvního parametru funkce Record.FromList a druhý parametr nech jako původní list.

= Record.FromList( List.Repeat( {true}, List.Count(list) ), list)

Filtrování s Record.FieldOrDefault místo List.Contains

Jak se zbavit List.Contains a přejít na Record.FieldOrDefault? Úplně jednoduše. Pokud potřebuješ filtrovat položky, které se nacházejí v listu, a normálně bys použil List.Contains, tady je rychlá alternativa.

Původní tabulka:

Filtrování přes List.Contains:

= Table.SelectRows(changeTypes, each List.Contains( list, [letters] ) )

Filtrování přes Record.FieldOrDefault:

= Table.SelectRows( changeTypes, each Record.FieldOrDefault(rec, [letters], false ) )

Kde rec je record vytvořený z:

= Record.FromList( List.Repeat( {true}, List.Count(list) ), list)

Poté hledáme název pole podle hodnoty v aktuálním řádku sloupce letters. Pokud se dané písmeno nenajde, použije se fallback hodnota false. Výsledkem je čistá a bezchybná (doslova) náhrada za List.Contains se stejným chováním.

Record.FieldOrDefault jako náhrada merge nebo join při explicitně typovaných sloupcích

Technicky jde tuhle metodu použít nejen pro lookupy, ale i na "mergování/join" a rozbalení sloupců, které po rozbalení rovnou vyjdou typované. Běžně bys pro spojení a rozšíření hodnot sáhl po Table.Join nebo jiných variantách joinu. Nevýhoda je, že po rozbalení často musíš sloupce znovu přetypovat. S Record.FieldOrDefault přidáš nový sloupec jako record a typy nastavíš hned na začátku.

Když si postavíš record takhle (budou všechny stejné pro příklad):

Opakování hodnot recordu pro jednotlivá pole

Můžeš provést „merge“ podobně jako při filtrování nebo když vytváříš sloupec s hodnotami true/false pro kontrolu, jestli se hodnota nachází v listu.

= Table.AddColumn(
    prevStep,
    "key_lookup",
    each Record.FieldOrDefault(
        lookup_field_big,
        [key_text]
    ),
    type [
        logical = logical,
        word    = text,
        date    = date
    ]
)

Tady mergujeme record klasickým způsobem, ale tentokrát bez fallback hodnoty. To znamená, že řádky bez shody vrátí null, zatímco řádky se shodou vrátí record. Protože přidáváme přímo record, můžeme rovnou využít čtvrtý parametr funkce Table.AddColumn a nastavit datový typ sloupce. V tomhle případě jde o record, kde pro každé pole určíme typ explicitně.

Při pozdějším rozbalení už mají pole správné datové typy, takže je nemusíš opravovat ručně. To většinou zlepší i rychlost refreshu. A ještě jedna poznámka: pro výkon prakticky nezáleží, jestli se shody najdou, nebo ne.

Poznámka

Jak uvidíš později, tenhle přístup může být výrazně rychlejší. Ale pozor: je rychlejší i proto, že při rozšiřování předem typovaného sloupce se hodnoty znovu netypují, jen se to bere jako metadata. Pokud máš data už správně otypovaná, je to úplně v pohodě.

Jestli jsi ale původně ukládal třeba text, který vypadal jako čísla, a teď chceš mít sloupec opravdu číselný, sloupec sice bude mít typ číslo, ale hodnoty zůstanou textové. A to tě může později pěkně dohnat. Tak na to bacha.

Proč na typech záleží a srovnání rychlosti

Na závěr jsem pustil pár testů, které ukazují, že mapování přes record s Record.FieldOrDefault pro lookup nebo merge je v tomhle kontrolovaném prostředí rychlejší než Table.Join, zvlášť když lze rozbalovat se zadanými typy. Použil jsem stejnou faktovou tabulku jako dřív a tentokrát jsem sledoval, jak se čas mění podle počtu polí, která je potřeba zpracovat.

Slučování se 100 tisíci lookup hodnotami

ID	Metoda	Strategie typování	Průměrný čas (s)
01	Table.NestedJoin + rozbalení	Změna typů na začátku, pak znovu po rozbalení	14.40
02	Record.FieldOrDefault merge + rozbalení	Změna typů na začátku, rozbalené sloupce už typované	12.75

Slučování s 1 milionem lookup hodnot

ID	Metoda	Strategie typování	Průměrný čas (s)
01	Record.FieldOrDefault merge + rozbalení	Změna typů na začátku, rozbalené sloupce už typované	13.7
02	Table.NestedJoin + rozbalení	Změna typů na začátku, pak znovu po rozbalení	30
03	Table.NestedJoin + rozbalení	Změna typů na začátku, po rozbalení už bez změn	28
04	Table.NestedJoin + rozbalení	Bez změny typů	19
05	Table.NestedJoin + rozbalení	Změna typů jen po rozbalení	23

Poznámky:

Změna typů na začátku – typy jsem měnil přímo v původní tabulce s pěti sloupci ještě před sloučením.
Bez bufferování – u Table Joinů to totiž vycházelo hůř

I když to s merge jako takovým úplně nesouvisí, z tabulek je vidět, že krok Změna typů může stát hodně výkonu. Pokud v Power Query klikáš cestu krok po kroku, tenhle krok se často přidá automaticky, takže typy pak měníš víckrát.

Doporučení: nejdřív udělej všechny kroky, které potřebuješ k finálnímu tvaru tabulky, a pak teprve řeš změnu typů a jen tam, kde to dává smysl. Pokud máš všechno jako text, můžeš to teoreticky nechat jako „any“. Jasně, lepší je mít typy definované, ale stačí jednou a jen tam, kde jsou opravdu potřeba, jinak si zbytečně ubereš na výkonu.