Další zdroje

Jak použít materiály z programu

Proces analytické práce

Organizace projektu

Názvy

…souborů, proměnných, někdy i složek, projektů, repozitářů

Jenny Bryan: Naming Things (slides) + How to name files slides video (5 min.)

Indrajeet Patil: Dealing with the Second Hardest Thing in Computer Science (slides)

(zdroj)

Organizace tabulkových souborů

Organizace dat v souborech

Broman a Woo (2018)

Zveřejňování a předávání Excelových souborů

Analysis Function Central Team, ONS (2021a)

Analysis Function Central Team, ONS (2021b)

Kontrola kvality

„The Aqua Book: Guidance on Producing Quality Analysis for Government" (2015)

„Quality assurance of code for analysis and research" (2022)

Jednotlivé kroky

Načtení dat

Vysvětlení rozdílu načtení dat „přímo“ versus přes PowerQuery - dokument od Microsoftu

Poznámka

Power Query se v novějších verzích Excelu postupně přejmenovává na „Načíst a Transformovat data“, ale v některých částech rozhrani název PowerQuery zůstává:

The average user doesn’t use the word „Query“ to describe the tasks they undertake. Put the word „Power“ in front and nothing is any clearer.

Maybe this is why Microsoft changed the name to Get & Transform in Excel 2016 and placed it in the Data ribbon. I am sure they were trying to give the user more clarity. While it may be labelled Get and Transform in Excel 2016 and later, the name Power Query appears to have stuck with the Excel community.

Excel PowerQuery a reprodukovatelná analýza

Čím se liší od běžné práce v Excelu

  • umožňují načíst externí data; v Excelu je pouze jejich odraz po transformaci a čištění. Např. na disku leží CSV soubor, excelový soubor obsahuje popis toho, jak se data načtou a upraví
  • v listu v excelovém souboru uvidíte výsledek této transformace
  • máte přístup k širšímu spektru formátů
  • každý datový soubor

Hrubá data v tom stejném nebo jiném excelovém souboru mohou sloužit jako zdraj dat pro PowerQuery

Analogické funkce existují i v PowerBI - pokud se tedy naučíte s PowerQuery v Excelu, bude pro Vás snadná práce při načítání a úpravě dat v PowerBI.

Úvod do PowerQuery (anglicky)

Úvod do PowerQuery of Microsoftu (anglicky)

