Zapytania parametryczne można łatwo znać i używać w programie SQL lub Microsoft Query. Parametry dodatku Power Query różnią się jednak między kluczami:
- Parametrów można używać w każdym kroku zapytania. Oprócz działania jako filtr danych, parametry mogą być używane do określania takich elementów, jak ścieżka pliku lub nazwa serwera.
- W parametrach nie jest wyświetlany monit o wprowadzenie danych. Zamiast tego można szybko zmienić ich wartość za pomocą dodatku Power Query. Możesz nawet przechowywać i pobierać wartości z komórek w programie Excel.
- Parametry są zapisywane w prostym zapytaniu parametrycznym, ale są niezależne od zapytań danych, w których są używane. Po utworzeniu zapytania można w razie potrzeby dodać do nich parametr.
Wskazówka Jeśli chcesz tworzyć zapytania parametryczne w inny sposób, zobacz Tworzenie zapytania parametrycznego w programie Microsoft Query.
Tworzenie parametru
Parametr umożliwia automatyczną zmianę wartości w zapytaniu i pozwala uniknąć edytowania zapytania za każdym razem w celu zmiany wartości. Wystarczy zmienić wartość parametru. Po utworzeniu parametru jest on zapisywany w specjalnym zapytaniu parametrycznym, które można wygodnie zmienić bezpośrednio z programu Excel.
Wybieranie danych>,Pobieranie danych>, Inne źródła>Uruchom edytor Power Query.
W edytorze Power Query wybierz pozycję Strona główna>Zarządzanie parametrami > Nowe parametry.
W oknie dialogowym Zarządzanie parametrem wybierz pozycję Nowy.
Ustaw następujące elementy zgodnie z potrzebami:
Nazwa Powinien on odzwierciedlać funkcję parametru, ale nie może być możliwie jak najkrótszy. Opis Może on zawierać wszelkie szczegóły, które pomogą użytkownikom w prawidłowym użyciu parametru. Wymagane Wykonaj jedną z następujących czynności:
Dowolna wartość W zapytaniu parametrycznym można wprowadzić dowolną wartość o dowolnym typie danych.
Lista wartości Możesz ograniczyć wartości do określonej listy, wpisując je w małej siatce. Musisz również wybrać wartość domyślną i wartość bieżącą poniżej.
Zapytanie Wybierz zapytanie listy przypominające kolumnę o strukturze listy , oddzieloną przecinkami i ujętą w nawiasy klamrowe.
Na przykład pole Stan problemów może mieć trzy wartości: {"Nowe", "W toku", "Zamknięte"}. Należy wcześniej utworzyć zapytanie listy, otwierając Edytor zaawansowany (wybierz pozycję Narzędzia główneEdytor>zaawansowany), usuwając szablon kodu, wprowadzając listę wartości w formacie listy zapytania, a następnie wybierając przycisk Gotowe.
Po zakończeniu tworzenia parametru zapytanie dotyczące listy jest wyświetlane w wartościach parametrów.Type (Typ) Określa to typ danych parametru. Sugerowane wartości W razie potrzeby możesz dodać listę wartości lub określić zapytanie, aby podać sugestie dotyczące danych wejściowych. Wartość domyślna Ten przycisk jest wyświetlany tylko wtedy, gdy dla właściwości Sugerowane wartości jest ustawiona wartość Lista wartości i jeśli element listy jest domyślny. W takim przypadku należy wybrać ustawienie domyślne. Bieżąca wartość W zależności od tego, gdzie użyjesz parametru, jeśli to pole jest puste, zapytanie może nie zwracać żadnych wyników. Jeśli pole Wymagana jest zaznaczone, wartość bieżąca nie może być pusta. Aby utworzyć parametr, wybierz przycisk OK.
Zmienianie źródła danych za pomocą parametru
Oto sposób zarządzania zmianami lokalizacji źródeł danych i zapobiegania błędom odświeżania. Na przykład, zakładając podobny schemat i źródło danych, utwórz parametr, aby łatwo zmienić źródło danych i zapobiec błędom odświeżania danych. Czasami serwer, baza danych, folder, nazwa pliku lub lokalizacja ulega zmianie. Być może menedżer bazy danych od czasu do czasu wymienia serwer, comiesięczne pobieranie plików CSV trafia do innego folderu lub musisz łatwo przełączać się między środowiskiem programistycznym/testowym/produkcyjnym.
Krok 1. Tworzenie zapytania parametrycznego
W poniższym przykładzie kilka plików CSV zostało zaimportowanych przy użyciu operacji importowania folderu (Wybierz dane>,pobierz dane> z folderu FilesFrom>) z folderu C:\DataFilesCSV1. Czasami jednak jako lokalizacja do upuszczania plików jest używany inny folder, C:\DataFilesCSV2. Możesz użyć parametru w zapytaniu jako wartości zastępczej dla innego folderu.
Wybierz pozycję Strona główna>Zarządzaj parametrami>Nowy parametr.
W oknie dialogowym Zarządzanie parametrem wprowadź następujące informacje:
Nazwa CSVFileDrop Opis Alternatywna lokalizacja upuszczania plików Wymagane Tak Type (Typ) Text (Tekst) Sugerowane wartości Dowolna wartość Bieżąca wartość C:\DataFilesCSV1 Wybierz przycisk OK.
Krok 2. Dodawanie parametru do zapytania danych
- Aby ustawić nazwę folderu jako parametr, w obszarze Ustawienia zapytania w obszarze Kroki zapytania wybierz pozycję Źródło, a następnie wybierz pozycję Edytuj ustawienia.
- Upewnij się, że opcja Ścieżka pliku jest ustawiona na Parametr, a następnie wybierz z listy rozwijanej parametr, który właśnie utworzyłeś.
- Wybierz przycisk OK.
Krok 3. Aktualizowanie wartości parametru
Lokalizacja folderu właśnie została zmieniona, więc teraz możesz po prostu zaktualizować zapytanie parametryczne.
- Wybierz pozycję Połączenia danych>& kartę Zapytania,> kliknij prawym przyciskiem myszy zapytanie parametryczne, a następnie wybierz polecenie Edytuj.
- Wprowadź nową lokalizację w polu Bieżąca wartość , na przykład C:\DataFilesCSV2.
- Wybierz pozycję Narzędzia główne>, Zamknij & Załaduj.
- Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych przy użyciu zaktualizowanego parametru (wybierz> pozycjęOdśwież wszystko).
Filtrowanie danych za pomocą parametru
Czasami może być potrzebna prosta zmiana filtru zapytania w celu uzyskania innych wyników bez edytowania zapytania lub tworzenia nieco innych kopii tego samego zapytania. W tym przykładzie zmieniamy datę, aby wygodnie zmienić filtr danych.
Aby otworzyć zapytanie, znajdź zapytanie załadowane wcześniej z poziomu edytora Power Query, zaznacz komórkę w danych, a następnie wybierz pozycję Edytuj zapytanie>. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie lub edytowanie zapytania w programie Excel.
Wybierz strzałkę filtru w nagłówku kolumny, aby filtrować dane, a następnie wybierz polecenie filtrowania, na przykład Filtry >daty/godzinyPo. Zostanie wyświetlone okno dialogowe Filtrowanie wierszy .
Wybierz przycisk po lewej stronie pola wartości , a następnie wykonaj jedną z następujących czynności:
- Aby użyć istniejącego parametru, wybierz pozycję Parametr, a następnie wybierz odpowiedni parametr z listy, która zostanie wyświetlona po prawej stronie.
- Aby użyć nowego parametru, wybierz pozycję Nowy parametr, a następnie utwórz parametr.
Wprowadź nową datę w polu Bieżąca wartość , a następnie wybierz pozycję Home>Close & Load.
Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych przy użyciu zaktualizowanego parametru (wybierz> pozycjęOdśwież wszystko). Na przykład zmień wartość filtru na inną datę, aby wyświetlić nowe wyniki.
Wprowadź nową datę w polu Bieżąca wartość .
Wybierz pozycję Narzędzia główne>, Zamknij & Załaduj.
Aby potwierdzić wyniki, dodaj nowe dane do źródła danych, a następnie odśwież zapytanie danych przy użyciu zaktualizowanego parametru (wybierz> pozycjęOdśwież wszystko).
Filtrowanie danych za pomocą wartości komórki
W tym przykładzie wartość parametru zapytania jest odczytywana z komórki w skoroszycie. Nie trzeba zmieniać zapytania parametrycznego — wystarczy zaktualizować wartość komórki. Na przykład chcesz przefiltrować kolumnę według pierwszej litery, ale łatwo zmienić wartość na dowolną literę od A do Z.
W arkuszu w skoroszycie, w którym jest załadowane zapytanie, które chcesz filtrować, utwórz tabelę programu Excel z dwiema komórkami: nagłówkiem i wartością.
MyFilter (Mój filtr) Bez ograniczeń Zaznacz komórkę w tabeli programu Excel, a następnie wybierz pozycję Dane>Pobierz dane>z tabeli/zakresu. Zostanie wyświetlony edytor Power Query.
W polu Nazwa okienka Ustawienia zapytania po prawej stronie zmień nazwę zapytania na bardziej opisową, na przykład FilterCellValue.
Aby przekazać wartość do tabeli, a nie do samej tabeli, kliknij prawym przyciskiem myszy wartość w obszarze Podgląd danych, a następnie wybierz polecenie Wyszczególnij.
Zwróć uwagę, że formuła zmieniła się na= #"Changed Type"{0}[MyFilter]
Gdy używasz tabeli programu Excel jako filtru w kroku 10, dodatek Power Query odwołuje się do wartości tabeli jako warunku filtru. Bezpośrednie odwołanie do tabeli programu Excel spowoduje błąd.Wybierz pozycję Home>Zamknij & załaduj załaduj>& załaduj do. Teraz masz parametr zapytania o nazwie "FilterCellValue", którego użyjesz w kroku 12.
W oknie dialogowym Importowanie danych wybierz pozycję Utwórz tylko połączenie, a następnie wybierz przycisk OK.
Otwórz zapytanie, które chcesz filtrować według wartości z tabeli FilterCellValue, załadowanej wcześniej z poziomu edytora Power Query, wybierając komórkę w danych, a następnie wybierając pozycję Edytuj zapytanie>. Aby uzyskać więcej informacji , zobacz Tworzenie, ładowanie lub edytowanie zapytania w programie Excel.
Wybierz strzałkę filtru w nagłówku kolumny, aby przefiltrować dane, a następnie wybierz polecenie filtrowania, na przykład Filtry> tekstuzaczynają się od. Zostanie wyświetlone okno dialogowe Filtrowanie wierszy .
Wprowadź dowolną wartość w polu Wartość, na przykład "G", a następnie wybierz przycisk OK. W tym przypadku wartość jest tymczasowym symbolem zastępczym dla wartości z tabeli FilterCellValue, która zostanie wprowadzona w następnym kroku.
Wybierz strzałkę po prawej stronie paska formuły, aby wyświetlić całą formułę. Oto przykładowy warunek filtru w formule:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Wybierz wartość filtru. W formule zaznacz literę "G".
Korzystając z funkcji M IntelliSense, wprowadź kilka pierwszych liter utworzonej tabeli FilterCellValue, a następnie wybierz ją z wyświetlonej listy.
Wybierz pozycję Narzędzia główne>, Zamknij, Zamknij>& Załaduj.
Wynik
Teraz zapytanie użyje wartości w utworzonej tabeli programu Excel w celu przefiltrowania wyników zapytania. Aby użyć nowej wartości, edytuj zawartość komórek w oryginalnej tabeli programu Excel w kroku 1, zmieniając literę "G" na "V", a następnie odśwież zapytanie.
Sterowanie użyciem zapytań parametrycznych
Możesz kontrolować, czy zapytania parametryczne są dozwolone, czy nie.
- Na Edytor Power Query wybierz pozycję Opcje pliku>i Ustawienia>Opcje> kwerendy Edytor Power Query.
- W okienku po lewej stronie w obszarze GLOBALNE wybierz pozycję Edytor Power Query.
- W okienku po prawej stronie w obszarze Parametry zaznacz lub wyczyść pole wyboru Zawsze zezwalaj na parametryzację w oknach dialogowych źródeł danych i transformacji.