Często dane do analizy znajdują się z różnych powodów w wielu plikach: mogą nie mieścić się w jednym pliku, pochodzić z różnych źródeł, być generowane codziennie przez system itp. Power Query, znajdujący się w Excelu i Power BI, daje możliwość połączenia plików jednego formatu (Excel, TXT, CSV, XML, JSON) za pomocą polecenia Z folderu. W artykule opisuję ten sposób i dodaję do niego możliwość dodania parametru, żeby sterować źródłem plików.
Pobierze dane z folderu w Power Query
Aby połączyć dane z wielu plików w Power BI wybieram Narzędzia główne > Pobierz dane > Więcej > Folder > Połącz > wskazuję lokalizację folderu z plikami do połączenia.
Następnie program sam odczytuje pliki jakie znajdują się w folderze. Edytor daje mi w tym momencie kilka możliwości:
Połącz i załaduj
Polecenie przenosi mnie do okna Połącz pliki , gdzie mogę podejrzeć zawartość łączonych plików (rozwijana lista Przykładowy plik), jednak nie mam możliwości ich edytowania. Jedyną opcją wpływającą na złączone dane jest przycisk na dole Pomiń pliki z błędami. Następnie po wybraniu OK dane są łączone i ładowane do modelu, widoczne od razu w Power BI. Wybieram również plik który będzie przekładowym, Pierwszy plik to pierwszy plik znajdując się w folderze. Z listy mogę również wybrać inny, który został w tym folderze przez program odnaleziony.
Połącz i edytuj
Jest to opcja bardzo podobna do wcześniejszej. Również pojawia się okno Połącz pliki, z takimi samymi funkcjami jednak, po wyborze przycisku OK przechodzę do edycji łączonych plików, w Edytorze zapytań.
Załaduj
Polecenie powoduje stworzenie modelu, który można modyfikować (w Edytorze zapytań), ale po wyborze przycisku przenosi mnie od razu do widoku raportu w Power BI, a polami do wyboru w tym momencie są parametry techniczne plików pobranych ze wskazanego folderu (data utworzenia pliku, modyfikacji, rozszerzenie, nazwa), więc na niewiele się to zda w naszym przypadku.
Edytuj
W edytorze zapytań mam możliwość wskazania plików które będą łączone oraz jakie kolumny z nich powinny być połączone
Połącz pliki w interfejsie Edytora Zapytań
Analogiczne polecenie mogę znaleźć w samym Edytorze zapytań. Są dwa miejsca, gdzie mogę wybrać opcję połączenia plików:
- Z kolumny z danymi (mały przycisk z dwiema strzałkami w dół)
- Z Narzędzia główne > Połącz > Połącz pliki
Opcja połączenia plików na pasku Narzędzi głównych uaktywni się tylko w przypadku jeżeli zaznaczę kolumnę z danymi (czyli Content) pozostałe kolumny to jedynie informacje techniczne dot. łączonych plików. Przy pomocy filtrów mogę zaznaczyć, które pliki chcę ze sobą finalnie połączyć.
Co się dzieje podczas łączenia danych z folderu w Edytorze zapytań?
Podczas łączenia danych Power Query:
- Tworzy przykładowe zapytanie, które wykonuje wszystkie wymagane kroki ekstrakcji
- Tworzy parametr, którym jest przykładowy plik binarny.
- Tworzy zapytanie funkcyjne, które parametryzuje dane wejściowe pliku do przykładowego zapytania. Zapytanie funkcyjne i przykładowe są ze sobą połączone, tak by zmiany w przykładowym zapytaniu zostały odzwierciedlone w zapytaniu o funkcję (co można sprawdzić na pasku formuły).
- Stosuje zapytanie funkcyjne do oryginalnego zapytania (z wejściowymi binarnymi), więc stosuje zapytanie funkcyjne dla wejściowych danych binarnych w każdym wierszu i rozszerza wynikową ekstrakcję danych.
Efekt końcowy pobierania danych z folderu
W zapytaniu końcowym widoczne będą dane ze wszystkich plików, zaś w zapytaniu przykładowym – tylko z jednego.
Łączenie plików ze ścieżką jako parametrem
Opisany powyżej sposób jest często wykorzystywany w praktyce, jednak może spowodować, że przy zmianie ścieżki plików wszystkie zapytania pokażą błąd. Co ważne, ścieżka plików pojawia się w naszych zapytaniach 2 razy – raz jako ścieżka folderu, drugi raz jako ścieżka przykładowego pliku. Wykorzystam parametr, aby w Power Query zmieniać tę ścieżkę w jednym miejscu. Parametry opisywałem już w dwóch artykułach:
- Podstawowe zastosowania parametrów w Edytorze zapytań
- Ciekawe przykłady wykorzystania parametrów Power Query
Tworzenie parametru w Power Query
Zacznę od utworzenia parametru , którym będzie ścieżka do łączonych plików. Przechodzę więc do Edytora zapytań Narzędzia główne > Edytuj zapytania > Narzędzia główne Parametry > Zarządzaj parametrami > Nowy parametr.
Dodaję nowy parametr o nazwie Folder, wybieram jako Wymagany, typ Tekst , jako wartość bieżącą wprowadzam ścieżkę do folderu z plikami, które chcę połączyć > OK.
Mając parametr, przechodzę do połączenia plików > Narzędzia główne > Nowe źródło > Więcej > Folder > Połącz > z lewej strony okna wybieram opcję Parametr > Ścieżka folderu > OK.
W kolejnym oknie wybieram Edytuj. Zmieniam nazwę zapytania na PołączPliki, a z zaznaczoną kolumną Content wybieram opcję Połącz pliki (na pasku zadań lub wybierając znak dwóch strzałek na kolumnie). By faktycznie zmienić ścieżkę do plików ze statycznej na dynamiczną przechodzę do edytora zaawansowanego (Narzędzia główne > Zapytanie > Edytor zaawansowany). Znajduję zapytanie Przykładowy plik i zmieniam ścieżkę na parametr:
Następnie przechodzę do zapytania głównego i tam także zmieniam ścieżkę pliku na wartość parametru.
W ten sposób mogę łatwo przenosić dane źródłowe w inne miejsce, a do korekty będzie zawsze tylko i wyłącznie parametr. Ta technika ma jeszcze większe możliwości w Excelu, gdyż tam mamy możliwość pobrania ścieżki aktualnie otwartego pliku, o czym wkrótce napiszę na blogu.
Pobierz plik Power BI z zapytaniem w edytorze zapytań
Pobierz i sprawdź plik Połącz pliki.
Pytanie.
Jak podpiąć dynamiczną ścieżkę do pliku?
Chciałbym wysłać komos gotowy raport aby ta osoba zaciągała sobie na swoim komputerze z pliku lokalnego txt do arkusza z power query.
Jak zdefiniować tą ścieżkę ??
Źródło = Csv.Document(File.Contents(„C:\Users\XXX\Desktop\data.txt”),
by była ona dynamiczna ??
a o tak:
Path = Excel.CurrentWorkbook(){[Name=”FolderPath”]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Path & „DataInput.xlsx”)),
Jeśli dobrze rozumiem, chcesz zbudować zapytanie, które będzie działało na komputerze odbiorcy i pobierze plik tekstowy. Najłatwiej to zrobić sposobem opisanym tu:
https://excelbi.pl/jak-wczytac-do-power-query-wszystkie-pliki-z-lokalnej-sciezki/
Finalne zapytanie miało u mnie taką postać:
let
Ścieżka = fnPobierzParametr(„Ścieżka pliku”),
Źródło = Table.FromColumns({Lines.FromBinary(File.Contents(Ścieżka&”test.txt”), null, null, 1250)})
in
Źródło
Hej, czy w 2022 ten sposób jest nadal aktualny? Zatrzymuję się na tym kroku
„Mając parametr, przechodzę do połączenia plików > Narzędzia główne > Nowe źródło > Więcej > Folder > Połącz > z lewej strony okna wybieram opcję Parametr > Ścieżka folderu > OK.”
Jestem w stanie utworzyć nowy parametr, ale nie mam opcji połącz i nie mogę z lewej strony okna wybrać parametru
Hej Paweł, dzięki za pytanie. Sprawdziłem, że w Power BI działa mi to tak samo.
Krok 1: tworzysz parametr w Power Query.
Krok 2: tworzysz zupełnie nowe zapytanie do folderu i wtedy możesz ścieżkę wskazać tym parametrem (jak na screenie).
Jeśli nie działa, zawsze możesz nagrać na dowolnym folderze i później podmienić ścieżkę już w kodzie języka M.