Tworzenie formuł dodatku Power Query w programie Excel

Edytor Power Query zapewnia programowi Excel zapytanie danych i środowisko kształtowania, za pomocą którego można zmieniać kształty danych z wielu źródeł danych. Aby wyświetlić Edytor Power Query oknie, zaimportuj dane z zewnętrznych źródeł danychw arkuszu programu Excel, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Poniżej przedstawiono podsumowanie głównych składników.

1. Wstążka Edytor Power Query umożliwiająca kształtowanie danych

2. Okienko Zapytania używane do lokalizowania źródeł danych i tabel

3. Menu kontekstowe, które są wygodnymi skrótami do poleceń na wstążce

4. Podgląd danych wyświetlający wyniki kroków zastosowanych do danych

5. Okienko Ustawienia kwerendy zawierające listę właściwości i poszczególnych kroków zapytania

W tle każdy krok w zapytaniu jest oparty na formule widocznej na pasku formuły.

Może się zdarzyć, że zechcesz zmodyfikować lub utworzyć formułę. W formułach jest używany Power Query język formuł, który umożliwia tworzenie zarówno prostych, jak i złożonych wyrażeń. Aby uzyskać więcej informacji na temat składni, argumentów, uwag, funkcji i przykładów, zobacz Power Query język formuł M.

Używając jako przykładu listy mistrzostw w piłce nożnej, użyj Power Query, aby wziąć nieprzetworzone dane znalezione w witrynie internetowej i przekształcić je w dobrze sformatowaną tabelę. Obejrzyj, jak dla każdego zadania są tworzone kroki zapytania i odpowiadające im formuły, w okienku Ustawienia zapytania w obszarze Zastosowane kroki i na pasku formuły.

Procedura

1. Aby zaimportować dane, wybierz pozycję > Dane z sieci Web, wprowadź ciąg "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" w polu Adres URL , a następnie wybierz przycisk OK.

2. W oknie dialogowym Nawigator wybierz tabelę Wyniki [Edycja] po lewej stronie, a następnie wybierz pozycję Przekształć dane u dołu. Zostanie wyświetlony edytor Power Query.

3. Aby zmienić domyślną nazwę zapytania, w okienku Ustawienia kwerendy w obszarze Właściwości usuń pozycję "Wyniki [Edycja]", a następnie wprowadź ciąg "Mistrzowie UEFA".

4. Aby usunąć niechciane kolumny, zaznacz pierwszą, czwartą i piątą kolumnę, a następnie wybierz pozycję Narzędzia główne > Usuń kolumnę > Usuń inne kolumny.

5. Aby usunąć niechciane wartości, wybierz pozycję Kolumna1, wybierz pozycję Narzędzia główne > Zamień wartości, wprowadź "szczegóły" w polu Wartości do znalezienia, a następnie wybierz przycisk OK.

6. Aby usunąć wiersze zawierające wyraz "Rok", wybierz strzałkę filtru w kolumnie 1, wyczyść pole wyboru obok pozycji "Rok", a następnie wybierz przycisk OK.

7. Aby zmienić nazwy nagłówków kolumn, kliknij dwukrotnie każdy z nich, a następnie zmień wartość "Kolumna1" na "Rok", "Kolumna4" na "Zwycięzca" i "Kolumna5" na "Wynik końcowy".

8. Aby zapisać zapytanie, wybierz pozycję Narzędzia główne > Zamknij & Załaduj.

Wynik

Poniższa tabela zawiera podsumowanie każdego zastosowanego kroku i odpowiadającej mu formuły.

Krok i zadanie kwerendy	Formuła
Źródło Nawiązywanie połączenia ze źródłem danych sieci Web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Nawigacja Zaznacz tabelę, z którym chcesz nawiązać połączenie	=Source{2}[Data]
Zmieniono typ Zmienianie typów danych (które Power Query są wykonywane automatycznie)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Usunięto inne kolumny Usuwanie innych kolumn w celu wyświetlenia tylko potrzebnych kolumn	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Wartość zamieniona Zamienianie wartości w celu oczyszczenia wartości w wybranej kolumnie	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Odfiltrowane wiersze Filtrowanie wartości w kolumnie	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Kolumny o zmienionej nazwie Zmieniono nagłówki kolumn, aby były zrozumiałe	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Ważne    Zachowaj ostrożność podczas edytowania kroków Źródło, Nawigacja  i Zmieniony typ, ponieważ są one tworzone przez Power Query w celu zdefiniowania i skonfigurowania źródła danych.

