Tworzenie, ładowanie i edytowanie zapytania w programie Excel (Power Query)

Dowiedz się, w którym środowisku się znajdujesz Dodatek Power Query jest dobrze zintegrowany z interfejsem użytkownika programu Excel, zwłaszcza podczas importowania danych, pracy z połączeniami i edytowania tabel przestawnych, tabel programu Excel i nazwanych zakresów. Aby uniknąć nieporozumień, w dowolnym momencie warto wiedzieć, w którym środowisku się obecnie znajdujesz, programie Excel lub dodatku Power Query.

Znany arkusz programu Excel, wstążka i siatka	Wstążka Edytora dodatku Power Query i podgląd danych

Na przykład manipulowanie danymi w arkuszu programu Excel różni się zasadniczo od dodatku Power Query. Ponadto połączone dane widoczne w arkuszu programu Excel mogą, ale nie muszą, spowodować, że dodatek Power Query będzie działał w tle w celu kształtowania danych. Dzieje się tak tylko po załadowaniu danych do arkusza lub modelu danych z dodatku Power Query.

Zmienianie nazw kart arkusza    Warto zmieniać nazwy kart arkuszy w zrozumiały sposób, szczególnie w przypadku dużej ich liczby. Szczególnie ważne jest wyjaśnienie różnicy między arkuszem danych a arkuszem załadowanym z Edytora dodatku Power Query. Nawet jeśli masz tylko dwa arkusze, jeden z tabelą programu Excel o nazwie Arkusz1, a drugi zapytanie utworzone przez zaimportowanie tabeli programu Excel o nazwie Tabela1, łatwo się pomylić. Zawsze warto zmienić domyślne nazwy kart arkuszy na nazwy bardziej sensowne dla Ciebie. Na przykład zmień nazwę Arkusz1 na DataTable, a Tabela1 na QueryTable. Teraz jest jasne, która karta zawiera dane i która karta zawiera zapytanie.

Możesz utworzyć zapytanie na podstawie zaimportowanych danych lub utworzyć puste zapytanie.

Tworzenie zapytania na podstawie zaimportowanych danych

Jest to najczęstszy sposób tworzenia zapytania.

1. Zaimportuj dane. Aby uzyskać więcej informacji, zobacz Importowanie danych z zewnętrznych źródeł danych.

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

Tworzenie pustego zapytania

Warto zacząć od podstaw. Można to zrobić na dwa sposoby.

• Wybierz pozycję Dane > Pobierz > danychz innych źródeł > puste zapytanie.

• Wybierz pozycję Dane > Pobierz dane > Uruchom Edytor dodatku Power Query.

Na tym etapie możesz ręcznie dodać kroki i formuły, jeśli dobrze znasz język formuł M dodatku Power Query.

Możesz też wybrać pozycję Narzędzia główne , a następnie wybrać polecenie w grupie Nowe zapytanie . Wykonaj jedną z poniższych czynności:

• Wybierz pozycję Nowe źródło , aby dodać źródło danych. To polecenie przypomina polecenie Dane > Pobierz dane na wstążce programu Excel.

• Wybierz pozycję Ostatnie źródła , aby wybrać źródło danych, z którym pracujesz. To polecenie przypomina polecenie Dane > Ostatnio używane źródła na wstążce programu Excel.

• Wybierz pozycję Wprowadź dane , aby ręcznie wprowadzić dane. Możesz wybrać to polecenie, aby wypróbować Edytor dodatku Power Query niezależnie od zewnętrznego źródła danych.

Zakładając, że zapytanie jest prawidłowe i nie zawiera żadnych błędów, można je załadować z powrotem do arkusza lub modelu danych.

Ładowanie zapytania z Edytora dodatku Power Query

W Edytorze dodatku Power Query wykonaj jedną z następujących czynności:

• Aby załadować do arkusza, wybierz pozycję > główne Zamknij & Załaduj > Zamknij & Załaduj.

• Aby załadować do modelu danych, wybierz pozycję Strona główna > Zamknij & Załaduj > Zamknij & Załaduj do.
  
  W oknie dialogowym Importowanie danych wybierz pozycję Dodaj te dane do modelu danych.

