Tworzenie formuł dodatku Power Query w programie Excel

Tworzenie prostej formuły

Aby przedstawić przykład prostej formuły, zapiszemy wartości tekstowe z wielkiej litery, korzystając z formuły Text.Proper() .

1. Na karcie wstążki POWER QUERY wybierz pozycję Z innych źródeł > Puste zapytanie.

   

2. Na pasku formuły w Edytorze zapytań wpisz = Text.Proper("wartość tekstowa") i naciśnij klawisz Enter lub wybierz ikonę Enter.

3. Wyniki w dodatku Power Query zostaną wyświetlone w okienku wyników formuły.

4. Aby wyświetlić wynik w arkuszu programu Excel, wybierz pozycję Zamknij i załaduj.

Wynik w arkuszu będzie wyglądać następująco:

W Edytorze zapytań możesz także tworzyć formuły zapytań zaawansowanych.

Tworzenie formuły zaawansowanej

Aby przedstawić przykład formuły zaawansowanej, nadamy tekstom w kolumnie prawidłową wielkość liter, korzystając z kombinacji formuł. W celu połączenia wielu formuł w kroki zapytania, które zwrócą wynik w postaci zbioru danych, można użyć języka formuł dodatku Power Query. Wynik może również zostać zaimportowany do arkusza programu Excel.

Załóżmy na przykład, że masz tabelę programu Excel zawierającą nazwy produktów, które chcesz zapisać z wielkiej litery.

Pierwotna tabela wygląda następująco:

Chcesz, aby tabela wynikowa wyglądała następująco:

Przyjrzyjmy się krokom formuły zapytania, aby w pierwotnej tabeli wartości w kolumnie ProductName zapisać z wielkiej litery.

Przykład tworzenia zapytania zaawansowanego przy użyciu Edytora zaawansowanego

Aby wyczyścić pierwotną tabelę, użyj Edytora zaawansowanego w celu utworzenia kroków formuły zapytania. Skonstruujemy poszczególne kroki formuły zapytania, aby zaprezentować sposób tworzenia zapytania zaawansowanego. Poniżej przedstawiono wszystkie kroki formuły zapytania. Podczas tworzenia zapytania zaawansowanego należy postępować zgodnie z tym procesem:

• Utwórz serię kroków formuły zapytania, które zaczynają się od instrukcji let. Pamiętaj, że w języku formuł dodatku Power Query jest uwzględniana wielkość liter.

• Każdy krok formuły zapytania opiera się na poprzednim kroku, odwołując się do kroku według nazwy.

• W celu uzyskania danych wyjściowych z kroku formuły zapytania użyj instrukcji in. W ogólnym ujęciu ostatni krok zapytania jest stosowany jako wynik ostatniego zbioru danych z instrukcji in.

Krok 1. Otwieranie Edytora zaawansowanego

1. Na karcie wstążki POWER QUERY wybierz pozycję Z innych źródeł > Puste zapytanie.

2. W Edytorze zapytań wybierz pozycję Edytor zaawansowany.

   

3. Zostanie wyświetlony Edytor zaawansowany.

   

Krok 2. Definiowanie oryginalnego źródła

W Edytorze zaawansowanym:

1. Użyj instrukcji let, która spowoduje przypisanie formuły Source = Excel.CurrentWorkbook(). Dzięki temu tabela programu Excel będzie używana jako źródło danych. Aby uzyskać więcej informacji na temat formuły Excel.CurrentWorkbook(), zobacz Excel.CurrentWorkbook.

2. Przypisz Source (Źródło) do wyniku in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. Zaawansowane zapytanie będzie wyglądało następująco w Edytorze zaawansowanym.

   

4. Aby wyświetlić wyniki w arkuszu:

   1. Kliknij pozycję Gotowe.

   2. Na wstążce Edytora zapytań kliknij pozycję Zamknij i załaduj.

Wynik w arkuszu wygląda następująco:

Krok 3. Przekształcanie pierwszego wiersza w nagłówki

Aby przekonwertować wartości w kolumnie ProductName na wartości zapisane z wielkiej litery, należy najpierw przekształcić pierwszy wiersz w nagłówki kolumn. Zrób to w Edytorze zaawansowanym:

1. Dodaj formułę #"First Row as Header" = Table.PromoteHeaders() do kroków formuły zapytania i utwórz odniesienie do Source jako do źródła danych. Aby uzyskać więcej informacji na temat formuły Table.PromoteHeaders(), zobacz Table.PromoteHeaders.

2. Przypisz formułę #"First Row as Header" do wyniku w instrukcji in.

   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source)
   in
       #"First Row as Header"

Wynik w arkuszu wygląda następująco:

Krok 4. Zmienianie każdej wartości w kolumnie na wartość zapisaną z wielkiej litery

Aby przekonwertować każdą wartość w kolumnie ProductName na zapisaną z wielkiej litery, użyj formuły Table.TransformColumns() i utwórz odniesienie do kroku formuły zapytania „First Row as Header” (Pierwszy wiersz jako nagłówek). Zrób to w Edytorze zaawansowanym:

1. Dodaj formułę zamiany pierwszej litery każdego wyrazu na wielką #"Capitalized Each Word" = Table.TransformColumns() do kroków formuły zapytania, a następnie utwórz odniesienie do formuły #"First Row as Header" jako do źródła danych. Aby uzyskać więcej informacji na temat formuły Table.TransformColumns(), zobacz Table.TransformColumns.

2. Przypisz formułę #"Capitalized Each Word" do wyniku instrukcji 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"

Ostateczny wynik zmieni każdą wartość w kolumnie ProductName na zapisaną z wielkiej litery, a wynik będzie wyglądał następująco w arkuszu:

Za pomocą języka formuł dodatku Power Query można tworzyć zapytania proste oraz zaawansowane przeznaczone do odnajdowania, łączenia i uściślania danych. Aby uzyskać więcej informacji na temat dodatku Power Query, zobacz Microsoft Power Query dla programu Excel — Pomoc.