Tworzenie formuł dodatku Power Query w programie Excel

Edytor dodatku Power Query udostępnia środowisko zapytania danych oraz możliwości kształtowania Excel, za pomocą których można ponownie kształtować dane z wielu źródeł danych. Aby wyświetlić okno Edytora dodatku Power Query,zaimportuj dane z zewnętrznych źródeł danych w 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 Edytora dodatku Power Query, która umożliwia kształtowanie danych

2. Okienko Zapytania, za pomocą których odszukasz źródła danych i tabele

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

4. Podgląd danych, w którym są wyświetlane wyniki kroków zastosowanych do danych

5. Okienko Ustawienia, które zawiera właściwości i poszczególne kroki zapytania.

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

Może się jednak okazać, że trzeba zmodyfikować lub utworzyć formułę. W formułach jest używany język formuł dodatku Power Query, za pomocą którego można tworzyć zarówno proste, jak i złożone wyrażenia. Aby uzyskać więcej informacji o składni, argumentach, spostrzeżeniach, funkcjach i przykładach, zobacz Język formuł M dodatku Power Query.

Używając jako przykładu listy drużyn piłkarskich, użyj dodatku Power Query, aby przechować nieprzetworzone dane znalezione w witrynie sieci Web i przekształcić je w dobrze sformatowaną tabelę. Zobacz, jak są tworzone kroki zapytania i odpowiadające im formuły dla każdego zadania w okienku Ustawienia zapytania w obszarze Zastosowane kroki i na pasku Formuła.

Procedura

1. Aby zaimportować dane, wybierz pozycję Dane> z sieci Web,wprowadź "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ę Results [Edit] (Wyniki [Edycja] po lewej stronie, a następnie wybierz pozycję Transform Data (Przekształć dane) u dołu. Zostanie wyświetlony edytor dodatku Power Query.

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

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

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

6. Aby usunąć wiersze z wyrazem "Rok", wybierz strzałkę filtru w kolumnie Kolumna1,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ń nazwę "Kolumna1" na "Rok", kolumnę "Kolumna4" na "Zwycięzca" i "Kolumna5" na "Wynik końcowy".

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

Wynik

W poniższej tabeli przedstawiono podsumowanie każdego zastosowanego etapu i odpowiadającej mu formuły.

Etap i zadanie kwerendy	Formuła
Źródło Połączenie ze źródłem danych sieci Web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Nawigacja Wybierz tabelę do połączenia	=Source{2}[Data]
Zmieniony typ Zmienianie typów danych (które są automatycznie używane w dodatku Power Query)	= 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ść zastąpiona Zamienianie wartości w celu oczyszczenia wartości w zaznaczonej kolumnie	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrowane wiersze Filtrowanie wartości w kolumnie	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Kolumny o zmienionej nazwy Zmieniono nagłówki kolumn tak, aby zawierały zrozumiały opis	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Ważne    Podczas edytowania kroków Źródło, Nawigacjai Zmieniony typ należy zachować ostrożność, ponieważ są one tworzone przez dodatku Power Query w celu zdefiniowania i   skonfigurowania źródła danych.

Pokazywanie lub ukrywanie paska formuły

Domyślnie wyświetlany jest pasek formuły, ale jeśli jest niewidoczny, można go ponownie wyświetlać.

• Wybierz pozycję Widok > układu > pasku formuły.

Edit a formula in the formula bar

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

2. W okienku Ustawienia w obszarze Zastosowane krokiwybierz 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łę tak, aby zawierała również kolumnę Kolumna2:
   
   Przed: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Po:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

Tworzenie formuły na pasku formuły

Aby uzyskać przykład prostej formuły, nadamy wartość tekstową z prawidłową literą przy użyciu funkcji Text.Proper.

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

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

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

Wynik:

 Podczas tworzenia formuły w dodatku Power Query jest sprawdzana poprawność składni formuły. Jednak wstawienie, zmiana kolejności lub usunięcie kroku pośredniego w zapytaniu może spowodować potencjalne zerwanie zapytania.  Zawsze sprawdzaj wyniki w podglądzie danych.

Ważne    Podczas edytowania kroków Źródło, Nawigacjai Zmieniony typ należy zachować ostrożność, ponieważ są one tworzone przez dodatku 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 etapu. Nie musisz znać składni formuły.

