Informace o kombinování více zdrojů dat (Power Query)

V tomto úkolu naimportujete produkty ze souboru Produkty a Orders.xlsx (stažený a přejmenovaný výše) do excelového sešitu, zvýšíte úroveň řádků na záhlaví sloupců, odeberete některé sloupce a načtete dotaz do listu.

Krok 1: Připojení k excelovému sešitu

1. Vytvořte excelový sešit.

2. Vyberte Data > Získat data > ze souboru > ze sešitu.

3. V dialogovém okně Importovat data vyhledejte a vyhledejte soubor Products.xlsx, který jste stáhli, a pak vyberte Otevřít.

4. V podokně Navigátor poklikejte na tabulku Products (Produkty ). Zobrazí se Editor Power Query napájení.

Krok 2: Prozkoumání kroků dotazu

Ve výchozím nastavení Power Query automaticky přidá několik kroků, které jsou pro vás pohodlné. Další informace najdete v jednotlivých krocích v části Použitý postup v podokně Nastavení dotazů .

1. Klikněte pravým tlačítkem na krok Zdroj a vyberte Upravit nastavení. Tento krok byl vytvořen při importu sešitu.

2. Klikněte pravým tlačítkem na krok Navigace a vyberte Upravit nastavení. Tento krok byl vytvořen, když jste v dialogovém okně Navigace vybrali tabulku.

3. Klikněte pravým tlačítkem na krok Změněný typ a vyberte Upravit nastavení. Tento krok vytvořil Power Query, který odvodil datové typy jednotlivých sloupců. Výběrem šipky dolů napravo od řádku vzorců zobrazíte úplný vzorec.

Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jen potřebné sloupce

V tomto kroku odeberete všechny sloupce kromě IDProduktu, NázevProduktu, IDKategorie a MnožstvíNaJednotku.

1. V náhledu dat vyberte sloupce ProductID (IDproduktu), ProductName (Názevproduktu), CategoryID (Id kategorie) a QuantityPerUnit (použijte ctrl+kliknutí nebo shift+kliknutí).

2. Vyberte Odebrat sloupce > Odebrat další sloupce.

   

Krok 4: Načtení dotazu na produkty

V tomto kroku načtete dotaz Produkty do excelového listu.

• Vyberte Domů > Zavřít & načtení. Dotaz se zobrazí v novém excelovém listu.

Shrnutí: Power Query kroky vytvořené v úloze 1

Při provádění aktivit dotazů v Power Query se kroky dotazu vytvoří a zobrazí v podokně Nastavení dotazů 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 Power Query vzorcích najdete v tématu Vytvoření Power Query vzorců v Excelu.

Úkol	Krok dotazu	Vzorec
Import excelového sešitu	Zdroj	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Vyberte tabulku Products (Produkty).	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}, {"NázevProduktu", zadejte text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce	Odebrané ostatní sloupce	= Table.SelectColumns(FirstRowAsHeader,{"ProductID"; "ProductName", "CategoryID", "QuantityPerUnit"})

V tomto úkolu importujete data do excelového sešitu z ukázkového datového kanálu Northwind OData v http://services.odata.org/Northwind/Northwind.svc,rozbalíte tabulku Order_Details, odeberete sloupce, vypočítáte součet řádků, 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

1. Vyberte Data > Získat data > z jiných zdrojů > z datového kanálu OData.

2. V dialogu Datový kanál OData zadejte Adresu URL datového kanálu Northwind OData.

3. Vyberte OK.

4. V podokně Navigátor poklikejte na tabulku Objednávky .

Krok 2: Rozšíření tabulky Podrobnosti_objednávky

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).

V Power Query má sloupec obsahující související tabulku v buňce hodnotu Record (Záznam) nebo Table (Tabulka). Tyto sloupce se nazývají strukturované sloupce. Záznam označuje jeden související záznam a představujerelaci 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ý má relační model. Například strukturovaný sloupec označuje entitu s přidružením cizího klíče v datovém kanálu OData nebo relaci cizího klíče v databázi SQL Server.

Po rozšíření tabulky Podrobnosti_objednávky 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.

1. V náhledu dat se vodorovně posuňte na sloupec Order_Details .

2. Ve sloupci Order_Details vyberte ikonu rozbalení ().

