Power Query dynamiczne źródło danych - zmiana źródła z listy

Co miesiąc dostajesz nowy plik z zamówieniami, a raport w Excelu ma pokazać dane właśnie z niego. Zwykle kończy się to otwieraniem Power Query i ręcznym poprawianiem ścieżki w kroku Źródło. Da się to zrobić wygodniej: ścieżkę wybierasz z listy w komórce arkusza, odświeżasz i raport pokazuje dane z innego pliku.

W poradniku budujemy takie dynamiczne źródło danych w trzech zapytaniach: listę plików z folderu, komórkę z wybraną ścieżką i import, który z tej ścieżki korzysta. Przykładem są zamówienia dwóch lokali z trzech miesięcy, każdy miesiąc w osobnym folderze.

Najważniejszy jest krok drugi, w którym wartość z komórki arkusza staje się parametrem zapytania. Ten sam mechanizm przyda się wszędzie tam, gdzie raport ma zależeć od wyboru użytkownika.

W tym poradniku:

1. Przykład: zamówienia z trzech miesięcy
2. Krok 1. Lista plików z folderu
3. Krok 2. Ścieżka z komórki jako parametr
4. Krok 3. Import z wybranego pliku
5. Gdy zapytanie nie chce się odświeżyć

Przykład: zamówienia z trzech miesięcy

W archiwum do pobrania jest folder dane z podfolderami styczeń, luty i marzec. W każdym leżą dwa skoroszyty, na przykład „styczeń lokal A” i „styczeń lokal B”, z listą zamówionych produktów: data, lokal, produkt i wartość zamówienia. Obok leży skoroszyt Zestawienie, w którym jest raport: tabela przestawna z zamówieniami wybranego lokalu w danym miesiącu.

Zapytania w pliku szukają danych w folderze C:\dane. Najprościej rozpakować archiwum właśnie tam. Jeśli wolisz inne miejsce, zmień adres folderu w kroku Źródło zapytania „dane”, opisanego niżej.

Krok 1. Lista plików z folderu

Ręczne wpisywanie ścieżki prędzej czy później skończy się literówką. Dlatego najpierw robimy listę wszystkich plików, z której będzie się wybierać.

1. W skoroszycie Zestawienie wybierz Dane > Pobierz dane > Z pliku > Z folderu, wskaż folder C:\dane i kliknij Przekształć dane. Power Query pokaże wszystkie pliki, także z podfolderów.
2. W kolumnie Name odfiltruj sam plik Zestawienie.xlsx oraz plik tymczasowy ~$Zestawienie.xlsx, który Excel tworzy, gdy skoroszyt jest otwarty.
3. Zostaw tylko kolumny Folder Path i Name.
4. Dodaj kolumnę > Kolumna niestandardowa o nazwie „ścieżka do pliku” z formułą [Folder Path]&[Name], a potem usuń dwie pozostałe kolumny.
5. Nazwij zapytanie „dane” i załaduj je do tabeli w osobnym arkuszu.

W arkuszu pojawi się kolumna pełnych ścieżek, na przykład C:\dane\luty\luty lokal B.xlsx. Kiedy w kwietniu dołożysz folder z nowymi plikami, wystarczy odświeżyć to zapytanie, a lista sama się wydłuży.

Krok 2. Ścieżka z komórki jako parametr

Teraz komórka, w której wybiera się plik. Poprawność danych nie przyjmuje kolumny tabeli wprost jako źródła listy, więc potrzebny jest pośrednik:

1. W Formuły > Menedżer nazw utwórz nazwę „ścieżka_dane”, która odwołuje się do kolumny tabeli: =dane[ścieżka do pliku].
2. W arkuszu z raportem wpisz nagłówek „ścieżka”, a pod nim dowolną ścieżkę z listy. Zamień te dwie komórki w tabelę (Ctrl + T) i nazwij ją „ścieżka” na karcie Projekt tabeli.
3. Komórce ze ścieżką ustaw Dane > Poprawność danych, kryterium Lista, źródło =ścieżka_dane. Od teraz plik wybiera się z rozwijanej listy.
4. Kliknij w tabelę „ścieżka” i wybierz Dane > Z tabeli/zakresu. W edytorze Power Query kliknij prawym przyciskiem samą komórkę z adresem i wybierz Przejdź do szczegółów. Zapytanie zamiast tabeli zwróci jeden tekst.
5. Nazwij to zapytanie „ścieżka” i wybierz Zamknij i załaduj do > Tylko utwórz połączenie.

W edytorze zaawansowanym to zapytanie wygląda tak:

let
    Źródło = Excel.CurrentWorkbook(){[Name="ścieżka"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"ścieżka", type text}}),
    ścieżka1 = #"Zmieniono typ"{0}[ścieżka]
in
    ścieżka1

Ostatni krok, {0}[ścieżka], bierze pierwszy wiersz kolumny „ścieżka”. Dzięki temu wynik nie jest tabelą, tylko zwykłym tekstem, którego da się użyć w innym zapytaniu zamiast wpisanego ręcznie adresu.

Krok 3. Import z wybranego pliku

Zaimportuj zwyczajnie jeden z plików miesięcznych (Dane > Pobierz dane > Z pliku > Ze skoroszytu) i przygotuj dane do raportu. W przykładzie to zamiana daty na nazwę miesiąca i grupowanie po miesiącu, lokalu i produkcie z sumą wartości zamówień. Potem otwórz Edytor zaawansowany i w kroku Źródło zamień wpisaną ścieżkę na nazwę zapytania z kroku 2:

Źródło = Excel.Workbook(File.Contents(ścieżka), null, true),

Bez cudzysłowu, bo to nie jest tekst, tylko odwołanie do zapytania „ścieżka”. Załaduj wynik do tabeli przestawnej. Teraz wybierz w komórce inny plik, kliknij Dane > Odśwież wszystko i raport pokaże dane z marca zamiast stycznia albo z lokalu B zamiast A.

Warunek jest jeden: wszystkie pliki muszą mieć ten sam układ, czyli taki sam arkusz z tymi samymi nagłówkami. Zapytanie szuka arkusza po nazwie, w przykładzie „Arkusz1”. Plik z inną nazwą arkusza albo innymi kolumnami skończy się błędem przy odświeżeniu.

Gdy zapytanie nie chce się odświeżyć

• Błąd o poziomach prywatności („Zapytanie odwołuje się do innych zapytań lub kroków…”). Power Query nie lubi łączyć danych z arkusza z danymi z pliku na dysku. W pliku, którego dane znasz, wybierz Dane > Pobierz dane > Opcje zapytania, w sekcji Prywatność dla bieżącego skoroszytu zaznacz ignorowanie poziomów prywatności i odśwież jeszcze raz.
• Nie znaleziono pliku. Lista ścieżek jest aktualna na moment ostatniego odświeżenia. Po przeniesieniu albo zmianie nazwy pliku najpierw odśwież zapytanie „dane”, potem wybierz ścieżkę na nowo.
• Raport pokazuje stare dane po zmianie ścieżki. Samo wybranie pliku z listy niczego nie przelicza, trzeba jeszcze odświeżyć zapytania.

Jeśli zamiast wybierać jeden plik chcesz połączyć wszystkie naraz, wystarczy samo zapytanie „Z folderu” i przycisk łączenia plików, bez parametru. Więcej o pierwszych krokach z Power Query jest w poradniku Power Query w Excelu.

⚡ Power Query Zaawansowany

Raporty, które same wiedzą, skąd wziąć dane

✓Zmiana ścieżki do źródeł danych i automatyczna zmiana ścieżki

✓Dynamiczny parametr z listą plików w folderze

✓Parametry i własne funkcje w Power Query

✓Gwarancja zwrotu: 30 dni

Zobacz kurs zaawansowany

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy