Cvičenie 01 – používanie Power Query v Exceli

  • 30 min

Teraz máte príležitosť vyskúšať Power Query pomocou tohto interaktívneho a podrobného použitia. Použite uvedené vzorové súbory zdroja údajov na dokončenie cvičení.

Cvičenie 01 – Analýza v Exceli: Používanie Power Query v Exceli

Odhadovaný čas na absolvovanie tohto cvičenia je 30 minút.

V tomto praktickom učebnom cvičení vykonáte tieto úlohy:

  1. Pripojenie k zdrojového súboru .csv – Customers pomocou Power Query
  2. Pomocou transformácií Power Query spojte stĺpec podľa oddeľovača – Customers
  3. Použitie Power Query na pripojenie k excelového zdrojového súboru údajov – Cenové ponuky
  4. Použitie transformácií Power Query na zrušenie kontingenčnosti – úvodzovky
  5. Použitie transformácií Power Query na vyčistenie – Úvodzovky

Cvičné predpoklady

Na úspešné dokončenie cvičení musia byť splnené nasledujúce predpoklady a nastavenie:

  • Musíte byť pripojení na internet.

  • Musíte mať nainštalovaný balík Microsoft Office.

  • Zaregistrujte sa do služby Microsoft Power BI.

  • Minimálne by ste mali mať počítač s dvoma jadrami a 4 GB pamäte RAM, na ktorých je spustená jedna z nasledujúcich verzií systému Windows: Windows 8/Windows Server 2008 R2 alebo novšiu verziu.

  • Ak sa rozhodnete používať Internet Explorer, bude potrebná verzia 10 alebo novšia. Môžete tiež použiť prehliadač Microsoft Edge alebo Google Chrome.

  • Overte, či máte 32-bitový alebo 64-bitový operačný systém a rozhodnite sa, či potrebujete nainštalovať 32-bitovú alebo 64-bitovú aplikáciu.

    Poznámka

    Najlepší je 64-bitový excelový formát a Power BI Desktop.

    Dôležité

    Stiahnutie študentského obsahu: V jednotke C: v lokálnom počítači vytvorte priečinok s názvom ANALYST-LABS . Stiahnite si a rozbaľte všetok obsah z https://aka.ms/modern-analytics-labs priečinka ANALYST-LABS, ktorý ste vytvorili (C:\ANALYST-LABS).

  • Stiahnite a nainštalujte Power BI Desktop pomocou niektorej z nasledujúcich možností:

    • Ak máte Windows 10, stiahnite si a nainštalujte aplikáciu Power BI Desktop pomocou služby Microsoft App Store.
    • Stiahnite si a nainštalujte aplikáciu Microsoft Power BI Desktop.

  • Ak už máte Power BI Desktop nainštalovanú, uistite sa, že máte stiahnu najnovšiu verziu služby Power BI.

Štruktúra dokumentu

Zdrojové údaje alebo počiatočné súbory každého cvičenia sa nachádzajú v každom priečinku cvičení.

  • Cvičenie 01 sa dokončí pomocou Power Query v excelovej aplikácii.

  • Cvičenia 02A a Lab 02B sa dokončia pomocou aplikácie Power BI Desktop.

  • Cvičenie 03A sa vykonáva pomocou aplikácií Power BI Desktop, služba Power BI a Excelu.

  • Cvičenie 03B sa vykonáva pomocou Excelu a aplikácií služba Power BI.

Každé cvičenie obsahuje podrobné pokyny, podľa ktorých sa môžete riadiť a ktoré obsahujú obrázky obrazovky v rámci inštrukcií. Kľúčové akcie každého kroku sú identifikované tučným textom. Venujte pozornosť poznámkam, tipom a ďalším dôležitým informáciám. Každé cvičenie obsahuje dokončený súbor riešenia, ktorý možno použiť ako odkaz.

Prehľad

Odhadovaný čas na absolvovanie tohto cvičenia je 30 minút. V tomto cvičení vykonáte tieto úlohy:

  1. Použitie Power Query na pripojenie k zdrojového súboru CSV – Customers

  2. Pomocou Power Query transformácií môžete rozdeliť stĺpec podľa oddeľovača – Zákazníci

  3. Použite Power Query na pripojenie k zdrojového súboru XLSX – Cenové ponuky

  4. Použitie transformácií Power Query na zrušenie kontingenčnosti – úvodzovky

  5. Transformácie použite Power Query na vyčistenie – úvodzovky

    Poznámka

    Toto cvičenie bolo vytvorené na základe predajných aktivít fiktívnej Wi-Fi spoločnosti s názvom SureWi, ktorú poskytla adaptívna služba P3. Údaje sú vlastnosťou adaptívnej kategórie P3 a zdieľali sa s cieľom predvedenie funkcií Excelu a služby Power BI so vzorovými údajmi z odvetvia. Pri každom použití týchto údajov sa toto pripísanie musí týkať adaptívnej kategórie P3.