3. V rozevíracím seznamu Rozšířit:

   1. Pokud chcete vymazat všechny sloupce, vyberte (Vybrat všechny sloupce).

   2. Vyberte ProductID( ID produktu), UnitPrice (Cena za jednotku) a Quantity (Množství).

   3. Vyberte OK.

      

      Poznámka: V Power Query můžete rozbalit tabulky propojené ze sloupce a agregovat sloupce propojené tabulky před rozbalení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.

Krok 3: Odebrání dalších sloupců tak, aby se zobrazovaly jen potřebné sloupce

V tomto kroku odeberete všechny sloupce kromě DatumObjednávky, IDProduktu, JednotkováCena a Množství. 

1. V náhledu dat vyberte následující sloupce: 

   1. Vyberte první sloupec , OrderID (ID objednávky).

   2. Shift+Klikněte na poslední sloupec Odesílatel.

   3. 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í.

2. Klikněte pravým tlačítkem na záhlaví vybraného sloupce a vyberte Odebrat další sloupce.

Krok 4: Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky

V tomto kroku vytvoříte vlastní sloupec k výpočtu součtu řádku pro každý řádek tabulky Podrobnosti_objednávky.

1. V náhledu dat vyberte ikonu tabulky () v levém horním rohu náhledu.

2. Klikněte na Přidat vlastní sloupec.

3. V dialogovém okně Vlastní sloupec zadejte do pole Vzorec vlastního sloupce[Order_Details.UnitPrice] * [Order_Details.Quantity].

4. Do pole Nový název sloupce zadejte Součet řádků.

5. Vyberte OK.

Krok 5: Transformace sloupce roku DatumObjednávky

V tomto kroku transformujete sloupec DatumObjednávky tak, aby zobrazoval rok data objednávky.

1. V náhledu dat klikněte pravým tlačítkem na sloupec OrderDate (DatumObjednávky ) a vyberte Transformovat > Year (Transformovat > Year).

2. Přejmenujte sloupec DatumObjednávky na Rok:

   1. dvakrát klikněte na sloupec DatumObjednávky a zadejte Rok nebo

   2. Right-Click ve sloupci Datumobjednávky vyberte Přejmenovat a zadejte Rok.

Krok 6: Seskupení řádků podle IDProduktu a Roku

1. V náhledu dat vyberte Rok a Order_Details.ProductID.

2. Right-Click jedno ze záhlaví a vyberte Seskupovat podle.

3. V dialogu Seskupit podle:

   1. Do textového pole Nový název sloupce zadejte Celkové prodeje.

   2. V rozevíracím seznamu Operace vyberte Součet.

   3. V rozevíracím seznamu Sloupec vyberte Součet za řádek.

4. Vyberte OK.

   

Krok 7: Přejmenování dotazu

Před importem dat o prodeji do Excelu přejmenujte dotaz:

• V podokně Nastavení dotazu do pole Název zadejte Total Sales (Celkové prodeje).

Výsledky: Konečný dotaz pro úkol 2

Po provedení všech kroků budete mít dotaz Celkové prodeje pro datový kanál Northwind OData.

Shrnutí: Power Query kroky vytvořené v úloze 2 

Při provádění aktivit dotazů v Power Query se kroky dotazu vytvoří a zobrazí v podokně Nastavení dotazů 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 Power Query vzorcích najdete v tématu Informace o Power Query vzorcích.

