Łączenie danych z wielu źródeł danych (Power Query)

Uwaga:  Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Uwaga: Dodatek Power Query jest nazywany pobrać i przekształcić w programie Excel 2016. Podane tu informacje dotyczą obu. Aby dowiedzieć się więcej, zobacz Pobieranie i Przekształć w programie Excel 2016.

W tym samouczku za pomocą edytora zapytań dodatku Power Query importowanie danych z lokalnego pliku programu Excel, który zawiera informacje o produkcie, z OData kanał, który zawiera informacje zamówienia produktu. Wykonaj kroki przekształcenie i agregacji i łączenie danych z obu źródeł, aby wygenerować raport Total Sales per Product i rok.

Do skorzystania z tego samouczka jest potrzebny skoroszyt Products and Orders. W oknie dialogowym Zapisywanie jako nazwij plik Products and Orders.xlsx.

W tym samouczku

Zadanie 1. Importowanie produktów do skoroszytu programu Excel

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

Krok 2. Przekształcanie pierwszego wiersza w nagłówki kolumn tabeli

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

Utworzone kroki dodatku Power Query

Krok 4. Importowanie zapytania dotyczącego produktów

Zadanie 2. Importowanie danych zamówień ze źródła strumieniowego OData

Krok 1. Nawiązywanie połączenia ze źródłem strumieniowym OData

Krok 2. Rozwijanie tabeli Order_Details

Rozwijanie linku do tabeli Order_Details

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

Usuwanie wybranych kolumn

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

Obliczanie sumy wiersza dla każdego wiersza tabeli Order_Details

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

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

Krok 7. Zmienianie nazwy zapytania

Ostateczne wyniki zapytania

Utworzone kroki dodatku Power Query

Krok 8. Wyłączanie pobierania zapytania do skoroszytu programu Excel

Wyłączanie pobierania zapytania

Zadanie 3. Łączenie zapytań Products i Total Sales

Krok 1. Scalanie kolumny ProductID z zapytaniem Total Sales

Krok 2: Rozwijanie Scalonej kolumny

Rozwijanie linku do tabeli NewColumn

Utworzone kroki dodatku Power Query

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

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

Ostateczne zapytanie Total Sales per Product

Zadanie 1. Importowanie produktów do skoroszytu programu Excel

W tym zadaniu zaimportujesz produkty z pliku Products and Orders.xlsx do skoroszytu programu Excel.

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

  1. Utwórz skoroszyt programu Excel.

  2. Na karcie wstążki POWER QUERY kliknij pozycję Z pliku > Z programu Excel.

  3. W oknie dialogowym przeglądania w programie Excel odszukaj lub wpisz ścieżkę pliku Products and Orders.xlsx, aby zaimportować plik lub utworzyć link do niego.

  4. W okienku Nawigator kliknij dwukrotnie arkusz Products lub kliknij pozycję Products, a następnie kliknij pozycję Edytuj zapytanie. Kiedy edytujesz zapytanie lub łączysz się z nowym źródłem danych, pojawia się okno Edytora zapytań.

    Uwaga: Na końcu tego artykułu znajduje się bardzo krótki klip wideo na temat wyświetlania Edytora zapytań.

Krok 2. Przekształcanie pierwszego wiersza w nagłówki kolumn tabeli

W siatce Podgląd zapytania pierwszy wiersz tabeli nie zawiera nazw kolumn tabeli. Aby przekształcić pierwszy wiersz w nagłówki kolumn tabeli:

  1. Kliknij ikonę tabeli ( Ikona tabeli ) w lewym górnym rogu podglądu danych.

  2. Kliknij pozycję Użyj pierwszego wiersza jako nagłówków.

Przekształcanie pierwszego wiersza w nagłówki kolumn tabeli

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 siatce Podgląd zapytania zaznacz kolumny ProductID, ProductName, CategoryID i QuantityPerUnit (przytrzymując naciśnięty klawisz Ctrl lub Shift i klikając te kolumny).

  2. Na wstążce Edytor zapytań kliknij pozycję Usuń kolumny > Usuń inne kolumny lub kliknij prawym przyciskiem myszy nagłówek kolumny i kliknij polecenie Usuń inne kolumny.

    Ukrywanie innych kolumn

Utworzone kroki dodatku Power Query

Podczas wykonywania działań dotyczących zapytań w dodatku 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 o języku formuł dodatku Power Query, zobacz Informacje o formułach dodatku Power Query.

Zadanie

Krok zapytania

Formuła

Nawiązywanie połączenia ze skoroszytem programu Excel

Source

Source{[Name="Products"]}[Data]

Przekształcanie pierwszego wiersza w nagłówki kolumn tabeli

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Krok 4. Importowanie zapytania dotyczącego produktów

W tym kroku zaimportujesz zapytanie Products do skoroszytu programu Excel.

  1. Na wstążce Edytor zapytań kliknij pozycję Zastosuj i zamknij. Wyniki zostaną wyświetlone w nowym arkuszu programu Excel.

Początek strony

Zadanie 2. Importowanie danych zamówień ze źródła strumieniowego OData

W tym zadaniu zaimportujesz dane do skoroszytu programu Excel z przykładowego źródła strumieniowego Northwind OData na stronie http://services.odata.org/Northwind/Northwind.svc.

Krok 1. Nawiązywanie połączenia ze źródłem strumieniowym OData

  1. Na karcie wstążki POWER QUERY kliknij pozycję 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. Kliknij przycisk OK.

  4. W okienku Nawigator kliknij dwukrotnie tabelę Orders lub kliknij pozycję Orders, a następnie kliknij pozycję Edytuj.

Uwaga:  Po aktywowaniu tabeli wskaźnikiem myszy zostanie wyświetlony wysuwany podgląd tabeli.

Aktywowanie źródła danych

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) zostaną połączone z wierszami tabeli docelowej (Orders).

W dodatku Power Query kolumna zawierająca link do powiązanej tabeli ma link Wpis lub link Tabela. Link Wpis prowadzi do jednego powiązanego rekordu i reprezentuje relację jeden-do-jednego z tabelą docelową. Link Tabela prowadzi do powiązanej tabeli i reprezentuje relację jeden-do-wielu z tabelą docelową. Link reprezentuje właściwości nawigacji w źródle danych w ramach modelu relacyjnego. W przypadku źródła strumieniowego OData właściwości nawigacji reprezentują encję ze skojarzeniem klucza obcego. W przypadku bazy danych, takiej jak SQL Server, właściwości nawigacji reprezentują relacje klucza obcego w tej bazie danych.

Rozwijanie linku do tabeli Order_Details

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 okienku Podgląd zapytania przewiń do kolumny Order_Details.

  2. W kolumnie Order_Details kliknij ikonę rozwijania ( Rozwiń ).

  3. Na liście rozwijanej Rozwiń:

    1. Kliknij pozycję (Zaznacz wszystkie kolumny), aby usunąć zaznaczenie wszystkich kolumn.

    2. Kliknij pozycje ProductID, UnitPrice i Quantity.

    3. Kliknij przycisk OK.

      Rozwijanie linku do tabeli Order_Details

      Uwaga:  W dodatku Power Query można rozwijać tabele powiązane z kolumną, a także wykonywać operacje agregowania na kolumnach połączonej tabeli przed rozwinięciem danych w tabeli docelowej. 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. W poprzednim zadaniu zostało użyte polecenie Usuń inne kolumny. W tym zadaniu usuniesz wybrane kolumny.

Usuwanie wybranych kolumn

  1. W okienku Podgląd zapytania zaznacz wszystkie kolumny:

    1. Kliknij pierwszą kolumnę (OrderID).

    2. Przytrzymaj naciśnięty klawisz Shift i kliknij ostatnią kolumnę (Shipper).

    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 jednej z zaznaczonych kolumn, a następnie kliknij pozycję Usuń 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.