Pokazywanie lub ukrywanie paska formuły

Pasek formuły jest domyślnie wyświetlany, ale jeśli nie jest widoczny, możesz go ponownie wyświetlić.

• Wybierz pozycję Wyświetl > układ > pasek formuły.

Edit a formula in the formula bar

1. Aby otworzyć zapytanie, znajdź zapytanie załadowane wcześniej z Edytor Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. W okienku Ustawienia zapytania w obszarze Zastosowane kroki wybierz krok, który chcesz edytować.

3. Na pasku formuły znajdź i zmień wartości parametrów, a następnie wybierz ikonę Wprowadź lub naciśnij klawisz Enter. Na przykład zmień tę formułę, aby zachować również kolumnę Kolumna2:
   
   Przed: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   po:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Wybierz ikonę Enter lub naciśnij klawisz Enter, aby wyświetlić nowe wyniki w podglądzie danych.

5. Aby wyświetlić wynik w arkuszu programu Excel, wybierz pozycję Narzędzia główne> Zamknij & Załaduj.

Tworzenie formuły na pasku formuły

W przypadku prostego przykładu formuły przekonwertujmy wartość tekstową na wielkość liter z wielkiej litery przy użyciu funkcji Tekst.Z wielkiej litery.

1. Aby otworzyć puste zapytanie, w programie Excel wybierz pozycję Dane > Pobierz > danychz innych źródeł > puste zapytanie. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. Na pasku formuły wpisz=Text.Proper("text value"), a następnie wybierz ikonę Enter lub naciśnij klawisz Enter.
   
   Wyniki zostaną wyświetlone w podglądzie danych.

3. Aby wyświetlić wynik w arkuszu programu Excel, wybierz pozycję Narzędzia główne> Zamknij & Załaduj.

Wynik:

 Podczas tworzenia formuły Power Query sprawdza poprawność składni formuły. Jednak w przypadku wstawiania, zmieniania kolejności lub usuwania etapu pośredniego w zapytaniu może to spowodować przerwanie zapytania.  Zawsze sprawdzaj wyniki w podglądzie danych.

Ważne    Zachowaj ostrożność podczas edytowania kroków Źródło, Nawigacja  i Zmieniony typ, ponieważ są one tworzone przez Power Query w celu zdefiniowania i skonfigurowania źródła danych.

Edytowanie formuły przy użyciu okna dialogowego

Ta metoda umożliwia korzystanie z okien dialogowych, które różnią się w zależności od kroku. Nie musisz znać składni formuły.

1. Aby otworzyć zapytanie, znajdź zapytanie załadowane wcześniej z Edytor Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. W okienku Ustawienia zapytania w obszarze Zastosowane kroki wybierz ikonę Edytuj ustawienia kroku, który chcesz edytować, lub kliknij krok prawym przyciskiem myszy, a następnie wybierz pozycję Edytuj ustawienia.

3. W oknie dialogowym wprowadź zmiany, a następnie wybierz przycisk OK.

Wstawianie kroku

Po wykonaniu kroku zapytania, który zmienia dane, poniżej bieżącego kroku zapytania jest dodawany krok zapytania. ale po wstawieniu kroku zapytania pośrodku kroków może wystąpić błąd w kolejnych krokach. Power Query podczas próby wstawienia nowego kroku jest wyświetlane ostrzeżenie Wstaw krok, a nowy krok powoduje zmianę pól, takich jak nazwy kolumn, które są używane w dowolnej z czynności, które następują po wstawionym kroku.

1. W okienku Ustawienia zapytania w obszarze Zastosowane kroki wybierz krok, który ma być bezpośrednio poprzedzony nowym krokiem i odpowiadającą mu formułą.

2. Wybierz ikonę Dodaj krok po lewej stronie paska formuły. Ewentualnie kliknij prawym przyciskiem myszy krok, a następnie wybierz pozycję Wstaw krok po. W formacie :
   
   = <nameOfTheStepToReference>, na przykład =Production.WorkOrder, zostanie utworzona nowa formuła.

3. Wpisz nową formułę przy użyciu formatu:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Załóżmy na przykład, że masz tabelę z kolumną Płeć i chcesz dodać kolumnę z wartością "Pani". lub "Pan",w zależności od płci danej osoby. Formuła będzie następująca:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Zmienianie kolejności kroku

• W okienku Ustawienia zapytań w obszarze Zastosowane kroki kliknij prawym przyciskiem myszy krok, a następnie wybierz pozycję Przenieś w górę lub Przenieś w dół.