Cvičenie č. 1: Pripojenie k súboru CSV – Customers.csv pomocou Power Query

V tomto cvičení sa pripojíte pomocou Excelu k zdrojového súboru CSV.

Úloha 1: Spustenie Excelu

V tejto úlohe spustíte nový prázdny hárok, aby ste mohli začať.

  1. Spustite Excel.

    Logo Excelu.

  2. Vytvorte nový prázdny zošit.

    Snímka obrazovky s obrazovkou na výber nového prázdneho zošita v Exceli.

Úloha 2: Pripojenie k súboru CSV pomocou Power Query

V tejto úlohe sa pripojíte k zdrojového súboru údajov Customers CSV.

  1. Vyberte kartu Údaje na hlavnom páse s nástrojmi Excelu.

  2. Vyberte položky Získať údaje > zo súboru > Z textu/CSV.

    Snímka obrazovky s možnosťou ponuky Údaje > z textu/CSV.

  3. Prejdite na súbor C:\ANALYST-LABS\Lab 01\MAIAD Lab 01 – Zdroj údajov Customers.csv súboru.

    Oblasť Ukážka zobrazí ukážku údajov zákazníka, názvy stĺpcov a hodnoty.

    Poznámka

    V tomto príklade je len ukážka údajov.

    Snímka obrazovky okna Navigátor zobrazujúca ukážku údajov z laboratória Lab 01 – Zdroj údajov – Customers.csv so zvýrazneným tlačidlom Transformovať údaje.

  4. Vyberte tlačidlo Transformovať údaje . Tým sa spustí okno Editor Power Query.

    Poznámka

    Keď pracujete v Power Query, najlepšie je maximalizovať okno Editor Power Query , aby ste mohli zobraziť úplné zobrazenie ponúk, tabiel a možností Power Query okien.

  5. Predvolene sa tabla Dotazy na ľavej strane okna Editor Power Query zbalí. Kliknutím na šípku na table Dotazy rozbalíte a otvoríte tablu Dotazy.

    Snímka obrazovky znázorňujúca okno Editor Power Query s tablou Dotazy zbalenú a šípkou zobrazenou na rozbalenie tably.

  6. Na table Dotazy kliknite pravým tlačidlom myši na predvolený názov dotazu s názvom MAIAD Lab 01 – Zdroj údajov – Zákazníci a potom dotaz premenujte na Customers (Zákazníci).

    Snímka obrazovky otvorenej tably Dotazy zobrazujúca zvýraznené tlačidlo Lab 01 – Data Source – Customers (Zdroj údajov – zákazníci) a tlačidlo Premenovať.

    Tip

    Dotazy, ktoré sa načítajú, aby sa použili ako súčasť dátového modelu, by mali mať jasný, popisný, jednoducho použiteľný názov podstatného mena, ktorý popisuje, čo údaje predstavujú. Sú to napríklad zákazníci, cenové ponuky, faktúry, produkty, geografia atď.

Cvičenie č. 2: Použitie transformácií Power Query na rozdelenie stĺpca podľa oddeľovača – Zákazníci

V tomto cvičení použijete Power Query na extrahovanie krstného mena zo stĺpca Kontakt.

Úloha 1: Použitie stĺpca z príkladu

V tejto úlohe vytvoríte nový stĺpec s názvom Krstné meno pomocou transformácie Pridať stĺpec > stĺpca z príkladov s cieľom rozdeliť kontakt podľa oddeľovača.

  1. V mriežke Ukážka vyberte stĺpec Kontakt .

  2. Potom vyberte z karty Pridať stĺpec , šípku nadol Stĺpec z príkladov a možnosť Z výberu .

    Snímka obrazovky s oknom Editor Power Query zobrazujúca možnosti ponuky Pridať stĺpec.

    Poznámka

    Otvorí sa okno nového používateľského rozhrania s názvom Pridať stĺpce z príkladov – toto okno vyzerá ako Power Query mriežky ukážky, ale je to samostatné okno, ktoré umožňuje zadať navrhovanú hodnotu, aby Power Query mohli identifikovať vzor a vzorec, aby sa dosiahli konečné výsledky.

  3. V okne Pridať stĺpec z príkladov zadajte do stĺpca s názvom [Stĺpec1] hodnotu "Bc" a potom zadajte .

    Snímka obrazovky okna Pridať stĺpec z príkladov zobrazujúca, že Hugo zadal hodnotu v stĺpci Column1.

    Poznámka

    Po stlačení klávesu Enter Power Query zistí, či v údajoch existuje vzor na vyplnenie hodnôt pre všetky riadky!

  4. Dvakrát kliknite na predvolenú hlavičku s názvom Text pred oddeľovačom a nový stĺpec premenujte na "Krstné meno". Vyberte tlačidlo OK .

    Snímka obrazovky okna Stĺpec z príkladov zobrazujúca premenovanie predvoleného názvu stĺpca na Krstné meno.

    Poznámka

    Teraz si v mriežke Editor Power Query ukážky všimnete stĺpec NEW s názvom [Krstné meno] – vytvorený analýzou [Krstné meno] z [Kontakt] pomocou transformácie Stĺpec z príkladu!

    Snímka obrazovky mriežky ukážky v Editor Power Query zobrazujúca nový stĺpec First Name (Meno).

Cvičenie č. 3: Ak sa chcete pripojiť k xlsx, Power Query Quotes.xlsx

V tomto cvičení sa budete pomocou Excelu pripájať k zdrojového súboru XLSX.

Úloha 1: Pripojenie k zdroju XLSX z okna Editor Power Query

V tejto úlohe začnete v okne Editor Power Query.

  1. V ponuke Power Query Domov vyberte možnosť Nový zdroj > excelového súboru.

    Snímka obrazovky okna Editor Power Query znázorňujúca možnosti karty Domov s vybratou možnosťou Nový zdroj > súbor > Excelom.

  2. Prejdite do súboru C:\ANALYST-LABS\Lab 01\Data Source – Quotes.xlsx.

  3. V okne Navigátor vyberte hárok s názvom "Lab 01 – Quotes" (Cvičenie 01 – Úvodzovky).

    Poznámka

    Toto je LEN ukážka údajov.

    Snímka obrazovky okna Navigátor zobrazujúca zvýraznený hárok Lab 01A – Quotes (Cenové ponuky).

  4. Vyberte tlačidlo OK, ktoré sa načíta ako druhý dotaz v okne Editor Power Query.

  5. Na table Dotazy kliknite pravým tlačidlom myši na predvolený názov dotazu s názvom Cvičenie 01 – Cenové ponuky, čím dotaz premenujete na "Cenové ponuky".

    Screesnshot tably Dotazy v okne Editor Power Query s vybratou položkou Lab 01 – názov úvodzoviek na premenovanie.

Cvičenie č. 4: Použitie Power Query na zrušenie kontingennosti – Úvodzovky

V tomto cvičení použijete Power Query transformácie na štruktúru údajov Úvodzovky pre Power Pivot.

Úloha 1: Použitie prvého riadku ako tlačidla transformácie hlavičky

V tejto úlohe presuniete prvý riadok s hodnotami hlavičky stĺpca do hlavičky tabuľky.

V ponuke Domov vyberte tlačidlo Použiť prvý riadok ako hlavičky .

Snímka obrazovky karty Domov zobrazujúca vybraté tlačidlo Použiť prvý riadok ako hlavičky.

Úloha 2: Použitie možnosti ponuky zrušenia kontingenčnej transformácie

V tejto úlohe zrušíte kontingenčnej hodnoty údajov Úvodzovky.

  1. Na table Ukážka zobrazte možnosti ponuky kliknutím pravým tlačidlom myši na stĺpec [CustID].

  2. Potom vyberte možnosť Zrušiť kontingencnosť ostatných stĺpcov .

    Snímka obrazovky stĺpca CustID a zvýraznená možnosť Zrušiť kontingenčnosť ostatných stĺpcov na výber.

  3. Dvojitým kliknutím na stĺpec s názvom [Atribút] premenujte stĺpec na [QuoteDate].

  4. Dvakrát kliknite na stĺpec s názvom [Hodnota], čím stĺpec premenujete na [QuoteAmt].

    Pred

    Snímka obrazovky znázorňujúca hlavičky stĺpcov CustID, Attribute a Values ešte predtým, ako budú premenované.

    Po

    Snímka obrazovky so stĺpcami Attribute (Atribút) a Value (Hodnota) premenovaných na QuoteDate (Dátum cenovej ponuky) a QuoteAmt (Dátum cenovej ponuky).

Cvičenie č. 5: Použitie Power Query na vyčistenie – úvodzovky

V tomto cvičení použijete Power Query transformácie na vyčistenie údajov úvodzoviek.

Úloha 1: Použitie funkcie Nahradiť transformáciu

