Power Query ve firemní praxi – případová studie z firmy Verkon

Power Query (Načíst a Transformovat) je velmi užitečný doplněk. Na následujícím příkladu chci ukázat, jak nám pomohl v jedné konkrétní firmě vyřešit konkrétní úkol.

Zadání

• Naším zákazníkem je firma Verkon, významný hráč na trhu potřeb pro vědecké laboratoře. Firma potřebuje optimalizovat své zásoby. Tak, aby dokázala uspokojit co nejvíce zákaznických objednávek ihned ze skladových zásob, ale současně aby neskladovala příliš široký sortiment. Je třeba pracovat s různými prioritami zboží, zkoušet různé míry vykrytí objednávek atd.
• Za tímto účelem jsme vytvořili model, kde je možné sledovat dopady různých variant naskladňování na možnost vyřizování objednávek.
• V tomto článku se zaměříme především na přípravu dat pro model. K dispozici jsou data stažená z Heliosu –  jedna nebo více obdobných tabulek ve formátu xls. Tabulka obsahuje seznam jednotlivých fakturovaných položek s informací o zboží, čísle objednávky atd. Tato data je třeba pro modelování poměrně hodně pospojovat, vyčistit, upravit.

Jak výsledný report funguje

• Uživatel stáhne z Heliosu soubor s informacemi o prodejích za vybrané historické období (například za posledních 12 měsíců) a uloží ho do předem připravené složky. 
• Pak uživatel otevře Excel s hlavním analytickým reportem. 
• Kliknutím na tlačítko načte data a provede nad nimi výpočty, tím se vytvoří požadované výstupy.

Jak pomáhá Power Query

Power Query jsme použili hlavně k přípravě dat pro model, kdy jsme potřebovali dostat seznam všech prodejů, seznam všech objednávek a seznam všech druhů zboží.

Vytvořili jsme několik na sebe navazujících dotazů. První dotaz je kombinací tří zdrojů

• dotazu, který najde všechny soubory v zadaném adresář (souborů může být i více),
• vlastní funkce, která z těchto souborů vytáhne data (podrobněji o tomto triku tady),
• buňky, ve které je definovaná cesta ke složce se soubory.

U dotazů je tam uvedeno “Nenačtené”, protože tyto dotazy ještě nejsou určené k načtení do sešitu – jsou to jen zdroje pro jiné dotazy (o návaznosti dotazů více tady).

Současně se spojením dat se také data upraví, např.:.

• Přejmenují se sloupce
• Změní se pořadí sloupců
• Z některých sloupců se extrahuje pouze část textu
• Vytvoří se unikátní ID objednávek spojením jiných sloupečků

Takže z nepřehledné sjetiny z Heliosu vzniká tento dotaz, ze kterého by šla už udělat docela slušná tabulka:

My z tohoto dotazu ale ještě vytvoříme tři dotazy dílčí, které potřebujeme pro modelování. (Z určitého důvodu je zařazený i mezikrok, který umožňuje model omezit pouze na jeden vybraný sklad). Dotaz Prodeje je víceméně jen mírně upraveným původním dotazem. Dotazy Objednávky a Druhy zboží vznikly seskupením, resp. odstraněním duplicit a několika dalšími úpravami.

Tyto tři dotazy už se načítají přímo do tabulek v listech, kde se k nim dopočítávají hodnoty pro modelování logistiky skladů.

Pak následuje řada vzorečků a jednoduchý VBA kód, který jejich použití automatizuje. Výsledkem mohou být tabulky jako je tato:

nebo tato:

Přesnou logiku modelu tady neuvádím, obě tabulky zjednodušeně prezentují procenta kompletně vyřízených objednávek v závislosti na změnách různých vstupních proměnných. Modely mohou být různé pro všechno zboží i pro zboží z různých kategorií. (Na screenshotech nejsou skutečná čísla z firmy.)

Jak dlouho nám trvalo tohle vytvořit

Celkově kolem čtyř člověkodnů včetně řešení logiky a konzultací.

Samotné načítání a úprava dat by se daly zvládnout asi za 4 hodiny – takže víc práce trvalo napsat o tom případovku 🙂 Kdybychom to řešili pomocí VBA, což by asi také šlo (a ještě před dvěma roky bychom na to tak šli), bylo by to mnohem zdlouhavější a velmi dlouho by trvalo ladění a odstraňování chyb.

Power Query si prostě člověk musí zamilovat 🙂