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.
Cześć,
osobiście walczę z ciut innym problemem, zamiast 1 parametru chciałbym na podstawie tabeli w excelu ograniczyć „joinem” wynik zapytania SQL z bazy danych. Tzn. mam tabele w excelu z produktami i dla nich chciałbym wyciągnąć dane za pomocą zapytania SQL z bazy danych.
Hej Rafał, w takiej sytuacji potrzebujesz zbudować 2 zapytania:
1 – tabela z Excela
2 – tabela z bazy SQL
Następnie wykonać operację Scalanie (Merge).
Cześć 🙂
Dziękuje za odpowiedź, nie mniej jednak nie do końca mogę z tej podpowiedzi skorzystać :/.
Problem polega na tym, że już podczas wyciągania danych z systemu muszę je ograniczyć zapytaniem SQL do pozycji jakie znajdują się w tabeli w arkuszu xls. Jeżeli nie zrobię tego od razu tylko najpierw wyciagnę całość danych a dopiero później za pomocą scalania danych w PQ ograniczę tą tabele do pozycji z tabeli XLS to zbyt mocno obciążę system, z którym się łączę 🙁
Hej, wydaje mi się, że da się to osiągnąć w sposób opisany w poniższym artykule, modyfikując kod zapytania do bazy z poziomu Edytora zaawansowanego:
https://www.fourmoo.com/2020/10/08/power-query-m-passing-parameters-dynamically-to-a-sql-server-query/
Cześć,
Jak dodać kolejne 3 linijki aby mieć drugą komórkę przez która można potem odwoływać się do zapytania?
Obecnie posiadam taki kod w edytorze zaawansowanym (w zapytaniu, a pod nim zapytanie SQL jako Źródło2):
let
Źródło = Excel.CurrentWorkbook(){[Name=”Tabela1″]}[Content],
#”Zmieniono typ” = Table.TransformColumnTypes(Źródło,{{„Wprowadź datę”, type text}}),
Parametr = #”Zmieniono typ”{0}[Wprowadź datę],
Zależy mi aby dodać kolejny parametr po którym będę zmieniał np. płeć w zapytaniu na podstawie innej komórki. Natomiast po przeklejeniu kroków coś mi nie gra, jeśli dobrze pamiętam to z „zmieniono typ”
Czy da się to zrobić? Jeśli tak proszę o pomoc jakie kroki należy podjąć. 🙂
Hej Paweł, kolejne parametry możesz definiować w kolejnych linijkach kodu, np.
Parametr2 = #”Zmieniono typ”{0}[Płeć]
Jeśli coś Ci nie działa, podeślij plik na maila.
Bartek,
dzięki Tobie zrobiłem to zadanie, ale pojawił się problem…
W momencie gdy komórka w Excelu nie zawiera żadnej daty (jest pusta), zapytanie wyrzuca mi do Excela dane będące listą tabel bazy SQL do której odnosi się zapytanie.
Jak to wyeliminować ?
Dodam, że w samym zapytaniu za pomocą „Case” przewidziałem taką ewentualność i umieściłem kawałek kodu który uruchamia działanie alternatywne (konkretnie wybiera zestaw danych z ostatniego zapisu – Max [Data]
Jakiś pomysł ?
Z góry dziękuję.
Hej Robert, możesz dodać jakiś warunek w stylu: jeśli Data jest pusta to weź dzisiejszą datę.