Porada   Czasami polecenie Załaduj do jest wyszarzone lub wyłączone. Może się tak zdarzyć podczas tworzenia zapytania w skoroszycie po raz pierwszy. W takim przypadku wybierz pozycję Zamknij & Załaduj, w nowym arkuszu wybierz pozycję Zapytania > danych & kartę Połączenia > Zapytania , kliknij zapytanie prawym przyciskiem myszy, a następnie wybierz pozycję Załaduj do. Ewentualnie na wstążce Edytora dodatku Power Query wybierz pozycję Zapytanie > Załaduj do.

Ładowanie zapytania z okienka Zapytania i połączenia 

W programie Excel może być konieczne załadowanie zapytania do innego arkusza lub modelu danych.

1. W programie Excel wybierz pozycję Dane > Zapytania & Połączenia, a następnie wybierz kartę Zapytania .

2. Na liście zapytań znajdź zapytanie, kliknij je prawym przyciskiem myszy, a następnie wybierz pozycję Załaduj do. Zostanie wyświetlone okno dialogowe Importuj dane.

3. Zdecyduj, jak chcesz zaimportować dane, a następnie wybierz przycisk OK. Aby uzyskać więcej informacji na temat korzystania z tego okna dialogowego, wybierz znak zapytania (?).

Istnieje kilka sposobów edytowania zapytania załadowanego do arkusza.

Edytowanie zapytania na podstawie danych w arkuszu programu Excel

• Aby edytować zapytanie, znajdź zapytanie załadowane wcześniej z Poziomu Edytora dodatku Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj.

Edytowanie zapytania w okienku Zapytania & Połączenia

Może się okazać, że okienko Zapytania & Połączenia jest wygodniejsze w użyciu, gdy masz wiele zapytań w jednym skoroszycie i chcesz je szybko znaleźć.

1. W programie Excel wybierz pozycję Dane > Zapytania & Połączenia, a następnie wybierz kartę Zapytania .

2. Na liście zapytań znajdź zapytanie, kliknij je prawym przyciskiem myszy, a następnie wybierz pozycję Edytuj.

Edytowanie zapytania w oknie dialogowym Właściwości zapytania

• W programie Excel wybierz pozycję Dane > dane & połączenia > kartę Zapytania , kliknij prawym przyciskiem myszy zapytanie i wybierz pozycję Właściwości, wybierz kartę Definicja w oknie dialogowym Właściwości , a następnie wybierz pozycję Edytuj zapytanie.

Napiwek    Jeśli jesteś w arkuszu zawierającym zapytanie, wybierz pozycję Dane > Właściwości, wybierz kartę Definicja w oknie dialogowym Właściwości , a następnie wybierz pozycję Edytuj zapytanie. 

Model danych zazwyczaj zawiera kilka tabel rozmieszczonych w relacji. Zapytanie można załadować do modelu danych za pomocą polecenia Załaduj do w celu wyświetlenia okna dialogowego Importowanie danych , a następnie zaznaczając pole wyboru Dodaj te dane do trybu danychl. Aby uzyskać więcej informacji o modelach danych, zobacz Dowiedz się, które źródła danych są używane w modelu danych skoroszytu, Tworzenie modelu danych w programie Excel i Tworzenie tabeli przestawnej przy użyciu wielu tabel.

1. Aby otworzyć model danych, wybierz pozycję Power Pivot > Zarządzaj.

2. U dołu okna dodatku Power Pivot wybierz kartę arkusza odpowiedniej tabeli.
   
   Upewnij się, że jest wyświetlana właściwa tabela. Model danych może zawierać wiele tabel.

3. Zwróć uwagę na nazwę tabeli.

4. Aby zamknąć okno dodatku Power Pivot, wybierz pozycję Plik > Zamknij. Odzyskanie pamięci może potrwać kilka sekund.

5. Wybierz pozycję Dane > Połączenia & Właściwości > kartę Zapytania , kliknij zapytanie prawym przyciskiem myszy, a następnie wybierz pozycję Edytuj.

6. Po zakończeniu wprowadzania zmian w Edytorze dodatku Power Query wybierz pozycję Plik > Zamknij & Załaduj.

Wynik

Zapytanie w arkuszu i tabela w modelu danych zostaną zaktualizowane.