Základní úvody do PowerQuery česky

  • (http://home.zcu.cz/~kryl/DBM2/power-bi-3-transformace.html)

Proč PowerQuery používat

  • máme popsaný postup úpravy dat
  • můžete vytvářet podskupiny a transformované verze jedné tabulky, které se aktualizují, pokud se změní zdrojová data nebo proces úpravy
  • při načtení dat můžete explicitně popsat datové typy, je tedy menší riziko, že dojde např. k chybnému formátování čísel jako datumů
  • některé operace těžko proveditelné v běžném Excelu jsou v PowerQuery snadné (Unpivot (kontingenční sloupec); pokročilá extrakce částí textových řetězců)
  • tvorba derivovaných sloupců je v PowerQuery intuitivnější
  • operace, které v Excelu provádíte kontingenční tabulkou, jsou v PowerQuery proeditelné intuitivněji a jejich výsledkem je normální datová tabulka, se kterou lze (narozdíl od kontingenční tabulky) snadno dál pracovat bez potřeby speciálních vzorců a kopírování dat
  • propojování dat (např. napojení číslelníků) je v PowerQuery uživatelsky snadnější a výpočetně rychlejší; navíc můžete opravdu propojit dvě tabulky namísto postupného připojování jednotlivých sloupců, jak je nutné s využitím funkce SVYHLEDAT/XVYHLEDAT
  • máte možnost vidět a editovat přímo strojový kód, který transformace dat provádí (Excel zaznamenává jednak jednotlivé kroky ve formě tlačítek, která můžete mazat nebo upravovat, jednak ve formě kódu, který můžete přímo editovat)
  • pokud máte několik souborů stejné struktury, které chcete analyzovat společně (např. výkazy z různých resortů), můžete je snadno načíst společně, bez kopírování a vkládání

Jak tedy o práci s PowerQuery přemýšlet

  1. Někde na disku nebo jinde sedí datový zdroj
  2. Excelový soubor obsahuje instrukce, díky kterým Excel data načte, vyčistí a upraví. Každý datový zdroj má v levé části svou ikonu; když na ni kliknete, zobrazí se náhled dat a v pravém sloupci posloupnost kroků úpravy dat
  3. Stejně tak v PowerQuery můžete data spojovat nebo vytvářet podvýběry či transformované verze jednotlivých datových tabulek (klikněte pravým na krok úpravy v pravé liště a vyberte „Extract Previous“) nebo klikněte pravým na dotaz (Query) v levé liště a vyberte „Odkaz“. V obou případech vznikne nový Dotaz, jehož kroky končí, resp. začínají tam, kde jste klikli. Znamená to, že můžete používat data vzniklá v různé fázi transformace z datového zdroje. Podobně můžete dotaz duplikovat.
  4. Výsledek každého procesu můžete načíst do listu v Excelu. Pokud se změní zdrojová data nebo postup jejich úpravy, aktualizují se i data načtená do listu v Excelu.
  5. S daty v listu v Excelu můžete dál pracovat

Pozn.: pokud datový zdroj (např. CSV soubor na disku) bude nedostupný, Excel sice nebude schopen data z něj znovu načíst/aktualizovat, ale výsledek načtení a transformace dat bude stále dostupný.

  • Některé operace lze udělat dvěma způsoby:
    • v PowerQuery jako součást procesu načtení a transformace
    • následně v Excelu v listu s načtenými daty pomocí vzorečků

Kaduk (2018)

Tipy pro práci s PowerQuery

  • jednotlivé Dotazy (levá lišta) a kroky (pravá lišta) můžete přejmenovávat (dvojklik) a doplňovat k nim popisky (pravý klik => Vlastnosti)
  • stejně tak se přejmenovávají sloupce
  • místo Zpět (Ctrl+Z) jednoduše zrušte poslední krok v pravé liště
  • v novějších verzích Excelu jde zobrazit celá síť vztahů mezi Dotazy (jejich propojení, vzájemné odvození): Zobrazit > Diagram
PowerQuery a PowerPivot

PowerPivot je nástroj na modelování dat. Je součástí novějších verzí Excelu. Umožňuje explicitně popsat vztahy mezi tabulkami a pracovat s několika tabulkami jako s databází.

Co ještě v Excelu

Kde hledat pomoc

Excel a dál

R a Python

Verzování kódu: Git a Github

Zdroje

Zdroje

Analysis Function Central Team, ONS. 2021a. „Creating and Sharing Spreadsheets". https://www.gov.uk/guidance/creating-and-sharing-spreadsheets.
Analysis Function Central Team, ONS. 2021b. „Releasing Statistics in Spreadsheets". https://analysisfunction.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/#section-14.
Broman, Karl W., a Kara H. Woo. 2018. „Data Organization in Spreadsheets". The American Statistician 72 (1): 2–10. https://doi.org/10.1080/00031305.2017.1375989.
Kaduk, Taras. 2018. „Taras Kaduk: Power Query: Excel’s gateway to reproducible analysis", březen. https://taraskaduk.com/posts/2018-03-29-power-query/.
„Quality assurance of code for analysis and research". 2022. https://best-practice-and-impact.github.io/qa-of-code-guidance/intro.html.
„The Aqua Book: Guidance on Producing Quality Analysis for Government". 2015. GOV.UK. https://www.gov.uk/government/publications/the-aqua-book-guidance-on-producing-quality-analysis-for-government.