Ú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"; "Cena za jednotku"; "Quantity"}; {"Order_Details.ProductID"; "Order_Details.UnitPrice"; "Order_Details.Quantity"})
Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Výpočet součtů pro každý řádek tabulky Podrobnosti_objednávky	Přidání vlastního	= Table.AddColumn(RemovedColumns, "Vlastní", každý [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details"; "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Změna na smysluplnější název, Lne Total	Přejmenované sloupce	= Table.RenameColumns(InsertedCustom;{{"Custom"; "Line Total"}})
Transformace sloupce DatumObjednávky na zobrazení roku	Extrahovaný rok	= Table.TransformColumns(#"Seskupené řádky",{{"Year", Date.Year, Int64.Type}})
Změnit na  smysluplnější názvy, OrderDate a Year	Přejmenované sloupce 1	Table.RenameColumns (TransformedColumn,{{"DatumObjednávky", "Rok"}})
Seskupení řádků podle IDProduktu a Roku	GroupedRows	= Table.Group(RenamedColumns1; {"Year"; "Order_Details.ProductID"}; {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query vám umožňuje zkombinovat několik dotazů prostřednictvím jejich sloučení nebo připojení. Operace sloučení se provádí u jakéhokoli dotazu Power Query, který má formát tabulky, bez ohledu na zdroj dat, ze kterého data pochází. Další informace o kombinování zdrojů dat najdete v tématu Kombinování víc dotazů.

V tomto úkolu zkombinujete dotazy Products (Produkty) a Total Sales (Celkové prodeje ) pomocí operace Merge (Sloučení) a Expand (Rozbalit ) a pak načtete dotaz Total Sales per Product (Celkové prodeje za produkt) do datového modelu Excelu.

Krok 1: Sloučení IDProduktu a dotazu Celkové prodeje

1. V excelovém sešitu přejděte na dotaz Produkty na kartě Listu Produkty .

2. Vyberte buňku v dotazu a pak vyberte Dotaz > Sloučit.

3. V dialogovém okně Sloučit vyberte produkty jako primární tabulku a jako sekundární nebo související dotaz, který chcete sloučit, vyberte Total Sales (Celkové prodeje ). Total Sales se stane novým strukturovaným sloupcem s ikonou rozbalení.

4. 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.

5. V dialogu Úrovně ochrany osobních údajů:

   1. Pro oba zdroje dat vyberte jako úroveň izolace osobních údajů hodnotu Organizační.

   2. Vyberte Uložit.

6. Vyberte OK.

   Poznámka zabezpečení:  Ú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ů.

   

Výsledek

Operace sloučení vytvoří dotaz. Výsledek dotazu obsahuje všechny sloupce z primární tabulky (Produkty) a jeden strukturovaný sloupec tabulky do související tabulky (Celkové prodeje). Výběrem ikony Rozbalit přidejte nové sloupce do primární tabulky ze sekundární nebo související tabulky.

Krok 2: Rozbalení sloučeného sloupce

V tomto kroku rozbalíte sloučený sloupec s názvem NewColumn a v dotazu Products (Produkty) vytvoříte dva nové sloupce: Year (Rok) a Total Sales (Celkové prodeje).

1. V náhledu dat vyberte Rozbalit ikonu () vedle Položky NovýSloupce.

2. V rozevíracím seznamu Rozbalit :

   1. Pokud chcete vymazat všechny sloupce, vyberte (Vybrat všechny sloupce).

   2. Vyberte Year (Rok) a Total Sales (Celkové prodeje).

   3. Vyberte OK.

3. Přejmenujte tyto dva sloupce na Rok a Celkové prodeje.

4. Pokud chcete zjistit, které produkty a ve kterých letech dosáhly nejvyššího objemu prodeje, vyberte Seřadit sestupně podle celkového prodeje.

5. 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, abyste vytvořili sestavu připojenou k výsledku dotazu. Po načtení dat do datového modelu Excelu můžete k další analýze dat použít Power Pivot.

1. Vyberte Domů > Zavřít & načtení.

2. V dialogovém okně Importovat data vyberte Přidat tato data do datového modelu. Pokud chcete získat další informace o používání tohoto dialogového okna, vyberte otazník (?).

Výsledek

Máte dotaz Total Sales 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. Kromě toho změny dotazu upraví a aktualizují výslednou tabulku v datovém modelu.

Shrnutí: Power Query kroky vytvořené v úloze 3

Při provádění aktivit sloučení dotazů v Power Query se kroky dotazu vytvoří a zobrazí 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 Power Query vzorcích najdete v tématu Informace o Power Query vzorcích.

Úkol	Krok dotazu	Vzorec
Sloučení IDProduktu a dotazu Celkové prodeje	Zdroj (zdroj dat pro operaci sloučení)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales"; {"Order_Details.ProductID"}; "Total Sales", JoinKind.LeftOuter)
Rozšíření sloučeného sloupce	Rozbalený celkový prodej	= Table.ExpandTableColumn(Source, "Total Sales"; {"Year"; "Total Sales"}; {"Total Sales.Year"; "Total Sales.Total Sales"})
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é prodeje ve vzestupném pořadí	Seřazené řádky	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})