Jeśli zauważysz, że ładowanie zapytania do modelu danych trwa znacznie dłużej niż ładowanie do arkusza, sprawdź w krokach dodatku Power Query, czy filtrujesz kolumnę tekstową, czy kolumnę strukturalną listy za pomocą operatora Zawiera . Ta akcja powoduje, że program Excel ponownie wyliczanie za pośrednictwem całego zestawu danych dla każdego wiersza. Ponadto program Excel nie może efektywnie używać wykonywania wielowątkowego. Aby obejść ten problem, spróbuj użyć innego operatora, takiego jak Równa się lub Rozpoczyna się od.

Firma Microsoft wie o tym problemie i jest badana.

Dodatek Power Query można załadować:

• Do arkusza. W Edytorze dodatku Power Query wybierz pozycję Narzędzia główne > Zamknij & Załaduj > Zamknij & Załaduj.

• Do modelu danych. W Edytorze dodatku Power Query wybierz pozycję Narzędzia główne > Zamknij & Załaduj > Zamknij & Załaduj do.
  
  Domyślnie dodatek Power Query ładuje zapytania do nowego arkusza podczas ładowania pojedynczego zapytania i ładuje wiele zapytań jednocześnie do modelu danych.  Możesz zmienić domyślne zachowanie wszystkich skoroszytów lub tylko bieżącego skoroszytu. Podczas ustawiania tych opcji dodatek Power Query nie zmienia wyników zapytania w arkuszu ani w danych i adnotacjach modelu danych.
  
  Możesz również dynamicznie zastąpić ustawienia domyślne zapytania za pomocą okna dialogowego Importowanie wyświetlanego po wybraniu pozycji Zamknij & Załaduj do.

Ustawienia globalne, które mają zastosowanie do wszystkich skoroszytów

1. W Edytorze dodatku Power Query wybierz pozycję Opcje i ustawienia> plików > Opcje zapytania.

2. W oknie dialogowym Opcje zapytania po lewej stronie w sekcji GLOBALNE wybierz pozycję Ładowanie danych.

3. W sekcji Domyślne ustawienia ładowania zapytań wykonaj następujące czynności:

   • Wybierz pozycję Użyj standardowych ustawień ładowania. 

   • Wybierz pozycję Określ niestandardowe domyślne ustawienia ładowania,  a następnie wybierz lub wyczyść pole wyboru Załaduj do arkusza lub Załaduj do modelu danych.

Napiwek    U dołu okna dialogowego możesz wybrać pozycję Przywróć domyślne , aby wygodnie wrócić do ustawień domyślnych.

Ustawienia skoroszytu stosowane tylko do bieżącego skoroszytu

1. W oknie dialogowym Opcje zapytania po lewej stronie w sekcji BIEŻĄCY SKOROSZYT wybierz pozycję Ładowanie danych.

2. Wykonaj co najmniej jedną z następujących czynności:

   • W obszarze Wykrywanie typu zaznacz lub wyczyść pole wyboru Wykryj typy kolumn i nagłówki dla źródeł niestrukturalnych.
     
     Domyślnym zachowaniem jest ich wykrycie. Wyczyść tę opcję, jeśli wolisz samodzielnie kształtować dane.

   • W obszarze Relacje zaznacz lub wyczyść pole wyboru Utwórz relacje między tabelami podczas dodawania do modelu danych po raz pierwszy.

     Przed załadowaniem do modelu danych domyślnym zachowaniem jest znalezienie istniejących relacji między tabelami, takich jak klucze obce w relacyjnej bazie danych, i zaimportowanie ich razem z danymi. Wyczyść tę opcję, jeśli wolisz to zrobić samodzielnie.

   • W obszarze Relacje zaznacz lub wyczyść pozycję Aktualizuj relacje podczas odświeżania zapytań załadowanych do modelu danych.
     
     Zachowaniem domyślnym jest nie aktualizowanie relacji. Gdy zapytania odświeżające są już załadowane do modelu danych, dodatek Power Query znajduje istniejące relacje między tabelami, takimi jak klucze obce, w relacyjnej bazie danych i aktualizuje je. Może to spowodować usunięcie relacji utworzonych ręcznie po zaimportowaniu danych lub wprowadzenie nowych relacji. Jeśli jednak chcesz to zrobić, wybierz odpowiednią opcję.

   • W obszarze Dane w tle zaznacz lub wyczyść pole wyboru Zezwalaj na pobieranie podglądów danych w tle.
     
     Domyślnym zachowaniem jest pobieranie podglądów danych w tle. Wyczyść tę opcję, jeśli chcesz od razu wyświetlić wszystkie dane.