Scenariusz pobierania danych z bazy danych poprzez zapytanie SQL z parametrami z komórek był zwykle realizowany z użyciem VBA. Opisywane na blogu pobieranie parametrów zapytania z komórek Excel do Power Query wydaje się idealnym pomysłem na zastąpienie takich makr, jednak szybko natrafimy na dodatkowe wyzwania typu Formula.Firewall. Oto podpowiedź, jak ominąć tę przeszkodę i podawać parametry zapytania SQL z komórek arkusza Excel.
Krok 1: Stwórz tabelę w Excelu z komórką o tekstowym formacie
Przedstawiony przykład dotyczy podania parametru typu data. Jednak łatwiej będzie pracować od razu na tekście, dlatego tworzymy nagłówek, a następnie ustawiamy format komórki Excel na tekstowy. Na koniec wpisujemy datę, która stanie się parametrem i tworzymy z tego tabelę.
Krok 2: Stwórz zapytanie z tabeli
Zamiast pobierać dane z zewnętrznego źródła, utworzymy zapytanie na tej tabeli.
Krok 3: Wejdź do środka komórki w Power Query
Aby stworzyć parametr, wejdź do środka komórki poprzez polecenie Wyszczególnij.
Krok 4: Zbuduj zapytanie do bazy danych SQL
W tym celu utwórz nowe zapytanie (np. z bazy danych SQL) > rozwiń opcje zaawansowane > wklej instrukcję SQL w okienko.
Gdybyśmy na tym etapie próbowali podmienić datę na nasz parametr w oknie Edytora zaawansowanego lub w pasku formuły:
otrzymamy następujący komunikat:
Oznacza on, że zapytanie SQL nie może odnosić się do innych zapytań i samo zapytanie Power Query musi zawierać wszystko w jednym kodzie. Dlatego kolejny krok wymaga edycji zapytania.
Krok 5: Skopiuj kroki zapytania z parametrem i wklej je do zapytania z bazą
Operację kopiuj-wklej wykonasz w oknie Edytora zaawansowanego. To jednak nie wystarczy, dlatego w kodzie języka M wykonaj dodatkowe zmiany:
- Zmień nazwę ostatniego kroku na Parametr.
- Dodaj przecinek na końcu linijki kodu z parametrem.
- Zmień powtarzającą się nazwę Źródło np. na Źródło2.
- Wstaw parametr w miejsce kodu za pomocą znaku łączenia tekstowego (&). Pamiętaj o pozostawieniu apostrofów i uzupełnieniu o znaki cudzysłowu.
Krok 6: Przetestuj działanie z poziomu Excela
Zapytanie z parametrem ustaw jako połączenie, a końcowe zapytanie do bazy jako tabelę. Umieść obie tabele na jednym arkuszu, zmień parametr i odśwież zapytanie. Powinno zadziałać.
Krok 7 (opcjonalnie): Wyłącz ostrzeżenia Power Query
Ponieważ natywne zapytanie SQL może w ekstremalnym przypadku dokonać zmian w bazie, domyślnie włączone są ostrzeżenia Power Query. Wymuszają one każdorazowe zaakceptowanie wysyłanego zapytania SQL.
Jeśli chcemy to ostrzeżenie wyłączyć, potrzebujemy zarówno u siebie, jak i u każdego użytkownika końcowego, wyłączyć to w opcjach Power Query.
Ile wierszy można tak pobrać?
Jeśli wynik ma wylądować w arkuszu Excel jako tabela, otrzymamy maksymalnie 1 048 575 wierszy. Jeśli wynik może wylądować w tabeli przestawnej, z wykorzystaniem modelu danych można próbować i do 100 000 000 wierszy.
