V tomto kurzu můžete pomocí Editoru dotazů v Power Query importovat data z místního excelového souboru, který obsahuje informace o produktu, a z datového kanálu OData, který obsahuje informace o objednávce produktu. Proveďte kroky transformace a agregace a zkombinováním dat z obou zdrojů vytvořte sestavu Celkové prodeje za produkt a rok .
K dokončení tohoto kurzu potřebujete sešit Produkty . V dialogovém okně Uložit jako soubor pojmenujte Výrobky a objednávky.xlsx.
Úkol 1: Import produktů do excelového sešitu
V tomto úkolu importujete produkty ze souboru Výrobky a Orders.xlsx (stažený a přejmenovaný v předchozí části) do excelového sešitu. Potom zvyšte úroveň řádků na záhlaví sloupců, některé sloupce odeberte a načtěte dotaz do listu.
Krok 1: Připojení k excelovému sešitu
- Vytvořte excelový sešit.
- Vyberte možnost Získat>data>ze souboru>ze sešitu.
- V dialogovém okně Importovat data vyhledejte a najděte Products.xlsx soubor, který jste stáhli, a pak vyberte Otevřít.
- V navigačním podokně poklikejte na tabulku Produkty . Zobrazí se Editor Power Query.
Krok 2: Prozkoumání kroků dotazu
Ve výchozím nastavení Power Query pro vaše pohodlí automaticky přidává několik kroků. Další informace získáte v části Použitý postup v podokně Nastavení dotazu .
- Klikněte pravým tlačítkem na krok Zdroj a vyberte Upravit nastavení. Tento krok byl vytvořen při importu sešitu.
- Klikněte pravým tlačítkem myši na navigační krok a vyberte Upravit nastavení. Tento krok se vytvořil, když jste vybrali tabulku v navigačním dialogovém okně.
- Klikněte pravým tlačítkem myši na krok Změněný typ a vyberte možnost Upravit nastavení. Tento krok vytvořil nástroj Power Query, který odvodil datové typy jednotlivých sloupců. Vyberte šipku dolů napravo od řádku vzorců a podívejte se na celý vzorec.
Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jenom potřebné sloupce
V tomto kroku odeberete všechny sloupce kromě IDProduktu, NazevProduktu, IDKategorie a MnožstvíNaJednotku.
- V zobrazení Náhled dat vyberte sloupce IDProduktu, NazevProduktu, IDKategorie a MnožstvíNaJednotku (použijte kombinaci kláves Ctrl+kliknutí nebo Shift+kliknutí).
- Vyberte Odebrat sloupce> aodebrat ostatní sloupce.
Krok 4: Načtení dotazu na produkty
V tomto kroku načtete dotaz Produkty do excelového listu.
- Vyberte Domů>Zavřít & Načíst. Dotaz se zobrazí v novém listu aplikace Excel.
Shrnutí: Kroky Power Query vytvořené v úkolu 1
Při provádění aktivit dotazu v Power Query vytvoří kroky dotazu a zobrazí jejich seznam v podokně Nastavení dotazu v seznamu Použitý postup. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v dokumentaci k Power Query.
| Úkol | Krok dotazu | Vzorec |
|---|---|---|
| Import excelového sešitu | Zdroj | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null; true) |
| Vyberte tabulku Výrobky | Navigace: | = Source{[Item="Products",Kind="Table"]}[Data] |
| Power Query automaticky rozpozná datové typy sloupců | Změněný typ | = Table.TransformColumnTypes( Products_Table,{{"IDProduktu", Int64.Type}, {"ProductName", zadejte text}, {"IDDODAVATELE", Int64.Type}, {"IDKategorie", Int64.Type}, {"MnožstvíNaJednotku", zadejte text}, {"JednotkováCena", typ číslo}, {"JednotkyNaSkladě", Int64.Type}, {"JednotkyPři objednávce", Int64.Type}, {"Úroveň řazení", Int64.Type}, {"Ukončeno", logický typ}}) |
| Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce | Odebrané další sloupce | = Table.SelectColumns(FirstRowAsHeader,{"IDProduktu", "NázevProduktu", "IDKategorie", "MnožstvíNaJednotku"}) |
Úkol 2: Import dat objednávek z datového kanálu OData
V tomto úkolu importujete do excelového sešitu data z ukázkového datového kanálu Northwind OData na http://services.odata.org/Northwind/Northwind.svcadrese , rozbalíte tabulku Order_Details, odeberete sloupce, vypočítáte součet za řádek, transformujete DatumObjednávky, seskupíte řádky podle IDProduktu a Roku, přejmenujete dotaz a zakážete stahování dotazů do excelového sešitu.
Krok 1: Připojení k datovému kanálu OData
- Vyberte možnost Data>Načíst data>z jiných zdrojů>z datového kanálu OData.
- V dialogu Datový kanál OData zadejte Adresu URL datového kanálu Northwind OData.
- Vyberte OK.
- V navigačním podokně poklikejte na tabulku Objednávky .
Krok 2: Rozbalení tabulky Order_Details
V tomto kroku rozšíříte tabulku Podrobnosti_objednávky související s tabulkou Objednávky a zkombinujete sloupce IDProduktu, JednotkováCena a Množství z tabulky Podrobnosti_objednávky do tabulky Objednávky. Operace rozšíření kombinuje sloupce ze související tabulky do tabulky předmětu. Po spuštění dotazu se řádky ze související tabulky (Order_Details) zkombinují do řádků s primární tabulkou (objednávky).
Sloupec obsahující související tabulku v Power Query obsahuje v buňce hodnotu Záznam nebo Tabulka. Označují se jako strukturované sloupce. Záznam označuje jeden související záznam a představuje relaci 1:1 s aktuálními daty nebo primární tabulkou. Tabulka označuje související tabulku a představuje relaci 1:N s aktuální nebo primární tabulkou. Strukturovaný sloupec představuje relaci ve zdroji dat, který používá relační model. Strukturovaný sloupec například označuje entitu s přidružením cizího klíče v datovém kanálu OData nebo relací cizího klíče v databázi systému SQL Server.
Po rozšíření tabulky Order_Details se do tabulky Objednávky přidají tři nové sloupce a další řádky, pokaždé jeden za každý řádek ve vnořené nebo související tabulce.
V náhledu dat se vodorovně posuňte na sloupec Order_Details .
Ve sloupci Order_Details vyberte ikonu rozšíření (
).V rozevíracím seznamu Rozšířit:
Výběrem možnosti (Vybrat všechny sloupce) vymažete všechny sloupce.
Vyberte IDProduktu, JednotkováCena a Mnozstvi.
Vyberte OK.
Poznámka
V Power Query můžete rozšířit tabulky propojené ze sloupce a agregovat sloupce propojené tabulky před rozšířením dat v tabulce předmětu. Další informace o provádění agregačních operací najdete v tématu Agregace dat ze sloupce (Power Query).
Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jenom potřebné sloupce
V tomto kroku odeberete všechny sloupce kromě DatumObjednávky, IDProduktu, JednotkováCena a Množství .
V náhledu dat vyberte následující sloupce:
- Vyberte první sloupec, IDobjednávky.
- Shift+kliknutí na poslední sloupec, Dopravce.
- Podržte klávesu Ctrl a klikněte na sloupce Datum_objednávky, Podrobnosti_objednávky.IDProduktu, Podrobnosti_objednávky.JednotkováCena a Podrobnosti_objednávky.Množství.
Klikněte pravým tlačítkem myši na záhlaví vybraného sloupce a vyberte Odebrat ostatní sloupce.
Krok 4: Výpočet součtu řádku pro každý řádek Order_Details
V tomto kroku vytvoříte vlastní sloupec k výpočtu součtu řádku pro každý řádek tabulky Podrobnosti_objednávky.
- V náhledu dat vyberte ikonu tabulky (
) v levém horním rohu náhledu. - Vyberte Přidat vlastní sloupec.
- V dialogovém okně Vlastní sloupec zadejte do pole Vzorec vlastního sloupce[Order_Details.JednotkováCena] * [Order_Details.Množství].
- Do pole Název nového sloupce zadejte Součet za řádek.
- Vyberte OK.
Krok 5: Transformace sloupce roku DatumObjednávky
V tomto kroku transformujete sloupec DatumObjednávky tak, aby zobrazoval rok data objednávky.
V náhledu dat klikněte pravým tlačítkem myši na sloupec DatumObjednávky a vyberte Transformovat>rok.
Přejmenujte sloupec DatumObjednávky na Rok:
- Poklikejte na sloupec DatumObjednávky a zadejte Rok nebo
- Klikněte pravým tlačítkem myši na sloupec DatumObjednávky , vyberte Přejmenovat a zadejte Rok.
Krok 6: Seskupení řádků podle IDProduktu a Roku
V náhledu dat vyberte Rok a Order_Details.IDProduktu.
Klikněte pravým tlačítkem myši na jedno ze záhlaví a vyberte Seskupit podle.
V dialogu Seskupit podle:
- Do textového pole Nový název sloupce zadejte Celkové prodeje.
- V rozevíracím seznamu Operace vyberte Součet.
- V rozevíracím seznamu Sloupec vyberte Součet za řádek.
Vyberte OK.
Před importem dat prodeje do Excelu přejmenujte dotaz:
- V podokně Nastavení dotazu zadejte do pole Názevcelkové prodeje.
Výsledky: Závěrečný dotaz pro úkol 2
Po provedení všech kroků budete mít dotaz Celkové prodeje pro datový kanál Northwind OData.
Shrnutí: Kroky Power Query vytvořené v úkolu 2
Při provádění aktivit dotazu v Power Query vytvoří kroky dotazu a zobrazí jejich seznam v podokně Nastavení dotazu v seznamu Použitý postup. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v dokumentaci k Power Query.
| Úkol | Krok dotazu | Vzorec |
|---|---|---|
| Připojení k datovému kanálu OData | Zdroj | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc"; null; [Implementation="2.0"]) |
| Výběr tabulky | Navigace | = Source{[Name="Orders"]}[Data] |
| Rozšíření tabulky Podrobnosti_objednávky | Rozšíření tabulky Podrobnosti_objednávky | = Table.ExpandTableColumn(Orders, "Order_Details", {"IDProduktu", "JednotkováCena", "Množství"}, {"Order_Details.IDProduktu", "Order_Details.JednotkováCena", "Order_Details.Množství"}) |
| Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce | RemovedColumns | = Table.RemoveColumns(#"Rozšíření Order_Details",{"IDObjednávky", "IDZákazníka", "IDZaměstnance", "DodatDne", "DatumOdeslání", "Dopravce", "Dopravné", "NázevZásilky", "AdresaDodání", "MěstoDodání", "OblastDodání", "PSČDodání", "ZeměDodání", "Zákazník", "Zaměstnanec", "Přepravce"}) |
| Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky | Přidání vlastního |
= Table.AddColumn(RemovedColumns, "Vlastní", each [Order_Details.JednotkováCena] * [Order_Details.Množství]) = Table.AddColumn(#"Rozšířené Order_Details", "Součet za řádek", each [Order_Details.JednotkováCena] * [Order_Details.Množství]) |
| Změna na smysluplnější název Lne Celkem | Přejmenované sloupce | = Table.RenameColumns(InsertedCustom,{{"Vlastní", "Součet za řádek"}}) |
| Transformace sloupce DatumObjednávky na zobrazení roku | Extrahovaný rok | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Změnit na smysluplnější názvy, DatumObjednávky a Rok |
Přejmenované sloupce 1 |
Table.RenameColumns (TransformedColumn,{{"DatumObjednávky", "Rok"}}) |
| Seskupení řádků podle IDProduktu a Roku | GroupedRows | = Table.Group(RenamedColumns1, {"Rok", "Order_Details.IDProduktu"}, {{"Celkové prodeje", each List.Sum([Součet za řádek]), type number}}) |
Úkol 3: Zkombinování dotazů Produkty a Celkové prodeje
Power Query vám umožňuje zkombinovat několik dotazů jejich sloučením nebo připojením. Operaci sloučení můžete provést u jakéhokoli dotazu Power Query, který má formát tabulky, bez ohledu na zdroj dat. Další informace o kombinování zdrojů dat najdete v tématu Kombinování víc dotazů (Power Query).
V tomto úkolu zkombinujete dotazy Produkty a Celkové prodeje pomocí sloučeného dotazu a operace rozšíření a potom načtete dotaz Celkové prodeje za produkt do datového modelu Excelu.
Krok 1: Sloučení IDProduktu a dotazu Celkové prodeje
V excelovém sešitu přejděte na dotaz Produkty na kartě listu Produkty .
Vyberte buňku v dotazu a pak vyberte Sloučení dotazu>.
V dialogu Sloučit vyberte Produkty jako primární tabulku a vyberte Celkové prodeje jako sekundární nebo související dotaz ke sloučení. Celkové prodeje se změní na nový strukturovaný sloupec s ikonou rozbalení.
Aby Celkové prodeje odpovídaly Produktům podle IDProduktu, vyberte sloupec IDProduktu z tabulky Produkty a sloupec Podrobnosti_objednávky.IDProduktu z tabulky Celkové prodeje.
V dialogu Úrovně ochrany osobních údajů:
- Pro oba zdroje dat vyberte jako úroveň izolace osobních údajů hodnotu Organizační.
- Vyberte Uložit.
Vyberte OK.
Poznámka
Úrovně ochrany osobních údajů brání uživateli neúmyslně kombinovat data z víc zdrojů dat, které by mohly být soukromé nebo firemní. V závislosti na dotazu by uživatel mohl neúmyslně odesílat data ze soukromého zdroje dat jinému zdroji dat, který by mohl být škodlivý. Power Query analyzuje každý zdroj dat a klasifikuje ho do definované úrovně ochrany osobních údajů: Veřejné, organizační a soukromé. Další informace o úrovních ochrany osobních údajů najdete v tématu Nastavení úrovní ochrany osobních údajů (Power Query).
Výsledek
Operace sloučení vytvoří dotaz. Výsledek dotazu obsahuje všechny sloupce z primární tabulky (Produkty) a strukturovaný sloupec jedné tabulky se související tabulkou (Celkové prodeje). Výběrem ikony Rozbalit přidáte do primární tabulky nové sloupce ze sekundární nebo související tabulky.
Krok 2: Rozbalení sloučeného sloupce
V tomto kroku rozšíříte sloučený sloupec s názvem NovýSloupec a vytvoříte dva nové sloupce v dotazu Produkty : Rok a Celkové prodeje.
V náhledu dat vyberte ikonu rozšíření (
) vedle NovéhoSloupce.V rozevíracím seznamu Rozbalit :
- Výběrem možnosti (Vybrat všechny sloupce) vymažete všechny sloupce.
- Vyberte Rok a Celkové prodeje.
- Vyberte OK.
Přejmenujte tyto dva sloupce na Rok a Celkové prodeje.
Pokud chcete zjistit, které produkty ve kterém roce dosáhly nejvyšších objemů prodeje, vyberte Seřadit sestupně podle celkového prodeje.
Přejmenujte dotaz na Celkové prodeje za produkt.
Výsledek
Krok 3: Načtení dotazu Celkové prodeje za produkt do datového modelu Excelu
V tomto kroku načtete dotaz do datového modelu Excelu, takže můžete vytvořit sestavu propojenou s výsledkem dotazu. Po načtení dat do datového modelu Excelu můžete použít Power Pivot k další analýze dat.
- Vyberte Domů>Zavřít & Načíst.
- V dialogovém okně Importovat data se ujistěte, že jste vybrali Přidat tato data do datového modelu. Další informace o používání tohoto dialogového okna získáte výběrem otazníku (?).
Výsledek
Máte dotaz Celkové prodeje za produkt , který kombinuje data ze souboru Products.xlsx a datového kanálu Northwind OData. Tento dotaz se použije na model Power Pivotu. Změny dotazu navíc změní a aktualizují výslednou tabulku v datovém modelu.
Shrnutí: Kroky Power Query vytvořené v úkolu 3
Při provádění aktivit sloučení dotazů v Power Query se kroky dotazu vytvářejí a zobrazují v podokně Nastavení dotazu v seznamu Použitý postup. Každý krok dotazu má odpovídající vzorec Power Query známý taky jako jazyk „M“. Další informace o vzorcích Power Query najdete v dokumentaci k Power Query.
| Úkol | Krok dotazu | Vzorec |
|---|---|---|
| Sloučení IDProduktu a dotazu Celkové prodeje | Zdroj (zdroj dat pro operaci sloučení) | = Table.NestedJoin(Products, {"IDProduktu"}, #"Celkové prodeje", {"Order_Details.IDProduktu"}, "Celkové prodeje", JoinKind.LeftOuter) |
| Rozšíření sloučeného sloupce | Rozšířený celkový prodej | = Table.ExpandTableColumn(Source, "Celkové prodeje", {"Rok", "Celkové prodeje"}, {"Celkové prodeje.Rok", "Celkové prodeje.Celkové prodeje"}) |
| Přejmenování dvou sloupců | Přejmenované sloupce | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Seřadit celkový prodej vzestupně | Seřazené řádky | = Table.Sort(#"Přejmenované sloupce",{{"Celkové prodeje", Order.Ascending}}) |