W poprzednim wpisie wyciągnęliśmy listę plików z katalogu za pomocą funkcji makr 4.0. Tym razem chcemy to zrobić za pomocą Power Query. Funkcje w porównaniu do Power Query mają jedną zaletę automatycznie się odświeżają. Natomiast zapytania Power Query przeważnie musimy odświeżać ręcznie. Ale w pozostałych kwestiach uważam, że Power Query się lepiej sprawdza do rozwiązania tego problemu m.in. dlatego, że automatycznie sięga do podkatalogów, a z makrami trzeba już pisać bardzo skomplikowany kod VBA.

Starczy wstępu. Znamy już ścieżkę katalogu (C:\Users\PC\Documents\Pliki), więc możemy zacząć budować zapytanie Power Query. W Excelu 2016 przechodzimy do karty Dane -> polecenie Nowe zapytanie -> Z pliku -> Z folderu…

Pobieranie danych z folderu za pomocą Power Query

Wklejamy ścieżkę w okno, które się pojawi i naciskamy przycisk OK.

Okno ścieżki do folderu

W następnym oknie przechodzimy do Edycji.

Podgląd wyników zapytania z folderu

Pierwsza zaleta na korzyść Power Query w porównaniu do funkcji makr 4.0 – od razu widzimy wyniki zapytania i mamy więcej informacji o pliku niż tylko jego nazwa. 

Dodatkowo jeśli odnajdziemy kolumnę Folder Path, to zobaczymy, że Power Query wyciągnął również pliki z podkatalogów wskazanego katalogu.

Informacje o plikach wyciągnięte przez Power Query

W większości chcemy zaglądać również do podkatalogów wskazanego folderu, ale jeśli ich nie potrzebujesz wystarczy przefiltrować dane po kolumnie Folder Path.

Jeśli chcemy zobaczyć więcej informacji o plikach wystarczy, że rozwiniemy kolumnę Attributes (klikając w dwie strzałki wskazujące w przeciwnych kierunkach w nagłówku tej kolumny).

Lista atrybutów pliku możliwa do rozwinięcia

My więcej atrybutów nie potrzebujemy, chcemy ich mieć wręcz mniej, ale to zrobimy na koniec zapytania. Teraz chcemy przefiltrować dane tak, żebyśmy widzieli tylko pliki Excela. Możemy do tego wykorzystać kolumnę Extension, ale musimy pamiętać, że Power Query jest Case Sensitive, czyli, w przeciwieństwie do Excela, zwraca uwagę na wielkość liter. Czasami się zdarza, że niektóre programy zapisują rozszerzenia wielkimi literami, co może się dla nas okazać problemem. Dlatego przed filtrowaniem zamienimy wszystkie litery w kolumnie Extension na małe (karta Przekształć -> polecenie Format -> małe litery)

Polecenie zamiany tekstu na małe litery

Teraz możemy już przefiltrować dane po kolumnie Extension, tylko nie chcemy po prostu odhaczać niepasujących rozszerzeń, tylko chcemy przejść do filtrów tekstu – zaczyna się od.

Filtry tekstu – zaczyna się od, dla kolumny Extension

W oknie, które się pojawi wpisujemy tekst ‘.xls’, ponieważ interesują nas takie rozszerzenia jak ‘.xls’, ‘.xlsx’, ‘.xlsm’ i podobne związane z Excelem.

Okno filtrowania wierszy

Odfiltrowaliśmy nieinteresujące nas pliki, więc teraz wystarczy usunąć wszystkie kolumny poza Name. To bardzo proste, bo wystarczy na nią kliknąć prawym przyciskiem myszy i w podręcznym menu wybrać opcję Usuń inne kolumny.

Podręczne menu dla kolumny

Teraz pozostaje nam tylko załadować dane do Excela.

Power Query w porównaniu do funkcji makr 4.0 sięga do podkatalogów, pozwala łatwo odfiltrować niechciane katalogi i daje więcej możliwości jeśli chodzi o filtrowanie plików i ich rozszerzeń. Niestety nie odświeża się automatycznie i trudniej mu zmienić ścieżkę do folderu, gdzie funkcja PLIKI patrzy na wartość w komórce, którą łatwo umiemy zmienić (żeby nakłonić Power Query, żeby pobierał ścieżkę do folderu zobacz mój film/wpisu — Porada #305). Czyli jak to często powtarzam w Excelu, jak w życiu, jest wiele sposobów na uzyskanie tego samego, z jakiego sposobu skorzystasz, zależy głównie od Twoich potrzeb i możliwości 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP