Podczas ostatniego szkolenia otwartego w Warszawie nt. wizualizacji danych i projektowania dashboardów w Excelu jeden z uczestników zapytał o pobieranie danych z folderu. Power Query daje bardzo duże możliwości automatyzacji procesu pobierania danych, w tym utworzenia zapytania do folderu i ręcznego wybierania plików do pobrania. Proces ten opisuję krok po kroku w tym wpisie. Ale prawdziwe efekty uzyskamy, gdy zapytanie będzie pobierać automatycznie wszystkie pliki, a nam zostanie klikać Odśwież – ten sposób jest dostępny także tutaj, ale bardziej zaawansowane rozwiązanie jest omówione w kolejnym poście.
Pobieranie plików z folderu za pomocą Power Query
W katalogu Dane znajdują się 2 pliki.
Na karcie Power Query wybieramy polecenie Z pliku > Z folderu. Opcja ta umożliwia import wielu plików jednocześnie.
W kolejnym kroku należy wskazać ścieżkę (folder), w której znajdują się wszystkie pliki do importu.
W edytorze zapytań, po zaznaczeniu kolumny Content i kliknięciu prawym przyciskiem myszy, pojawi się lista, z której należy wybrać opcję Usuń inne kolumny. Kolumna Content odpowiada za nasz plik.
W kolejnym etapie należy dodać kolumnę niestandardową (Dodaj kolumnę > Dodawanie kolumny niestandardowej) oraz wpisać poniższą formułę
= Excel.Workbook([Content])
Uwaga: opisana w obecnym przykładzie metoda importu danych dotyczy plików w formacie xlsx. W przypadku innych formatów działania mogą się różnić.
W edytorze pojawi się nowa kolumna (PobierzDane) z obiektami tabeli oraz symbolem strzałek w prawym górnym rogu. Po ich kliknięciu należy wybrać OK.
Power Query wyświetli wszystkie obiekty znajdujące się w 2 plikach. Z kolumny PobierzDane.Item należy odfiltrować tylko interesujące nas arkusze tj. Arkusz1 z plików 1 i 2.
Następnie zaznaczamy kolumnę PobierzDane.Data i usuwamy pozostałe kolumny.
W tym momencie możliwe jest już zaimportowanie danych poprzez wybór strzałek w górnym rogu kolumny PobierzDane.Data. Rozwiązanie to wymagałoby jednak kilku dodatkowych operacji na danych na dalszym etapie. Żeby ich uniknąć, posłużymy się formułą w kolumnie niestandardowej (Dodaj kolumnę > Dodawanie kolumny niestandardowej):
=Table.PromoteHeaders([PobierzDane.Data])
W ostatnim etapie możemy usunąć kolumnę PobierzDane.Data, rozwinąć kolumnę DaneZaimportowane (za pomocą strzałek w nagłówku), odznaczając Użyj oryginalnej nazwy kolumn jako prefiksu, a następnie wciskamy OK. Odznaczenie Użyj oryginalnej nazwy kolumny jako prefiksu spowoduje, że w tabeli wynikowej nagłówki kolumn nie będą posiadały zbędnego prefiksu DaneZaimportowane np. DaneZaimportowane.Data wysyłki.
Opcja Zamknij i załaduj w zakładce Narzędzia główne ładuje dane do nowego arkusza Excel w formacie tabeli.
Jak dodać nowe dane do pobrania poprzez Power Query?
O ile na żadnym etapie polecenia w Power Query nasz nowy plik nie zostanie odfiltrowany (np. w powyższym ćwiczeniu odfiltrowaliśmy tylko arkusze o nazwie Arkusz1), może wystarczyć skopiowanie nowego pliku do folderu i wybranie polecenia Odśwież. W bardziej zaawansowanych przypadkach potrzebne jest bardziej uniwersalne polecenie, które pokazuję w kolejnym poście.
Plik do pobrania
Możesz pobrać załączony katalog i samodzielnie przetestować gotowy skrypt Power Query. Na swoim komputerze podmień tylko ścieżkę katalogu:
Cześć, super artykuł 😉
czy jest jeszcze możliwość wstawiania w oddzielnej kolumnie nazwy pliku (w nazwie poszczególnych plików data) z którego zaimportowano dane ?
Przede wszystkim przepraszam za zwlekanie z odpowiedzią!
Najprościej osiągnąć taki efekt po prostu nie kasując kolumny z nazwą pliku („Name”) – jest ona wstawiana zawsze przy importowaniu danych z określonego folderu.
W powyżej opisanym przypadku skasowaliśmy wszystkie kolumny oprócz „Content”, dlatego też w tabeli finalnej nie znalazły się nazwy plików.
Cześć, czy po przygotowaniu takiego arkusza w którym połączono dane z folderu z sharepointa jest możliwość ustawienia takiego rodzaju kolumny która nie będzie ulegać odświeżaniu po załadowaniu nowych danych podczas odświeżania? Mam taki problem że po dodaniu komentarzy do istniejących już pozycji, znikają mają one w związku z tym że dane źródłowe z sharepointa ich nie zawierają
Hej Konrad, niestety nie da się tego łatwo zrobić. Najczęściej pod komentarze budujemy oddzielne źródło danych i scalamy je w Power Query, żeby komentarze nie znikały lub żeby zawsze pasowały do określonego wiersza.
Dzień dobry,
Z jakiego powodu dane z wybranych arkuszy pobierane są wraz z pustymi wierszami? Nie da się ich usunąć po zastosowaniu polecenia „usuń puste wiersze”. Bardzo proszę o pomoc.
Power Query uważa za „pusty wiersz” taki który we wszystkich polach zawiera „null”. Może w danych znajdują się tzw. białe znaki?
Czy może Pani podesłać próbkę danych – lukasz@skuteczneraporty.pl
Dziękuję za odpowiedź – zbadam sprawę z białymi znakami.
Mam jeszcze jeden problem – nie wiem z jakiego powodu kolejność pobieranych kolumn zmienia się na alfabetyczną – chciałabym aby była taka, jak w pobieranych plikach. Jak można temu zapobiec? Ręczne przestawianie jest po prostu uciążliwe i czasochłonne, gdy kolumn jest wiele.
Witam,
Jestem ciekawy czy usunięcie błiałych znaków pomogło?
W jakiej sytuacji takie działanie ma miejsce? Tzn, skąd importuje Pani dane i czy używa Pani Power Query?
Witam!
Rewelacyjny step-by-step 🙂 Bardzo mi pomógł!
Czy istnieje możliwość, aby podczas scalania założyć filtr na tytuły plików? Mam serię plików, które w nazwie różnią się jedynie datą. Scalenie w moim przypadku oznacza wyszukiwanie danych w plikach starszych niż dzisiejszy, a więc chciałbym scalać pliki wszystkie poza tym, który ma w nazwie dzisiejszą datę tak jak w przykładzie: „161108_nazwa_pliku.xlsx”.
Z góry dziękuję za pomoc!
Dzień dobry,
jasno i rzeczowo opisane krok po kroku co należy zrobić – tego właśnie szukałem! Mam natomiast małe pytanie – czy jest szansa na zmniejszenie wielkości pliku? Przy 82 tys wierszy i około 15 kolumn plik „muli” a to tylko zakres danych kwartalnych. Czy wówczas należy korzystać z PowerPivot?
Witam,
Jeżeli chodzi o filtry wejściowych plików.
Tak – istnieje możliwość zakładania filtrów. Jeżeli wybierzemy pobieranie danych z folderu, to następnie możemy prosto odfiltrować które pliki chcemy procesować. Klikamy na kolumnę Name i używamy standardowych filtrów. Pozostałe kroki pozostają niezmienione.
Witam,
Power Query jest narzędziem z zasady wymagającym jeżeli chodzi o zasoby sprzętowe – wynika to z faktu, że całkowicie operuje na danych w pamięci RAM gdzie zapisuje każdy krok wykonywany w kwerendzie. To oznacza, że jeżeli dysponujemy słabszą maszyną to możemy zaobserwować powolne działanie aplikacji. Nie do końca rozumiem pytanie dotyczące wielkości pliku ? – czy mogę poprosić o więcej szczegółów?
Super artykuł, praktycznie nie pozostawia wątpliwości jak to zrobić. Mam tylko jeden problem, bo po „zaciągnięciu” danych przez power query z plików w folderze do jednej tabeli na podstawie której zrobiłem tabele przestawną, chciałem jeszcze zmodyfikować zapytanie w power query i tu pojawia mi się komunikat z błędem o braku dostępu do pliku bo już inny proces korzysta z tej tabeli. Jak mogę sobie z tym poradzić?
Witam,
A czy przypadkiem plik z którego Pan importuje dane nie jest otwarty?
Jest to ten sam plik, gdzie w jednym arkuszu jest wynik zapytania a w drugim arkuszu jest tabela przestawna która bazuje na danych z pierwszego arkusza. Chciałem aby było wygodniej i wszystko w jednym miejscu. Problem się pojawił w momencie edycji zapytania 🙁
Proponuję wstawić tabelę przestawną nie na tabeli wynikowej tylko załadować zapytanie jako „Tylko połączenie” i utworzyć tabelę przestawną poprzez Użyj połączenia zewnętrznego > Wybierz połączenie.
a jak to będzie w przypadku plików xlsm? Scaliło mi wszystkie pliki, jednakże nie chce aktualizować, wyświetla się komunikat „The proces cannot access the file because it is being used by another proces” Z góry dziękuje za odpowiedź
Bardziej wygląda to na problem z pobieraniem pliku tymczasowego, który jest zapisywany w folderze w momencie, gdy plik główny jest otwarty. Proszę spróbować go odfiltrować, np. krokiem zaraz po kroku Source / Źródło:
= Table.SelectRows(Source, each not Text.StartsWith([Name], „~$”))
Ewentualnie można go analogicznie odfiltrować w interfejsie poprzez filtr po nazwie pliku z $.
Witam, mam kilka pytań odnośnie power query, mam w pracy do zrobienia liste plików, ale:
zawiera ona spora liczbe plików(ok to 3mln plikow do przefiltrowania) i jak moglbym to dobrze zrobic i co i jak ustawic.
Reszte wytlumacze na priv
Pozdrawiam Paweł
Gdzieś umknął nam komentarz. Proszę o maila na kontakt@skuteczneraporty.pl.
Co w przypadku, gdy plik z danymi zwiększa lub zmniejsza liczbę kolumn o jedną? Po odświeżeniu brakuje kolumny, która została doklejona.
W takiej sytuacji moment rozpakowania warto oprzeć na dynamicznym elemencie (np. na liście). Proces wygląda tak:
1. Przygotować plik wzorcowy (np. pierwszy w folderze)
2. Utworzyć na jego bazie zapytanie z jedną kolumną zawierającą nagłówki
3. Skonwertować zapytanie na listę
4. Wstawić listę w kodzie M dla kroku rozwijania.
Cześć – a czy nie ma obejścia z dostępem do pliku który jest aktualnie otwarty przez kogoś innego?
Pozdrawiam,
Tomek
Hej Tomek, na poziomie widoku plików w Power Query (czyli tuż po pierwszym kroku) można ustawić krok filtrowania wg reguły: nie zawiera „~” (tyldy). Tak oznaczane są ukryte pliki otwarte.
Co wpisać w formule =Excel.Workbook([Content]) jeśli chcemy połączyć pliki .txt?
Hej Alicja, coś w stylu:
=Csv.Document([Content],[Delimiter=”,”, Columns=6, Encoding=1250, QuoteStyle=QuoteStyle.None])
Od momentu utworzenia powyższego artykułu trochę rzeczy się zmieniło, więc zerknij na wideo z ostatniego webinaru:
https://excelbi.pl/jak-pobierac-do-excela-i-power-bi-pliki-z-folderu-z-power-query-webinar-z-1-10-2020-wideo/
hej,
A jak zrobić tak żeby mi się nazwa użytkownika który dokonywał zmian pokazywała się automatycznie?
Hej Sebastian, taki parametr nie jest widoczny przez Power Query.
Dzień dobry,
Mam problem, po wykonaniu zapytania dubluje mi wartości.
Gdzie popełniam błąd??
Czy to może być związane z tym…
Po wykonaniu polecenia Excel.Workbook i rozwnięciu tabeli
Do kadego pliku pokazuje mi np.3 albo 2 pozycje tj. np.
Dane121
Dane121!xlnm_FilterDatabese
Dane1211
aczkolwiek powiedzmy na kilkanaście plików w folderze nie do każdego jest pozycja …!xlnm.FilterDatabase
???
Hej Piotr, FilterDatabase jest pozostałością po autofiltrze z Excela. Po nazwie usuń takie wiersze.
Ok, dzięki za podpowiedź.
to ciekawe skąd w takim razie bierze się pozycja Dane 1211… w każdym pliku jest tylko jedne arkusz.. w tym akurat o nazwie Dane 121.
Jakiś pomysł ??
Chyba musiałbyś podesłać pliki do przetestowania lub screen z Power Query.
Witaj Bartku, a jak zrobić połączenie plików csv? Niby PQ łączy te pliki, ale automatycznie tworzy przy tym swoje kroki, których wolałbym uniknąć. Przy próbie połączenia wg powyższego scenariusza problem powstanee przy funkcji =Table.Workbook… Jest jakieś rozwiązanie, czy jednak lepiej przeformatować do xlsx?
Cześć Rafał,
od czas powstania artykułu, sporo się zmieniło i nagrałem w tym zakresie webinar. W nim znajdziesz odpowiedzi na wszystkie pytania:
https://excelbi.pl/jak-pobierac-do-excela-i-power-bi-pliki-z-folderu-z-power-query-webinar-z-1-10-2020-wideo/
Wpis nadal bardzo pomocny. Dziękuję. U mnie pojawiły się 2 problemy:
1. kolumny zdublowały się, chociaż raczej nagłówki, wartości w zdublowanych kolumnach są wszędzie null,
2. nie przeszły wartości z drugiego pliku, mam same nulle. Przeszły wartości tylko z pierwszego pliku.
FilterDatabase usunęłam.
Po kilku poprawkach nie mam już zdublowanych kolumn, z drugiego skonsolidowanego pliku przeszły tylko wartości liczbowe, lecz tekstowe i daty nadal występują jako null. Coś idzie nie tak, tylko co?
Cześć Halina, żeby to sprawdzić, podeślij mi pliki źródłowe lub chociaż ich nagłówki i zapytanie końcowe na kontakt(at)skuteczneraporty.pl.
Jestem świeżak w pq. Uczę się, czytam, oglądam. Wszyscy piszą, jak dodać, jak połączyć… Ja mam inny problem. Mam po kimś pq gdzie łączy się dane z dwóch plików o tej samej strukturze ale z różnych spółek. Aktualnie po odświeżeniu dane są widoczne dla obu spółek. Chce odłączyć całkowicie dane z jednej spółki. Chce aby pq pokazywało dane wyłącznie z jednej spółki. Jak to zrobić. Będę wdzięczna za pomoc.Pozdrawiam 🙂
Hej Zuza,
Opcja 1. Usuń krok Dołącz zapytania.
Opcja 2. Niech dane się dołączą, a Ty na końcu załóż filtr i usuń dane niepotrzebnej spółki (to jest oczywiście mniej efektywne).
Obejrzyj film o PQ, żeby mieć podstawową wiedzę z budowy kroków: https://excelbi.pl/1-godzinne-szkolenie-wideo-z-excel-power-query-webinar-z-25-06-2020/