Power BI
desktop
Pavel Lasák - Excel, Power BI, G-tabulky
Načtení Vizualizace SdíleníÚprava Propojení
Power BI Desktop
Soubory (xls, csv, pdf)
Složky
Web
Databáze
….
Práce s řádky
Úpravy se sloupci
Řazení filtrování
Transformace
M - jazyk
Propojení tabulek
Slučování
Hierarchie
Vazby
DAX
….
Grafy
Ovládací prvky
Texty
Obrázky
….
Pdf
Csv
Cloud
….
Co se v načtených datech nelíbí
• Víme co se nám v datech nelíbí
• Název tabulky?
• Řádky „navíc“ – vybrat (filtr), řazení
• Záhlaví
• Hodnota Null, Chyby
• Sloupce (umístění, název, co typ sloupce? …) – rozdělit/sloučit
• Nechybí výpočet?
• Co další sloupec
• Data z jiné tabulky?
Power Query Editor – transformace dat
• Popis nástroje - Power Query Editor vs Editor Power Query
• Práce s tabulkou
• Práce s řádky
• Filtrování, řazení
• …
• Práce se sloupci
• ….
• Co po aktualizaci
• …
Power Query Editor vs Editor Power Query
•Česká verze: Editor Power Query
•Anglická verze: Power Query Editor
Vstup do Power Query Editoru
• Při načítání dat > Transformovat data (minule)
• Menu
• Report Domů (Home)
• Transformovat
• (Transform)
Popis – Power QUERY
(0) – Rychlý pas karet
(1) Pás karet – práce s ikonkami
(2) Řádek vzorců - zobrazuje se co se děje v rámci daného kroku
• viz Rozšířený editor – pojmenování kroků
(3) Dotazy - neboli, které tabulky máte načtené (vlastní seznamy atd. viz M-ko).
(4) Data z tabulky výběr z vašich data
(5) Nastavení dotazů – Vlastnosti a kroky které v úpravě provedete – viz M-ko, Rozšířený editor
(6) Stavový řádek - obsahuje zajímavé údaje. Proto zde zmiňuji.
(7) Info o „buňce“
• (*) Kontextové menu - pravý klik a Query Editor vám nabídne co lze provádět, většinou
nejrychlejší cesta k výsledku.
Power Query Editor – Úprava/nastavení
• Zobrazení řádku vzorců
CZEN
Transformace vs Přidání sloupce
Transform vs Add Column
CZEN
Rozšířený editor (Advanced Editor)
Rozšířený editor …
•Tip: Sledovat řádek vzorců…
Ukládání, Aktualizace
• Budeme-li mít hotovo/potřeba aktualizovat
Načítání dat
• Netřeba načítat/vracet se do Power BI…
• Tip: Power Query Editor v Excel má načítaní napravo 
Klávesové zkratky – s Alt
• Alt (stejně jako Excel)
• Alt , F, S - Uložit
• Alt , F, A – Uložit jako
Klávesové zkratky - Sloupce
• ➔ – přesun sloupce (zruš označené sloupce)
• Shift + ➔ - Přidání dalšího sloupce
• Ctrl +➔ další sloupec
• Ctrl + mezerník – výběr (dalšího) sloupce
• F2 – přejmenovat sloupec
• Delete - smazat sloupec
Klávesové zkratky - Sloupce
• Ctrl + A – všechny sloupce označit
• Ctrl + G – skok do sloupce
• Ctrl + E – sloupec z příkladu
• …
Klávesové zkratky - Buňky
• End - První buňka v řádku
• Home - Poslední buňka v řádku
• PgUp – První buňka ve sloupci
• PgDn – Poslední buňka ve sloupci
Klávesové zkratky – PQE „prvky/sekce“
• Tab - přepínaní mezi prvky na Power Query - dopředu
• Shift + Tab - přepínaní mezi prvky na Power Query – dozadu
• Enter, Mezerník – zobrazení menu (musí být vybráno )
Tabulátor mezi „prvky“ Power Query Editor
•
1
2
3 4
5
6
7
8
9
10
11
12
13
Tabulátor mezi „prvky“ Power -BI
•
1
2
3 4
5
6
7
8
9
10
11
12
13
Klávesové zkratky – Power Query - obecné
• Alt + F4 – zavřít Power Query Editor
• Ctrl S – Uložit / Uložit jako
• F12 – Uložit jako
PQE - Rychlý panel - přizpůsobit
• Pravý klik nad ikonou
• Přidat na panel nástrojů Rychlý přístup
• Výsledek
Proč se úpravou dat zabývat?
• Čištění dat nejvíce času
• Data nejsou v dobré kondici
• Využijete
• Zpřehlednění vizuálu
• Zrychlení práce
• Usnadnění v DAX
Power Query Editor – Práce s „tabulkou“
Co se nelíbilo?
• Jméno tabulky? (Page-001, sddgofdsat > Přejmenovat)
• Nebudeme chtít testovat (mít duplikát)
• Pokračovat s výsledkem (odkaz)
• Načteme špatnou tabulku … zrušit
Power Query Editor– Práce s „tabulkou“
Jak pracovat se zdrojem dat (načtenou tabulkou)
oPřejmenovat
oDuplikovat
oOdkaz
oZrušit
Power Query Editor– Prakticky
•Data
A
Power Query Editor– Pojmenování zdroje
• Vlevo • Vpravo
• Proč? Přehlednost viz Vazby (Relace), Tvorba vizuálu
A
Power Query Editor – Tip - vlastnosti
• K názvu „tabulky“
• Popis
• Povolení načtení
• Zahrnutí do aktualizace sestav
A
Power Query Editor – Duplikovat
• Pravé tlačítko • Menu
A
EN
CZ
Power Query Editor – Odkaz (Reference)
• Pravé tlačítko • Menu
ACZ
Power Query Editor– Duplikovat vs Odkaz
• Duplikovat
• Duplikuje se aktuální stav
• Pokud se zdroj změní nezmění se
• Odkaz
• Odkazuje se na výsledek původní tabulky
• Pokud se změní zdroj, změní se také odkaz
A
Power Query Editor – Zrušit
• Pravé tlačítko > Smazat • Menu
A
Power Query Editor – Pořádek
• Jak na velký počet Dotazů (Tabulek)
• Vkládání do skupin
A
Power Query Editor – Pořádek
• Přesouvání
• Přejmenovávání
• Mazání
• …
A
Power Query Editor – Prakticky
• Vyzkoušet prakticky
• duplikovat odkaz,
• zrušit
• přejmenovat
A
Než začneme upravovat data
• Co chceme zjistit?
• Kvalitu dat?
• Co chybí/nechybí?
• Zobrazení (typ fontu)
• Staří dat (aktualizace)
• Vědět
• Kam
• Pro koho
• …
Než začneme upravovat – Co nám pomůže
• Písmo
• Proporcionální / Neproporcionální
• Prázdné znaky
• Kvalita sloupce
• „puntíky“
• Distribuce sloupce
• „jak často se hodnota opakuje“
CZEN
Power Query Editor – Ukázky nastavení
•
Power Query Editor – Profil sloupce
Power Query Editor – Tip
•Chcete-li vyhodnotit mimo Power Query Editor
•pravým klik na vybraném sloupci
• Kopírovat distribuci hodnot (Copy Value Distribution)
CZ
EN
Výsledek
• Už víte so potřebujete upravit?
Power Query – Práce s řádky
• Záhlaví
• Odebrat řádky
• Zachovat řádky
• Obratit řádky
• Chyby/Duplicity/Prázdné
Power Query – Práce s řádky - Záhlaví
• (Ne)Použít první řádek jako záhlaví
o Použít první řádek jako záhlaví (Use First Row as Headers)
o Použít záhlaví jako první řádek (Use Headers as First Row)
Karta domů, Transformace, pravý klik nad tabulkou
B
Power Query – Práce s řádky - Odebrat
• Odebrat řádky
o Odebrat horní řádky (Remove top Rows)
o Odebrat dolní řádky (Remove Bottom Rows)
o Odebrat střídavé řádky (Remove Alternate Rows)
o Odebrat duplicity (Remove Duplicates)
o Odebrat prázdné řádky (Remove Blank Rows)
o Odebrat chyby (Remove Errors)
B
Power Query – Práce s řádky - Odebrat
• Tip – Filtr viz dále
B
Power Query – Práce s řádky - Zachovat
• Zachovat řádky
o Zachovat horní řádky (Keep Top Rows)
o Zachovat dolní řádky (Keep Botom Rows)
o Zachovat rozsah řádků (Keep Range of Rows)
o Zachovat duplicity (Keep Duplicates)
o Zachovat chyby (Keep Errors)
B
Power Query – Práce s řádky – Obratit
Karta Transformace (Transform)
• Obrátit řádky (ReversevRows)
• Počet řádku (Count Rows)
B
Power Query – Práce s řádky - Prakticky
Zdroj dat
B
Power Query – Práce s řádky - Prakticky
Otestujte práce s řádky
B
Power Query – Práce s řádky - Prakticky
Řešení
B
Jak se zbavit řádku jinak?
• Filtrování
• M-ko
• „Zopakovat“ první řádek jako záhlaví
Kroky – Činnosti – Použitý postup
• Každý krok se zaznamená
• Co lze provádět s kroky?
• Zrušit (odstranit)
• Změnit nastavení
• Poznámka (M)
• Krok bez možnosti nastavení
Pojmenování kroků
• Tip na pojmenování kroků
• Bez hacku a carek
• Využít VelblodiKonvenci
• Proč?
• Využití při práci v M-ku
Vložení Kroku
• Pozor kam vkládate
CZEN
Smazání kroku
• Pozor při mazání kroků
Tip:
Vědět co chceme na začátku!
CZEN
Chyby v krocích
• Pokud vložíte chybně
• Chybová zpráva
• Někdy lze upravit
• „viz hláška“
První krok
• V prvním kroku vyberte nastavení
• Nastavte cestu
• Jiný způsob?
• V PowerBI celkově
Power Query Editor – Řazení a filtrování
• Řazení
• Abecedně
• Velikostně
• Nejstarší / nejmladší
• Filtry
• Číslo
• Text
• Datum/Čas
• Viz Buffer v M-kódu
Power Query Editor – Řazení
• Seřadit řádky
• Abecedně Od A do Z
• Velikostně
• Nejstarší / nejmladší
• M-ku nezáleží na pořadí řádku
• například následně odstraňovat duplicity
• Nemusí odstranit hodnoty od konce (jako Excel)
• Viz Table.Buffer
• https://learn.microsoft.com/cs-cz/powerquery-m/table-buffer
C
Power Query – Filtrování - Text
• „Zaklikávat“
• Vybírat
• Rovno
• Není rovno
• Začíná na …
• Nezačíná na …
• Končí na …
• Nekončí na …
• Obsahuje …
• Neobsahuje …
C
Power Query – Filtrování - Číslo
• Jak lze vybírat?
• „Zaklikáváním“
• Výběrem
• Je rovno
• Není rovno
• …
C
Power Query – Filtrování - Datum
• Co lze vybírat?
• …
C
Power Query – Filtrování - Čas
• Co lze vybírat?
C
Power Query – Filtrování - Prakticky
• DATA
C
Prostor pro poznámky
Power Query – Sloupce základ
o Přejmenovat
o Datový typ
o Přesouvat sloupce
o Označování sloupců
o Odebrat sloupce
o Šířka
Power Query – Sloupce základ - přejmenovat
• Dvojklik
• Pravé tlačítko
• Menu Transformovat
D
Power Query – Sloupce základ – Šířka
oZvětšit šířku sloupce lze
oMinimální šířka
D
Power Query – Sloupce základ – Označovat
o Označování sloupců
▪ Vybrat sloupce
▪ Přejít do sloupce
• Pravé tlačítko
• Menu
• Myš a Ctrl, Shift
D
Power Query – Sloupce základ – Přesouvat
• Přesouvat sloupce
▪ Vlevo
▪ Vpravo
▪ Začátek
▪ Konec
• Pravé tlačítko
• Menu
D
Power Query – Sloupce základ – Datový typ
Důležité
o Datový typ
▪ Desetinné číslo
▪ Číslo s pevným počtem
desetinných míst
▪ Celé číslo
▪ Procento
▪ Datum/Čas
▪ Datum
▪ Čas
▪ Datum/Čas/Časové páísmo
▪ Trvání … viz dále datum a čas > Trvání
▪ Text
▪ Pravda/Nepravda
▪ Binární
▪ Rozpoznat
D
Power Query – Sloupce základ – Datový typ
• U sloupce
• Levý klik
• Nad sloupcem
• Pravé tlačítko
• Menu
• Data Type …
D
Power Query – Sloupce základ – Datový typ
• Pravé tlačítko nad sloupcem
• Lokální nastavení
• Výhoda pro
• Datum
• Načtený v národním formátu
D
Power Query – Sloupce základ – Odebrat
o Odebrat sloupce
▪ Vybrané
▪ Ostatní
• Pravé tlačítko
• Menu
D
Power Query – Sloupce základ – Prakticky
Data upravte (přesunout, přejmenovat, typ)
D
Prostor pro poznámky
Power Query – Sloupce – pokročilejší operace
• Sloučit sloupce
• Rozdělit sloupce
• Formát
• Extrahovat
• Nahradit hodnoty
• Duplikovat
▪ Transformace
▪ Přidání sloupce
Power Query Editor – Sloupce - Duplikovat
• Ne všechny transformace fungují v režimu
• Přidání sloupce
• Tip: Duplikovat sloupec
• Menu Přidání sloupce – Duplikovat sloupec
CZ
EN
Power Query Editor – Sloupce – Sloučit
• Označit sloupce ke sloučení
E
Power Query Editor – Sloupce – Sloučit
• Sloučit sloupce
E
Power Query – Sloupce – Rozdělit
• Jak můžeme sloupce dělit:
• Oddělovač
• ;
• :
• _
• Počet znaků
• Podle pozic
E
PQE – Sloupce – Rozdělit - Transformace
• Rozdělit sloupce
E
PQE – Sloupce – Rozdělit – Přidání sloupce
• Jak na to?
• Chci původní ponechat a mít nové?
• (1) Duplikovat sloupec
• (2) Rozdělit a následně opět sloučit na Přidání sloupce 
E
Power Query – Sloupce – Rozdělit
• Oddělovačem
• Jak rozdělit?
• Každý výskyt?
• Pravý
• Levý
• Upřesnit
E
Power Query – Sloupce – Rozdělit
• Počtem znaků
• Jak rozdělit
• Jednou zprava
• Jednou zleva
• Opakovaně
• Upřesnit
E
Power Query – Sloupce – Rozdělit
• Pro vaše poznámky
E
Power Query – Sloupce – Formát
• Opět lze vybrat
• Přidat
• Transformovat
• Formát text – úprava znaků:
• Malá
• VELKÁ
• …
E
CZ
EN
Power Query – Sloupce – Extrahovat
• Opět lze vybrat
• Přidat
• Transformovat
• Extrahovat
• Délka
• Výpočty kontrola
• První / Poslední
• Zleva/Zprava
E
CZ
EN
Power Query – Sloupce – Nahradit hodnoty
• Nahradit hodnoty
• Nahradit chyby
• Tip Odstranit chyby (chybové řádky)
E
CZ
EN
Power Query – Sloupce – Nahradit hodnoty
• Nahradit hodnoty
E
Power Query – Sloupce – Nahradit hodnoty
• Nahradit chyby
E
Power Query – Sloupce – Čísla
• Statistika
• Standardní
• Vědecký
• Trigonometrie
• Zaokrouhlení
• Informace
CZ
EN
F
Power Query – Sloupce – Čísla - Statistika
• Statistika
• Součet
• Minimum
• Maximum
• Medián
• Průměr
• Směrodatná odchylka
• Počet hodnot
• Počet jedinečných hodnot
• Nutno mít označeno více sloupců
F
CZ
EN
Power Query – Sloupce – Čísla - Standardní
• Hodnota sloupce (sloupců)
• Součet
• Jeden – přičte se konstanta
• Více - sečtou se
• Součin
• Jeden – vynásobí se konstantou
• Více – „pronásobí“ se čísla
• Odečíst - …
• Vydělit
• Vydělit celočiselně
• Zbytek po dělení
• Procenta
• Procentní část
F
CZ
EN
Power Query – Sloupce – Čísla - Vědecký
• Vědecký
• Mocniny
• Odmocniny
• Exponenty
• Logaritmy
F
CZ
EN
Power Query – Sloupce – Čísla - Trigonometrie
• Úhel v radiánech jako v Excel
• Výsledek zaokrouhlit
F
CZ
EN
Power Query – Sloupce – Čísla - Zaokrouhlení
• Zaokrouhlení
• Viz další funkce v M-ku
F
CZ
Power Query – Sloupce – Čísla - Informace
• Informace
• Sudé
• Liché
• Znaménko
F
CZ
Power Query – Sloupce – Čísla - Prakticky
• Praktické otestování
• Jeden označený sloupec
• Více označených sloupců
F
Prostor pro poznámky F
Power Query – Sloupce – Datum a čas
• Co potřebujeme z datum a čas získat?
• Rok?
• Čtvrtletí?
• Začátek, konec měsíce
• Název dne
• …
• Viz Power BI – DAX – Time inteligence
Power Query – Sloupce – Datum G
Datum
• Měsíc konec?
• Přestupný rok
• ….
CZ
EN
Power Query – Sloupce – Čas G
Čas
….
Power Query – Sloupce – Trvání G
• Typ sloupce - Trvání
Power Query – Sloupce – Datum Čas - Praxe G
• Prakticky
• Datum
• Měsíc konec?
• Čtvrtletí
• Čas
• …
• Trvání
• …
Power Query – Indexový sloupec
• Oceníte při komplikovanějších úpravách
• Využití M-ko
Power Query – Sloupce – Datum Čas G
• POZNÁMKY
Power Query – Sloupce – Speciálnosti
• Seskupit pole - Group By
• Pro kontingenčku…
• Vyplnit
• Sloučené buňky
CZEN
Power Query – Sloupce – Seskupit
• Seskupit pole - Group By
H
Power Query – Sloupce – Vyplnit
• Vyplnit
• Doplní hodnoty do sloupce
• Co sloučený řádek?
• Nejprve transformace TRANSPONOVAT viz příště
H
Power Query – Sloupce – Vyplnit - Prakticky
• Vyplňte sloupec Stát
• ČR,
• SR
• Seskupte podle státu
H
Převést na seznam
• Využijete při
• Hlubší práci v Mku
• Tvorbě pomůcek pro průřezy (Slicers)
Prostor pro poznámky
Power Query – Sloupce – Podmíněný
• Z Excel funkce KDYŽ
• Lze mít více podmínek
• „Naklikavatelné“
• Tip
• M-ko a funkce If – then
• viz následující lekce
Power Query – Sloupce – Podmíněný J
• Mějme státy ve kterých prodáváte
• ČR
• SR
• Chceme nahradit
• ČR > Česko
• SR > Slovensko
• Nahradit hodnoty umíme…
• Je to vhodné?
Power Query – Sloupce – Podmíněný J
Power Query – Sloupce – Podmíněný - Praxe
• ČR na Česko a SR na Slovensko
• Problém
• Co DE?
• Co čr
▪ KDYŽ?
▪ Podmíněný sloupec
J
Power Query – Sloupce – Podmíněný - Praxe
• ČR na Česko a SR na Slovensko budeme mít:
• Problém
• Co DE?
• Co čr
J
Co provede čR?
• Jak obejít?
• TIP: Slučování dotazů (Merge)
• SVYHLEDAT (VLOOKUP) v Excel
Power Query – Sloupce – Podmíněný - Praxe
• ČR na Česko a SR na Slovensko
• Problém
• Co DE?
• Co čr
J
Power Query – Sloupce – Podmíněný - Praxe
• Vyzkoušejte prakticky
J
Poznámky
Power Query – Sloupce – Z příkladu
PBI Napíše funkci za vás
•Ze všech sloupců
•Z výběru
• Pomoc Power BI
Power Query – Sloupce – Z příkladu
• Co chceme?
K
Power Query – Sloupce – Z příkladu
ABCD_Jméno
K
Power Query – Sloupce – Z příkladu - Praxe
Vyzkoušejte
• Jméno a Příjmení
• …
• Jak vypadá funkce?
• Využijeme dále
K
Power Query – Sloupce – Vlastní
• Co nejde vypočíst „nakliknutím“
• Zrychlení
• Lze využít funkce
• Využijí funkci Jazyka M v EN
• https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference
• Využijí funkci Jazyka M v CZ
• https://learn.microsoft.com/cs-cz/powerquery-m/power-query-m-function-reference
• Tip: Další krok využít přímo M-jazyk
Power Query – Sloupce – Vlastní
• Výpočet
• Konstanta
• Číslo
• Text
• Výpočet
• Součet?
• …
• Funkce
• …
Velká malá písmena!
L
Power Query – Sloupce – Vlastní
• Seskupit pole
L
Pro vaše poznámky
Poznámka navíc – kdyby náhodou ;)
• Vytvořit url adresu
• www.google.com
• Tip Nastavit v Power BI
• Tip: Excel HYPERLIN, HYPERTEXTOVÝ.ODKAZ ,
nebo Power Pivot a DAX