V tejto úlohe použijete metódu nahradenia, ktorou zmeníte hodnotu [QuoteDate] na úplný dátum, ktorý možno skonvertovať na typ údajov Dátum.

  1. V okne Ukážka zobrazte možnosti ponuky kliknutím pravým tlačidlom myši na stĺpec [QuoteDate].

  2. Potom vyberte možnosť Nahradiť hodnoty... .

    Snímka obrazovky stĺpca DátumNákupu so zvýraznenou možnosťou ponuky Nahradiť hodnoty.

  3. V časti Nahradiť hodnoty... Okno používateľského rozhrania:

    1. Do textového poľa Hodnota, ktorá sa má vyhľadať , zadajte spojovník .

    2. Do textového poľa Nahradiť čím zadajte hodnotu "/1/".

    Snímka obrazovky okna Nahradiť hodnoty zobrazujúca položky v textových poliach Hodnota, ktorá sa má vyhľadať a Nahradiť čím.

    1. Vyberte tlačidlo OK.

Úloha 2: Použitie ikony Typ údajov

V tejto úlohe budete používať ikonu Typ údajov na zmenu typu údajov z typu text na dátum.

  1. Kliknite na ikonu ABC , ktorá označuje, že stĺpec je textovým typom údajov.

  2. Potom v ponuke s možnosťami typu údajov vyberte možnosť Typ údajov.

    Snímka obrazovky s vybratou ikonou formátu údajov QuoteDate (Dátum Cenovej ponuky) so zvýrazneným typom údajov Date (Dátum).

Úloha 3: Zatvorenie a načítanie dátového modelu

V tejto úlohe načítate tabuľky Customers a Quotes do dátového modelu.

  1. V ponuke Domov vyberte položku Zavrieť & Načítať > Zavrieť & Načítať do....

    Snímka obrazovky s vybratým tlačidlom Zavrieť & Načítať a zvýraznenou možnosťou Zavrieť & načítať do.

  2. V okne Import vyberte prepínač Vytvoriť iba pripojenie .

    Snímka obrazovky znázorňujúca okno Importovať údaje, ktoré zobrazuje tlačidlo Vytvoriť iba pripojenie a políčko Pridať tieto údaje do dátového modelu začiarknuté.

  3. Začiarknite políčko vedľa položky Pridať tieto údaje do dátového modelu.

  4. Vyberte tlačidlo OK .

    Poznámka

    Načítané tabuľky sa zobrazia v okne Dotazy & table Pripojenia s celkovým počtom načítaných riadkov.

    Snímka obrazovky tably Dotazy & Pripojenia zobrazujúca dotaz Customers s načítanými 7 560 riadkami a dotaz Quotes s načítanými 84 307 riadkami.

    Poznámka

    V danom bode sme sa pripojili k zdrojom údajov pomocou Power Query a vybrali sme začiarkavacie políčko na pridanie týchto údajov do dátového modelu. V skutočnosti sme však nevideli, kam sa tieto údaje načítali. V ďalšom cvičení Lab 02A použijeme Power BI Desktop na importovanie pripojení Power Query, tabuľky Zákazník a Cenová ponuka – na vytvorenie dátového modelu.

    Poznámka

    Ak chcete upraviť okno Dotazy a znova spustiť Editor Power Query okno, vyberte položku Údaje > Dotazy & Pripojenia a zobrazte tablu Dotazy & Pripojenia.

    Snímka obrazovky tlačidla Dotazy & Pripojenia na karte Údaje.

    Poznámka

    Po výbere položky Zavrieť a načítať do... a prvýkrát nastavte možnosti Importovať údaje, toto je predvolené nastavenie. Tieto nastavenia môžete kedykoľvek upraviť kliknutím pravým tlačidlom myši na dotaz na table Dotazy & pripojenia a následným výberom položky Načítať do... možnosť zobraziť a aktualizovať nastavenia Importovať údaje.

    Snímka obrazovky položky Načítať do... v kontextovom dialógovom okne pre položku na table Dotazy & Pripojenia.

Úloha 4: Uloženie súboru

V tejto úlohe uložíte excelový súbor s pripojením dotazu Customers (Zákazníci ) a Quotes (Cenové ponuky ).

  1. Na hlavnom páse s nástrojmi programu Excel vyberte položky Súbor > Uložiť.

  2. Prejdite do priečinka C:\ANALYST-LABS\Lab 01 a potom súbor uložte ako Lab 01 – Môj Solution.xlsx.

Súhrn

V tomto cvičení ste sa v Exceli Power Query použili na pripojenie k zdrojovým súborom CSV & XLSX, vytvorili ste nový stĺpec pomocou príkladu, zrušiť kontingenčnosť & použili transformácie v Power Query, načítali zdrojové údaje do dátového modelu a uložili excelový súbor s údajovými pripojeniami.

Snímka obrazovky konečných výsledkov v Exceli zobrazujúca dotazy Customers (Zákazníci) a Quotes (Cenové ponuky).