Obliczanie sumy wiersza dla każdego wiersza tabeli Order_Details

  1. W okienku Podgląd zapytania kliknij ikonę tabeli ( Ikona tabeli ) w lewym górnym rogu podglądu.

  2. Kliknij pozycję Wstaw kolumnę > Niestandardowa.

  3. W oknie dialogowym Wstawianie kolumny niestandardowej w polu tekstowym Formuła kolumny niestandardowej wprowadź ciąg [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. W polu tekstowym Nazwa nowej kolumny wprowadź nazwę Line Total.

  5. Kliknij przycisk OK.

Obliczanie sumy wiersza dla każdego wiersza tabeli Order_Details

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 siatce Podgląd kliknij prawym przyciskiem myszy kolumnę OrderDate, a następnie kliknij pozycję Przekształć > Year.

  2. Zmień nazwę kolumny OrderDate na Year:

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

    2. kliknij prawym przyciskiem myszy kolumnę OrderDate, kliknij pozycję Zmień nazwę i wprowadź nazwę Year.

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

  1. W siatce Podgląd zapytania zaznacz kolumny Year i Order_Details.ProductID.

  2. Kliknij prawym przyciskiem myszy jeden z nagłówków i kliknij 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. Kliknij przycisk OK.

    Okno dialogowe Grupowanie według umożliwiające obsługę operacji agregowania

Krok 7. Zmienianie nazwy zapytania

Przed zaimportowaniem danych sprzedaży do programu Excel nadaj zapytaniu nazwę Total Sales:

  1. W okienku Ustawienia zapytania w polu tekstowym Nazwa wprowadź tekst Total Sales.

Ostateczne wyniki zapytania

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

Sprzedaż razem

Utworzone kroki dodatku Power Query

Podczas wykonywania działań dotyczących zapytań w dodatku 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 o języku formuł dodatku Power Query, zobacz Informacje o formułach dodatku Power Query.

Zadanie

Krok zapytania

Formuła

Nawiązywanie połączenia ze źródłem strumieniowym OData

Source

Source{[Name="Orders"]}[Data]

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

InsertedColumns

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Przekształcanie kolumny OrderDate w celu określenia roku

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Grupowanie wierszy według kolumn ProductID i Year

GroupedRows

Table.Group
(RenamedColumns1 {"Year", "Order_Details.ProductID"}, {{wynik "Total Sales", każdego List.Sum ([Line Total]), wpisz liczbę}})

Krok 8. Wyłączanie pobierania zapytania do skoroszytu programu Excel

Ponieważ zapytanie Total Sales nie reprezentuje ostatecznego raportu Total Sales per Product and Year, należy wyłączyć pobieranie zapytania do skoroszytu programu Excel. Gdy opcja Załaduj do arkusza jest ustawiona na wartość Wyłączony w okienku Ustawienia zapytania, wyniki z danymi tego zapytania nie są pobierane, ale zapytanie można mimo to łączyć z innymi zapytaniami w celu uzyskania żądanych wyników. W następnym zadaniu dowiesz się, jak połączyć to zapytanie z zapytaniem Products.

Wyłączanie pobierania zapytania

  1. W okienku Ustawienia zapytania wyczyść pole wyboru Załaduj do arkusza.

  2. Na wstążce Edytora zapytań kliknij pozycję Zastosuj i zamknij. W okienku Zapytania skoroszytu zapytanie Total Sales wyświetla komunikat Ładowanie jest wyłączone.

    Wyłączanie pobierania zapytania

Początek strony

Zadanie 3. Łączenie zapytań Products i Total Sales

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 przy użyciu kroków zapytania Scal i Rozwiń.

Krok 1. Scalanie kolumny ProductID z zapytaniem Total Sales

  1. W skoroszycie programu Excel przejdź do zapytania Products w arkuszu Sheet2.

  2. Na karcie wstążki ZAPYTANIE kliknij pozycję Scal.

  3. W oknie dialogowym Scalanie wybierz zapytanie Products jako podstawową tabelę i wybierz zapytanie Total Sales jako drugie (powiązane) zapytanie do scalenia. Zapytanie Total Sales stanie się nową kolumną z możliwością 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. Kliknij przycisk Zapisz.

  6. Kliknij 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 Poziomy prywatności.

    Okno dialogowe Scalanie

Po kliknięciu przycisku OK operacja Scal utworzy zapytanie. Wyniki zapytania będą zawierać wszystkie kolumny z podstawowej tabeli (Products) i jedną kolumnę z linkiem nawigacji do powiązanej tabeli (Total Sales). Operacja Rozwiń dodaje nowe kolumny z powiązanej tabeli do podstawowej (docelowej) tabeli.

Ostateczny wynik scalania

Krok 2. Rozwijanie scalonej kolumny

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

Rozwijanie linku do tabeli NewColumn

  1. W siatce Podgląd zapytania kliknij NewColumn Rozwiń ikonę ( Rozwiń ).

  2. Na liście rozwijanej Rozwiń:

    1. Kliknij pozycję (Zaznacz wszystkie kolumny), aby usunąć zaznaczenie wszystkich kolumn.

    2. Kliknij pozycjeYear i Total Sales.

    3. Kliknij przycisk OK.

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

  4. Użyj polecenia Sortuj malejąco, określając kolejność według kolumny Total Sales, aby dowiedzieć się, dla jakich produktów i w których latach sprzedaż była największa.

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

Rozwijanie linku do tabeli

Utworzone kroki dodatku Power Query

Podczas wykonywania działań scalania dotyczących zapytań w dodatku 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 o języku formuł dodatku Power Query, zobacz Informacje o formułach dodatku Power Query.

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"},"NewColumn")

Rozwijanie scalonej kolumny

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

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

W tym kroku wyłączysz opcję Załaduj do arkusza i załadujesz zapytanie do elementu Model danych programu Excel, aby utworzyć raport połączony z wynikami zapytania. Oprócz ładowania wyników zapytania do arkusza programu Excel dodatek Power Query umożliwia ładowanie wyników zapytania do elementu Model danych programu Excel. Po załadowaniu danych do elementu Model danych programu Excel można używać dodatku Power Pivot i programu Power View w celu dalszej analizy danych.

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

  1. W okienku Ustawienia zapytania wyczyść pole wyboru Załaduj do arkusza i zaznacz pole wyboru Załaduj do modelu danych.

  2. Aby załadować zapytanie do elementu Model danych programu Excel, kliknij pozycję Zastosuj i zamknij.

Ładowanie modelu danych programu Excel

Ostateczne zapytanie Total Sales per Product

Po wykonaniu wszystkich kroków zostanie utworzone zapytanie Total Sales per Product, łączące dane z pliku Products and Orders.xlsx oraz ze źródła strumieniowego OData Northwind. To zapytanie można zastosować do modelu dodatku Power Pivot. Ponadto modyfikacje zapytania w dodatku Power Query będą powodować zmodyfikowanie i odświeżenie wynikowej tabeli w modelu dodatku Power Pivot.

Początek strony

Uwaga: Edytor zapytań jest wyświetlana tylko podczas ładowania, edytowania lub tworzenia nowego zapytania przy użyciu Dodatku Power Query. Poniższym klipie wideo przedstawiono okno Edytora zapytań wyświetlane po zakończeniu edytowania zapytania ze skoroszytu programu Excel. Aby wyświetlić Edytora zapytań bez ładowania lub edytowania istniejącego zapytania skoroszytu, na podstawie sekcji Pobieranie danych zewnętrznych na karcie wstążki Power Query wybierz z innych źródeł > puste zapytanie. Następujące klip wideo przedstawia sposób wyświetlania Edytora zapytań.

Jak wyświetlić Edytora zapytań w programie Excel

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×