Dowiedz się, jak połączyć wiele źródeł danych (Power Query)

W tym zadaniu importujesz produkty z pliku Produkty i Orders.xlsx (pobrane i zmienione nazwy powyżej) do skoroszytu programu Excel, podwyższasz poziom wierszy do nagłówków kolumn, usuwasz niektóre kolumny i ładujesz zapytanie do arkusza.

Krok 1. Nawiązywanie połączenia ze skoroszytem programu Excel

1. Utwórz skoroszyt programu Excel.

2. Wybierz pozycję > Pobierz > danychz > plikuze skoroszytu.

3. W oknie dialogowym Importowanie danych wyszukaj i znajdź pobrany plik Products.xlsx, a następnie wybierz pozycję Otwórz.

4. W okienku Nawigator kliknij dwukrotnie tabelę Produkty . Zostanie wyświetlony Edytor Power Queryzasilania.

Krok 2. Sprawdzenie kroków zapytania

Domyślnie Power Query automatycznie dodaje kilka kroków jako wygodę. Sprawdź każdy krok w obszarze Zastosowane kroki w okienku Ustawienia zapytania , aby dowiedzieć się więcej.

1. Kliknij prawym przyciskiem myszy krok Źródło , a następnie wybierz pozycję Edytuj ustawienia. Ten krok został utworzony podczas importowania skoroszytu.

2. Kliknij prawym przyciskiem myszy krok nawigacji, a następnie wybierz pozycję Edytuj ustawienia. Ten krok został utworzony po wybraniu tabeli w oknie dialogowym Nawigacja .

3. Kliknij prawym przyciskiem myszy krok Zmieniony typ i wybierz pozycję Edytuj ustawienia. Ten krok został utworzony przez Power Query, który wywnioskował typy danych w każdej kolumnie. Wybierz strzałkę w dół po prawej stronie paska formuły, aby wyświetlić pełną formułę.

Krok 3. Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn

W tym kroku usuniesz wszystkie kolumny z wyjątkiem kolumn ProductID, ProductName, CategoryID i QuantityPerUnit.

1. W obszarze Podgląd danych wybierz kolumny ProductID, ProductName, CategoryID i QuantityPerUnit (użyj klawiszy Ctrl+Click lub Shift+Click).

2. Wybierz pozycję Usuń kolumny > Usuń inne kolumny.

   

Krok 4. Załadowanie zapytania dotyczącego produktów

W tym kroku załadujesz zapytanie Produkty do arkusza programu Excel.

• Wybierz pozycję > Strona główna Zamknij & Załaduj. Zapytanie zostanie wyświetlone w nowym arkuszu programu Excel.

Podsumowanie: Power Query kroki utworzone w zadaniu 1

Podczas wykonywania działań związanych z kwerendami w Power Query kroki zapytania są tworzone i wyświetlane w okienku Ustawienia zapytania na liście Zastosowane kroki. Każdemu krokowi zapytania odpowiada formuła dodatku Power Query, nazywana również językiem „M”. Aby uzyskać więcej informacji na temat Power Query formuł, zobacz Tworzenie formuł Power Query w programie Excel.

Zadanie	Krok zapytania	Formuła
Importowanie skoroszytu programu Excel	Źródło	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Zaznacz tabelę Produkty	Nawigacja	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query automatycznie wykrywa typy danych kolumn	Zmieniono typ	= Table.TransformColumnTypes(Products_Table;{{"ProductID", Int64.Type}, {"ProductName", wpisz tekst}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn	Usunięto inne kolumny	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

W tym zadaniu zaimportujesz dane do skoroszytu programu Excel z przykładowego źródła strumieniowego OData Northwind w witrynie http://services.odata.org/Northwind/Northwind.svc,rozwiniesz Order_Details tabelę, usuniesz kolumny, obliczysz sumę wierszy, przekształcisz kolumnę DataZamówienia, zgrupujesz wiersze według identyfikatora produktu i roku, zmienisz nazwę zapytania i wyłączysz pobieranie zapytania do skoroszytu programu Excel.

Krok 1. Nawiązywanie połączenia ze strumieniowym kanałem informacyjnym OData

1. Wybierz pozycję >Pobierz> danych z innych źródeł > ze źródła strumieniowego OData.

2. W oknie dialogowym Źródło strumieniowe OData wprowadź wartość Adres URL dla źródła strumieniowego OData Northwind.

3. Wybierz przycisk OK.

4. W okienku Nawigator kliknij dwukrotnie tabelę Zamówienia .

Krok 2. Rozwijanie tabeli Order_Details

W tym kroku rozwiniesz tabelę Order_Details (powiązaną z tabelą Orders), aby kolumny ProductID, UnitPrice i Quantity z tabeli Order_Details zostały połączone z tabelą Orders. Operacja Rozwiń łączy kolumny powiązanej tabeli z tabelą docelową. Po uruchomieniu zapytania wiersze z powiązanej tabeli (Order_Details) są łączone w wiersze z podstawową tabelą (Zamówienia).

W Power Query kolumna zawierająca powiązaną tabelę zawiera wartość Record (Rekord) lub Table (Tabela) w komórce. Są one nazywane kolumnami strukturalnymi. Rekord wskazuje jeden rekord pokrewny i reprezentuje relacjęjeden-do-jednego z bieżącymi danymi lub podstawową tabelą. Tabela wskazuje powiązaną tabelę i reprezentuje relację jeden-do-wielu z bieżącą lub podstawową tabelą. Kolumna strukturalna reprezentuje relację w źródle danych, które ma model relacyjny. Na przykład kolumna strukturalna wskazuje jednostkę ze skojarzeniem klucza obcego w źeniu strumieniowym OData lub relacją klucza obcego w bazie danych SQL Server.

Gdy rozwiniesz tabelę Order_Details, do tabeli Orders zostaną dodane trzy nowe kolumny i dodatkowe wiersze — po jednym dla każdego wiersza w zagnieżdżonej lub powiązanej tabeli.

1. W widoku Podgląd danych przewiń w poziomie do kolumny Order_Details .

2. W kolumnie Order_Details wybierz ikonę rozwijania ().

3. Na liście rozwijanej Rozwiń:

   1. Wybierz pozycję (Zaznacz wszystkie kolumny), aby wyczyścić wszystkie kolumny.

   2. Wybierz pozycje ProductID, UnitPrice i Quantity.

   3. Wybierz przycisk OK.

      

      Uwaga: W Power Query można rozwijać tabele połączone z kolumną i agregować kolumny tabeli połączonej przed rozwinięciem danych w tabeli tematu. Aby uzyskać więcej informacji o wykonywaniu operacji agregowania, zobacz Agregowanie danych z kolumny.

Krok 3. Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn

W tym kroku usuniesz wszystkie kolumny z wyjątkiem kolumn OrderDate, ProductID, UnitPrice i Quantity. 

1. W obszarze Podgląddanych zaznacz następujące kolumny: 

   1. Zaznacz pierwszą kolumnę — IdentyfikatorZamówień.

   2. Shift+Kliknij ostatnią kolumnę, Spedytor.

   3. Przytrzymaj naciśnięty klawisz Ctrl i kliknij kolumny OrderDate, Order_Details.ProductID, Order_Details.UnitPrice i Order_Details.Quantity.

2. Kliknij prawym przyciskiem myszy nagłówek zaznaczonej kolumny i wybierz polecenie Usuń inne kolumny.

Krok 4. Obliczanie sumy wiersza dla każdego wiersza tabeli Order_Details

W tym kroku utworzysz element Kolumna niestandardowa do obliczania sumy wiersza dla każdego wiersza tabeli Order_Details.

1. W obszarze Podgląd danych wybierz ikonę tabeli () w lewym górnym rogu podglądu.

2. Kliknij pozycję Dodaj kolumnę niestandardową.

3. W oknie dialogowym Kolumna niestandardowa w polu Formuła kolumny niestandardowej wprowadź ciąg [Order_Details.CenaJednkowa] * [Order_Details.Ilość].

4. W polu Nazwa nowej kolumny wprowadź ciąg Suma wierszy.

5. Wybierz przycisk OK.

Krok 5. Przekształcanie kolumny OrderDate zawierającej rok

W tym kroku przekształcisz kolumnę OrderDate w celu określenia roku daty zamówienia.

1. W obszarze Podgląd danych kliknij prawym przyciskiem myszy kolumnę DataZamówienia , a następnie wybierz pozycję Przekształć > Roku.

2. Zmień nazwę kolumny OrderDate na Year:

   1. kliknij dwukrotnie kolumnę OrderDate i wprowadź nazwę Year lub

   2. Right-Click w kolumnie DataZamówienia wybierz pozycję Zmień nazwę i wprowadź ciąg Rok.

Krok 6. Grupowanie wierszy według kolumn ProductID i Year

1. W obszarze Podgląd danych wybierz pozycję Rok i Order_Details.IDProduktów.

2. Right-Click jeden z nagłówków i wybierz pozycję Grupuj według.

3. W oknie dialogowym Grupowanie według:

   1. W polu tekstowym Nazwa nowej kolumny wprowadź nazwę Total Sales.

   2. Na liście rozwijanej Operacja wybierz pozycję Suma.

   3. Na liście rozwijanej Kolumna wybierz pozycję Line Total.

4. Wybierz przycisk OK.

   

Krok 7. Zmienianie nazwy zapytania

Przed zaimportowanie danych sprzedaży do programu Excel zmień nazwę zapytania:

• W okienku Ustawienia zapytania w polu Nazwa wprowadź wartość Total Sales.

Wyniki: ostateczne zapytanie dla zadania 2

Po wykonaniu wszystkich kroków zostanie utworzone zapytanie Total Sales, dotyczące źródła strumieniowego OData Northwind.

Podsumowanie: Power Query kroki utworzone w zadaniu 2 

Podczas wykonywania działań związanych z kwerendami w Power Query kroki zapytania są tworzone i wyświetlane w okienku Ustawienia zapytania na liście Zastosowane kroki. Każdemu krokowi zapytania odpowiada formuła dodatku Power Query, nazywana również językiem „M”. Aby uzyskać więcej informacji na temat Power Query formuł, zobacz Informacje o Power Query formułach.

Zadanie	Krok zapytania	Formuła
Nawiązywanie połączenia ze źródłem strumieniowym OData	Source	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Zaznaczanie tabeli	Nawigacja	= Źródło{[Name="Zamówienia"]}[Dane]
Rozwijanie tabeli Order_Details	Expand Order_Details	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Obliczanie sumy wiersza dla każdego wiersza tabeli Order_Details	Dodano niestandardowe	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Zmień na bardziej opisową nazwę, Lne Total	Kolumny o zmienionej nazwie	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Przekształcanie kolumny OrderDate w celu określenia roku	Wyodrębniony rok	= Table.TransformColumns(#"Pogrupowane wiersze",{{"Year", Date.Year, Int64.Type}})
Zmień na  bardziej opisowe nazwy, DataZamówienia i Rok	Kolumny o zmienionej nazwie 1	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Grupowanie wierszy według kolumn ProductID i Year	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Dodatek Power Query umożliwia łączenie wielu zapytań przez ich scalanie lub dołączanie. Operacja Scal jest wykonywana w przypadku wszelkich zapytań dodatku Power Query mających kształt tabeli, niezależnie od źródła, z którego pochodzą dane. Aby uzyskać więcej informacji dotyczących łączenia źródeł danych, zobacz Łączenie wielu zapytań.

W tym zadaniu połączysz zapytania Products i Total Sales za pomocą zapytania scalania i operacji Rozwiń , a następnie załadujesz zapytanie Total Sales per Product do modelu danych programu Excel.

Krok 1. Scalanie kolumny ProductID z zapytaniem Total Sales

1. W skoroszycie programu Excel przejdź do zapytania Produkty na karcie Arkusz produktów .

2. Zaznacz komórkę w zapytaniu, a następnie wybierz pozycję Zapytanie > Scal.

3. W oknie dialogowym Scalanie wybierz pozycję Produkty jako podstawową tabelę, a następnie wybierz pozycję Łączna sprzedaż jako zapytanie pomocnicze lub pokrewne do scalenia. Total Sales stanie się nową kolumną strukturalną z ikoną rozwijania.

4. Aby dopasować zapytanie Total Sales do zapytania Products za pośrednictwem kolumny ProductID, zaznacz kolumnę ProductID z tabeli Products i kolumnę Order_Details.ProductID z tabeli Total Sales.

5. W oknie dialogowym Poziomy prywatności:

   1. Wybierz pozycję Organizacyjne jako poziom ochrony prywatności dla obu źródeł danych.

   2. Wybierz pozycję Zapisz.

6. Wybierz przycisk OK.

   Uwaga dotycząca zabezpieczeń:  Poziomy prywatności uniemożliwiają użytkownikom przypadkowe łączenie danych z różnych źródeł danych, które mogą być własnością prywatną lub należeć do organizacji. Zależnie od zapytania użytkownik może przypadkowo wysłać dane z prywatnego źródła danych do innego źródła danych, które może być złośliwe. Dodatek Power Query analizuje każde źródło danych i klasyfikuje je przy użyciu jednego ze zdefiniowanych poziomów prywatności: Publiczne, Organizacyjne i Prywatne. Aby uzyskać więcej informacji o poziomach prywatności, zobacz Ustawianie poziomów prywatności.

   

Wynik

Operacja Scal umożliwia utworzenie zapytania. Wynik zapytania zawiera wszystkie kolumny z podstawowej tabeli (Produkty) i pojedynczą kolumnę strukturalną Tabeli do powiązanej tabeli (Total Sales). Wybierz ikonę Rozwiń , aby dodać nowe kolumny do podstawowej tabeli z tabeli pomocniczej lub powiązanej.

Krok 2. Rozwijanie scalonej kolumny

W tym kroku rozwiniesz scaloną kolumnę o nazwę NewColumn , aby utworzyć dwie nowe kolumny w zapytaniu Produkty : Year i Total Sales.

1. W obszarze Podgląd danych wybierz pozycję Rozwiń ikonę () obok pozycji NewColumn.

2. Na liście rozwijanej Rozwiń :

   1. Wybierz pozycję (Zaznacz wszystkie kolumny), aby wyczyścić wszystkie kolumny.

   2. Wybierz pozycje Rok i Łączna sprzedaż.

   3. Wybierz przycisk OK.

3. Zmień nazwy tych dwóch kolumn na Year i Total Sales.

4. Aby dowiedzieć się, które produkty i w których latach produkty uzyskały najwyższy poziom sprzedaży, wybierz pozycję Sortuj malejąco według całkowitej sprzedaży.

5. Użyj polecenia Zmień nazwę dla zapytania, określając dla niego nazwę Total Sales per Product.

Wynik

Krok 3. Ładowanie zapytania Total Sales per Product do modelu danych programu Excel

W tym kroku załadujesz zapytanie do modelu danych programu Excel, aby utworzyć raport połączony z wynikiem zapytania. Po załadowaniu danych do modelu danych programu Excel możesz użyć dodatku Power Pivot do dalszego analizy danych.

1. Wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

2. Upewnij się, że w oknie dialogowym Importowanie danych wybrano pozycję Dodaj te dane do modelu danych. Aby uzyskać więcej informacji na temat korzystania z tego okna dialogowego, wybierz znak zapytania (?).

Wynik

Masz zapytanie Total Sales per Product , które łączy dane z pliku Products.xlsx i źródła strumieniowego OData Northwind. To zapytanie jest stosowane do modelu dodatku Power Pivot. Ponadto zmiany w zapytaniu modyfikują i odświeżą wynikowe tabele w modelu danych.

Podsumowanie: Power Query kroki utworzone w zadaniu 3

Podczas wykonywania działań scalania zapytań w Power Query kroki zapytania są tworzone i wyświetlane w okienku Ustawienia zapytania na liście Zastosowane kroki. Każdemu krokowi zapytania odpowiada formuła dodatku Power Query, nazywana również językiem „M”. Aby uzyskać więcej informacji na temat Power Query formuł, zobacz Informacje o Power Query formułach.

Zadanie	Krok zapytania	Formuła
Scalanie kolumny ProductID z zapytaniem Total Sales	Source (źródło danych dla operacji Scal)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Rozwijanie scalonej kolumny	Rozszerzona łączna sprzedaż	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Zmienianie nazwy dwóch kolumn	Kolumny o zmienionej nazwie	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Sortowanie sumy Sprzedaż w kolejności rosnącej	Posortowane wiersze	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})