Materiál k cvičení INS týden #4, sezona 2023/2024
Transformace dat pomocí Power Query v Power BI
Aplikace Power BI používá integrovaný nástroj ke zpracování dat s názvem Power Query. S tímto nástrojem se můžete mimo jiné setkat i v kancelářském software MS Excel. Z prostředí Power BI Desktop se do rozhraní Power Query lze nejjednodušeji dostat přes ikonu Home → Transform Data. Uživatelské rozhraní sestává z jednoho okna, kde v centrální části je náhled na aktuálně zpracovaná data, nahoře je karta ikon pro běžné transformace a vpravo je posloupnost definovaných transformací. Entita v tomto prostředí se nazývá query a seznam těchto entit v rámci otevřeného projektu je v levém sloupci.
Fig — Prostředí Power Query editoru. (1) Karta s odkazy na běžné operace; (2) seznam query v otevřeném Power BI projektu; (3) příkazový řádek; (4) aktuální pohled na data; (5) seznam aplikovaných transformací na data; (6) stavový řádek s metadaty.
V příkazovém řádku (3) lze vidět kód v jazyku M sloužící k definici transformací. Tento jazyk je specifický pro Power Query a je založen na funkcionálním programování.
📝 Pro další práci bude využit projekt vytvořený v předchozích cvičení využívající Přehled osob s prokázanou nákazou dle hlášení krajských hygienických stanic (v2).
Q1: Jaké je momentální pořadí transformací (5) nad načteným CSV souborem v projektu?
Prozkoumejte, jak se změní data při přepnutí na některou z předchozích transformací.
Změna tvaru dat
V sekcí Home → Manage Columns jsou dvě ikony pro základní transformaci měnící dimenzionalitu dat — odstranění atributů (sloupců). Rozdíl mezi nimi je pouze v tom, jakým způsobem se sloupce zvolí. Ikona Choose Columns nabídne seznam atributů a zaškrtávacím políčkem uživatel indikuje ty, které mají zůstat. Oproti tomu Remove Columns odstraní momentálně vybrané sloupce, případně je v podnabídce možnost výběr logicky negovat.
Pro potřebu redukce řádků (záznamů) existuje dvojice ikon v sekci Home → Manage Rows, první z nich je pozitivní, tj. nechá záznamy vyhovující zvolené podmínce, druhá je negativní, tedy smaže záznamy vyhovující podmínce. Podmínky zde nemohou být příliš komplexní. Lze odstranit horních, resp. dolních N řádek, řádky obsahující chybu, řádky prázdné nebo řádky v nějakém ohledu duplicitní. Duplicitou se rozumí kombinace hodnot ve vybraných sloupcích. V řešeném datasetu by při selekci atributů Pohlaví a NUTS3 (kraj) a volbě odstranit duplicity zbylo jen 30 záznamů - první muž a první žena pro každý kraj při průchodu seřazenými daty shora.
※ I když ve své podstatě není nezbytné redukovat dimenzionalitu dat již v tomto kroku, je to vhodné kvůli úspoře výpočetního času a paměti. Typicky tato redukce bývá jednou z prvních operací v řetězci transformací.
Q2: Jakým způsobem se různé pokyny k transformaci přepíší do jazyka M.
Filtrování záznamů, změna datového typu, přejmenování atributů
Kromě horní karty s ikonami je v uživatelském rozhraní i několik jiných aktivních prvků. V záhlaví datové tabulky jsou u jména atributu dvě tlačítka. Levé z nich slouží jako přepínač datových typů. Vhodné nastavení datových typů je důležité pro správnou funkcionalitu vizualizací a agregací. Pokud by atribut s číselným charakterem byl definován jako text, nepůjde jeho hodnoty numericky agregovat a změní se i způsob jeho řazení.
Filtrovací ikona vpravo od názvu atributu skrývá podobnou nabídku jako v prostředí MS Excel. Nachází se zde možnost data seřadit podle hodnoty v příslušném sloupci a vyfiltrovat záznamy dle hodnoty v daném sloupci. Filtrace lze provést určením příslušného pravidla (číslo v rozmezí od-do, text začínající určitým řetězcem, datum vzdálené maximálně X dnů od dneška, ...) nebo zaškrtnutím konkrétních hodnot. Je potřeba si dát pozor, že ve výpisu hodnot nemusí být uvedeny všechny, které se nachází v datasetu. Není-li nastaveno jinak, nástroj se orientuju pouze podle prvních 1000 záznamů. Takto vyfiltrovaná data budou opět odstraněna z datasetu, stejně jako v případě odstraňování řádek.
Pro přejmenování atributu stačí 2x kliknout na název, aby se zobrazilo pole pro úpravu.
Agregování záznamů
Nástroj nabízí dvě agregační operace, které nějakým způsobem slučují podobné záznamy a vyčíslují jejich kombinovanou hodnotu atributu. Prvním z nich je operace Group By, která by měla být známa z jazyka SQL. Příslušná ikona je Transform → Group By. Po stisknutí se objeví dialog, ve kterém je potřeba zvolit atribut, podle kterého se budou data slučovat, a operaci aplikovanou na vybraný sloupec. Pokud uživatel zvolí advanced, nikoliv basic mód, má možnost pracovat s více atributy.
Fig — Group By operace k získání informace o souhrnném počtu a věku pacientů dle krajů a pohlaví.
Q3: Jaký byl počet nakažených v jednotlivých okresech v Plzeňském kraji v dubnu 2020?
Odpověď
Domažlice - 236;Klatovy - 70;
Plzeň-město - 60;
Plzeň-jih - 29;
Plzeň-sever - 9;
Rokycany - 5;
Tachov - 32;
※ Položky, ke kterým není v datech žádný záznam, se ve výsledku Group By operace nezobrazují.
Druhým agregačním mechanismem je pivotování sloupců. Při označení sloupce a zvolení Transform → Pivot Column je v dialogu nutné nastavit, jaký sloupec má charakter sumarizované veličiny a zvolit příslušnou funkci. Po potvrzení dojde k operaci, při které se místo původního sloupce vytvoří N nových sloupců, jeden pro každou unikátní hodnotu vyskytující se v původním sloupci. Řádky budou redukovány tak, aby zbyly pouze unikátní kombinace hodnot zbylých atributů. V nově vytvořeném sloupci pak budou hodnoty odpovídající sumarizované veličině pro příslušnou kombinaci atributů.
Př. uvažujme dataset s atributy datum, věk, pohlaví, kraj. Po označení atributu pohlaví k pivotování volíme atributu věk s funkcí count k agregování. Výsledná tabulka obsahuje původní sloupce datum a kraj. Kromě toho vznikly dva sloupce - pohlaví_Z a pohlaví_M ve kterých jsou hodnoty počtu nových případů v daném kraji a dni.
K operaci pivot existuje i obrácená (nikoliv reverzní ve smyslu získání původních hodnot) operace Transform → Unpivot Columns. Je potřeba označit množiny sloupců, které odpovídají kategoriím budoucího atributu. Po provedení se roznásobí počet řádek počtem odpivotovaných sloupců a vzniknou dva atributy. Jedním z nich je atribut obsahující kategorie z hlaviček sloupců, druhý z nich je sloupec s hodnotami.
Správa transformačního řetězce, klonování query
Každá použitá operace je uváděna v pravé části uživatelského rozhraní. Kliknutím na různé kroky v seznamu lze zobrazit náhled datové tabulky v příslušné fázi. Pokud by chtěl uživatel některý z kroků smazat nebo změnit jeho parametry, lze k tomu použít ikonu křížku vlevo od názvu kroku, resp. ikonu ozubeného kola vpravo od názvu kroku. Je potřeba si ale dát pozor na kroky následující, které pravděpodobně předpokládají určitou strukturu nebo vlastnost dat. Nové transformace lze také vkládat mezi jednotlivé kroky a není nutné je umisťovat na konec posloupnosti transformací.
Užitečná může být možnost naklonovat query, nebo vytvořit query, která se bude na jinou odkazovat. To lze provést přes nabídku po kliknutí pravým tlačítkem myši na název query v seznamu vlevo. Volba Duplicate vytvoří kopii bez provázání, Reference se bude odkazovat na výsledný dataset po všech úpravách. Pokud by později došlo k změně transformačního řetězce v referované query, projeví se změny i ve všech navázaných.
K uvědomění
Q: Co je to query v kontextu Power Query?
Q: V čem se liší výsledek operace Group By a operace Pivot?
Q: Jaký je rozdíl mezi možnostmi duplikovat a referencovat query? Příklady použití?