1. Aby otworzyć zapytanie, znajdź wcześniej załadowaną komórkę w Edytorze dodatku Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Zapytanie i> 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 dla kroku, który chcesz edytować, lub kliknij krok prawym przyciskiem myszy, a następnie wybierz pozycję Edytuj Ustawienia .

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

Wstawianie kroku

Po zakończeniu kroku zapytania, który ponownie kształtuje dane, zostanie on dodany poniżej bieżącego kroku zapytania. ale wstawienie kroku zapytania w środku kroków może spowodować błąd w kolejnych krokach. Podczas próby wstawienia nowego kroku w dodatku Power Query jest wyświetlane ostrzeżenie Wstaw krok, a nowy krok powoduje zmianę pól, takich jak nazwy kolumn, które są używane w dowolnych krokach, które są następujące po wstawieniu kroku.

1. W okienku Ustawienia 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. Możesz też kliknąć krok prawym przyciskiem myszy, a następnie wybrać polecenie Wstaw krok po.Zostanie utworzona nowa formuła w formacie :
   
   = <nameOfTheStepToReference>, na przykład =Production.WorkOrder.

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

Zmiana kolejności kroku

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

Usuń krok

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

W tym przykładzie nadamy tekstom w kolumnie prawidłową literę, używając kombinacji formuł w Edytorze zaawansowanym. 

Na przykład masz tabelę z tabelą Excel o nazwie Zamówienia, zawierającą kolumnę ProductName, którą chcesz przekonwertować na tabelę z prawidłową literą. 

Przed:

Po:

Podczas tworzenia zapytania zaawansowanego jest tworzyć serie 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 następnie odwołuje się klauzula in definiująca klauzulę Step. W tym przykładzie zwracany jest taki sam wynik jak wynik w sekcji "Tworzenie formuły na pasku formuły".

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

Zobaczysz, że każdy krok opiera się na poprzednim kroku, odwołując się do kroku według nazwy. Przypomnienie: w języku formuł dodatku Power Query jest wróżniana wielkość liter.

Etap 1. Otwieranie Edytora zaawansowanego

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

2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > w Edytorze zaawansowanym, który zostanie otwarty z szablonem wyrażenia ze let.

Etap 2. Definiowanie źródła danych

1. Utwórz wyrażenie ze Excel. Funkcja CurrentWorkbook w następujący sposób:
   
   let#x1
   
   in
       Source
   
   

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

Wynik:

Etap 3. Podniesienie pierwszego wiersza do nagłówków

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

2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > Edytor zaawansowany, który zostanie otwarty z zestawieniem utworzonym w fazie 2:Definiowanie źródła danych.

3. W wyrażeniu let dodaj funkcje #"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)#x3
   
       #"First Row as Header"

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

Wynik:

Etap 4. Zmienianie każdej wartości w kolumnie na wartość z odpowiednią literą

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

2. W Edytorze dodatku Power Query wybierz pozycję Narzędzia > Edytor zaawansowany, który zostanie otwarty wraz z instrukcje utworzone w fazie 3.Pod poziomem pierwszego wiersza wybierz pozycję Nagłówki .

3. W wyrażeniu let przekonwertuj każdą wartość kolumny ProductName na odpowiednią wartość, używając funkcji Table.TransformColumns, odwołując się do poprzedniego kroku formuły zapytania "First Row as Header", dodając formułę #"Capitalized Each Word" do źródła danych, a następnie przypisując formułę #"Capitalized Each Word" do wyniku w in.
   
   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> Zamknij & Załaduj > Zamknij & Załaduj.

Wynik:

Możesz sterować działaniem paska formuły w Edytorze dodatku Power Query dla wszystkich skoroszytów.

Wyświetlanie lub ukrywanie paska formuły

1. Wybierz pozycję Opcje> i Ustawienia > Opcje zapytania.

2. W lewym okienku w obszarze GLOBAL wybierzpozycję Edytor dodatku Power Query.

3. W prawym okienku w obszarze Układzaznacz lub wyczyść pole wyboru Wyświetl pasek formuły.

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

1. Wybierz pozycję Opcje> i Ustawienia > Opcje zapytania.

2. W lewym okienku w obszarze GLOBAL wybierzpozycję Edytor dodatku Power Query.

3. W prawym okienku w obszarze Formułazaznacz 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 zostanie w związku z tym w związku z następnym otwarciem okna Edytora dodatku Power Query.