Usuń krok

• Wybierz ikonę Usuń po lewej stronie kroku lub kliknij krok prawym przyciskiem myszy, a następnie wybierz pozycję Usuń lub Usuń do końca. Ikona Usuń jest również dostępna po lewej stronie paska formuły.

W tym przykładzie przekonwertujmy tekst w kolumnie na wielkość liter z wielkiej litery przy użyciu kombinacji formuł w Edytor zaawansowany. 

Na przykład masz tabelę programu Excel o nazwie Zamówienia z kolumną NazwaProduktu, którą chcesz przekonwertować na porządną wielkość liter. 

Przed:

Po:

Podczas tworzenia zapytania zaawansowanego tworzy się serię kroków formuły zapytania na podstawie wyrażenia let. Użyj wyrażenia let , aby przypisać nazwy i obliczyć wartości, do których odwołuje się klauzula in , definiującą Krok. W tym przykładzie zwracany jest ten sam wynik co wynik w sekcji "Tworzenie formuły na pasku formuły".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Zobaczysz, że każdy krok bazuje na poprzednim kroku, odwołując się do kroku po nazwie. Przypominamy, że w Power Query Język formuł jest uwzględniana wielkość liter.

Faza 1. Otwarcie Edytor zaawansowany

1. W programie Excel wybierz pozycję Dane > Pobierz dane > Inne źródła > puste zapytanie. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. W Edytor Power Query wybierz pozycję Narzędzia główne> Edytor zaawansowany, który zostanie otwarty z szablonem wyrażenia let.

Faza 2. Definiowanie źródła danych

1. Utwórz wyrażenie zezwalania przy użyciu funkcji Excel.CurrentWorkbook w następujący sposób:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe, a następnie wybierz pozycję Narzędzia główne> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Faza 3. Podwyższanie poziomu pierwszego wiersza do poziomu nagłówków

1. Aby otworzyć zapytanie, w arkuszu zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji, zobacz Tworzenie, ładowanie lub edytowanie zapytania w programie Excel (Power Query).

2. W Edytor Power Query wybierz pozycję Narzędzia główne> Edytor zaawansowany, który zostanie otwarty z instrukcją utworzoną w fazie 2. Definiowanie źródła danych.

3. W wyrażeniu let dodaj #"First Row as Header" i Table.PromoteHeaders w następujący sposób:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x4in
   

4. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe, a następnie wybierz pozycję Narzędzia główne> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Faza 4. Zmienianie każdej wartości w kolumnie na z wielkiej litery

1. Aby otworzyć zapytanie, w arkuszu zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie > Edytuj. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie i edytowanie zapytania w programie Excel.

2. W Edytor Power Query wybierz pozycję Narzędzia główne > Edytor zaawansowany, który zostanie otwarty wraz z instrukcją utworzoną w fazie 3. Podwyższanie poziomu pierwszego wiersza do poziomu nagłówków.

3. W wyrażeniu let konwertuj każdą wartość kolumny ProductName na właściwy tekst, używając funkcji Table.TransformColumns, odwołując się do poprzedniego kroku formuły zapytania "Pierwszy wiersz jako nagłówek", dodając do źródła danych ciąg #"Capitalized Each Word", a następnie przypisując do wyniku wyniku #"Capitalized Each Word".
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Aby załadować zapytanie do arkusza, wybierz pozycję Gotowe, a następnie wybierz pozycję Narzędzia główne> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Możesz kontrolować zachowanie paska formuły w Edytor Power Query dla wszystkich skoroszytów.

Wyświetlanie lub ukrywanie paska formuły

1. Wybierz pozycję Opcje> plików i Ustawienia > Opcje zapytania.

2. W lewym okienku w obszarze GLOBALNA wybierz pozycję Edytor Power Query.

3. W prawym okienku w obszarze Układ zaznacz lub wyczyść pozycję Wyświetl pasek formuły.

Włączanie lub wyłączanie funkcji M Intellisense

1. Wybierz pozycję Opcje> plików i Ustawienia > Opcje zapytania .

2. W lewym okienku w obszarze GLOBALNA wybierz pozycję Edytor Power Query.

3. W prawym okienku w obszarze Formuła zaznacz lub wyczyść pole wyboru Włącz funkcję M Intellisense na pasku formuły, w edytorze zaawansowanym i w oknie dialogowym kolumny niestandardowej.

Uwaga    Zmiana tego ustawienia zacznie obowiązywać przy następnym otwarciu okna Edytor Power Query.