Gdy tylko udało nam się wczytać w edytorze zapytań Power Query wszystkie pliki z folderu, Klient rzucił kolejne wyzwanie: Czy jest jakiś sposób, aby Excel wczytywał wszystkie pliki z lokalnej ścieżki? Przygotowane rozwiązanie działa w Excelu, ale już nie w Power BI, który nie widzi swojej aktualnej pozycji.

Przygotowanie aktualizującej się ścieżki folderu

Pierwszym krokiem będzie przygotowanie zapytania, ze ścieżką do folderu. W tym celu w nowym arkuszu Excel tworzę tabelę z dwiema kolumnami: Parametr oraz Wartość. W pierwszej komórce tabeli wprowadzam nazwę parametru czyli Ścieżka pliku. W komórce obok wprowadzam formułę, która zwróci mi ścieżkę do folderu, w którym znajduje się aktualnie modyfikowany plik Excel.

 

=LEWY(KOMÓRKA("filename";B1);ZNAJDŹ("[";KOMÓRKA("filename";B1);1)-1)

 

Funkcja KOMÓRKA zwraca informację o formatowaniu, położeniu czy też zawartości komórki. Dzięki funkcji Szacuj formułę (Formuły > Inspekcja formuł > Szacuj formułę), pokażę jakie są kolejne efekty działania tej formuły.

Argument filename zwraca nazwę pliku wraz z jego ścieżką zapisu.

Następnie funkcja ZNAJDŹ wyszukuje w nazwie pliku nawiasu kwadratowego („[„) , by z całej nazwy która pojawia się pod filename  odseparować jedynie ścieżkę pliku bez jego nazwy (w nawiasie kwadratowym pojawia się nazwa pliku).

Funkcja ZNAJDŹ jako wynik, podaje miejsce nawiasu kwadratowego w ciągu znaków.

A następnie funkcja LEWY z całego ciągu tekstowego, ukrytego pod filename, pobiera jedynie ścieżkę.

Tak utworzoną tabelę ładuję do Power Query jako zapytanie Dane > Pobieranie i przekształcanie > Z tabeli, nadaje mu nazwę Parametr.

Formuła w Power Query

Kolejnym krokiem będzie stworzenie formuły, która z zapytania Parametr pobierze jedynie nazwę ścieżki. W tym celu należy otworzyć nowe zapytanie Power Query: Narzędzia główne > Nowe zapytanie > Nowe źródło > Inne źródła > Puste zapytanie.

W Edytorze zaawansowanym (Widok > Edytor Zaawansowany) wprowadzam poniższą treść. Kod w języku M pozwala na pobranie z zapytania Parametry, ścieżki pliku, która w moim przypadku znajduje się w komórce B2. Zaznaczone na niebiesko fragmenty , to elementy zmienne. Jeżeli swoje zapytanie lub kolumny w pliku nazwiesz inaczej, to również w poniższym poleceniu nazwy te musisz zmienić.

Zmieniam nazwę tego zapytania na fnPobierzParametr.

Pobranie danych z folderu

Następnie pobieram dane z folderu. W tym kroku chodzi o powstanie kodu języka M w Edytorze Zaawansowanym, by w kolejnym kroku zmodyfikować go o wcześniej stworzone parametry (więcej o pobieraniu danych z folderu z parametrem). Wybieram w Edytorze zapytań > Narzędzia główne > Nowe zapytanie > Nowe źródło > Plik > Folder.

 

Wskazuję folder z którego będą pobrane dane. Następnie przechodzę do Edytora Zaawansowanego (Widok > Edytor zaawansowany) . Poniżej kod jaki został wygenerowany.

Aby faktycznie ścieżka była dynamiczna, dodam zmienną do której przypiszę Ścieżkę pliku (pobraną przez stworzoną przeze mnie funkcję fnPobierzParametr). Poniżej kod jaki finalnie umieszczam w edytorze.

Pojawia się lista z plikami dostępnymi we wskazanym folderze. Należy pamiętać aby odznaczyć z niej plik do którego pobierane są dane oraz jego wersję niezapisaną ( oznaczoną ~$).

 

Pobierz szablon Excel z zapytaniem Power Query

Pobierz i sprawdź plik Wczytywanie plików z lokalnej ścieżki.