Power BI – pobieranie plików z określonej lokalizacji

Częstym wymogiem jaki jest stawiany przed narzędziami odpowiedzialnymi za procesy ETL jest ładowanie bliżej nieokreślonej liczby plików o tej samej strukturze znajdujących się w określonej lokalizacji. Większość poważnych narzędzi transformujących posiada takie możliwości, czy wśród nich znajduje się Power BI/ Power Query? Z całą pewnością! Zapraszam na lekturę poniższego artykułu gdzie pokażę jak w bardzo szybki sposób można coś takiego osiągnąć używając kontektora Folder.

Na samym początku spójrzmy na nasz zbiór testowych plików, które przygotowałem na potrzeby niniejszego artykułu.

Mamy w sumie cztery niezbyt wielkie pliki gdzie każdy z nich ma taką samą strukturę i jest po prostu zrzutem danych z bazy AdventureWorksDW. Po otwarciu dowolnego z tych plików możemy zapoznać się z wewnętrznym ułożeniem danych, które przedstawia się następująco:

Na podstawie obserwacji możemy wywnioskować, że:

• pierwszy wiersz zawiera nagłówki,
• każdy kolejny wiersz oznacza osobny rekord,
• poszczególne kolumny są oddzielone tabulatorem,
• teksty nie są otoczone cudzysłowem ani żadnym innym znakiem grupującym ciąg,
• wartości puste są reprezentowane jako pusty ciąg.

Wskazane informacje powinny nam wystarczyć po to aby załadować dane zawarte w każdym z plików. Przejdźmy zatem dalej do Power BI Desktop i spróbujmy załadować wszystkie wspomniane pliki z danej lokalizacji. Aby to zrobić Klikamy na zakładce Home -> Get Data i z listy rozwijanej wybieramy More… aby zobaczyć pełną listę konektorów. Naszym oczom ukaże się nowe okno w którym powinniśmy wybrać konektor o wszystko mówiącej nazwie czyli Folder:

Konfiguracja konektora jest bardzo prosta i sprowadza się do podania ścieżki gdzie znajdują się nasze pliki:

Po zatwierdzeniu klawiszem OK dostaniemy podgląd metadanych odczytanych z systemu plików – w tym miejscu klikniemy Edit aby przejść do okna Power Query (pozwolicie, że tak będę nazywał okno do edycji ETL w Power BI) i nieco bardziej przyjrzeć się temu co zostało stworzone:

Powstało pojedyncze zapytanie z nazwą “C:\” czyli dysku gdzie znajdował się wskazany przeze mnie folder. Mamy również  komplet metadanych będący rezultatem wywołania funkcji Folder.Files języka M:

Jeśli chodzi o same metadane to automatycznie dostaliśmy następujące dane:

• ścieżka folderu z plikami (FolderPath)
• nazwa pliku (Name)
• rozszerzenie (Extension)
• data stworzenia (Data created)
• data modyfikacji (Date modified)
• data dostępu do pliku (Date accessed)

Ponadto gdy rozszerzymy sobie nasz zbiór danych o dodatkowe kolumny dostępne pod szyldem Attributes to otrzymamy następuje kolumny:

Pozwolę sobie wymienić kilka z ważniejszych właściwości:

• Typ zawartości pliku (Content Type)
• Typ pliku (Kind)
• Rozmiar (Size)
• Czy plik jest tylko do odczytu (ReadOnly)
• Czy plik jest ukryty (Hidden)
• Czy plik jest oznaczony jako systemowy (System)
• Plik jest katalogiem (Directory)
• Plik jest kandydatem do tworzenia kopii zapasowej lub usunięcia (Archive)
• Flaga nieużywana do użytku w przyszłości (Device)
• Czy plik jest tymczasowy (Temporary)
• Czy plik jest skompresowany (Compressed)
• Czy plik jest zaszyfrowany (Encrypted)

Całkiem pokaźna lista prawda? Patrząc na te wszystkie właściwości można wywnioskować, że Power BI równie dobrze może służyć jako narzędzie do raportowania folderów współdzielonych lub miejsca na dysku! W standardowych zastosowaniach analitycznych mimo wszystko nie będziemy używać większości z tych własności, ale jak najbardziej warto pamiętać, że mamy do nich bezpośredni dostęp.

Zanim przejdziemy dalej warto stworzyć dynamiczny filtr, który będzie pobierał tylko te pliki, które chcemy załadować. Wzorzec ten powinniśmy tworzyć zawsze nawet jak jesteśmy zapewniani, że nic innego oprócz pożądanych plików do określonej lokalizacji nie trafi.  Tworzenie filtra jest bardzo proste i sprowadza się do zastosowania określonego filtra tekstowego na nazwie pliku. W zobrazowanym poniżej przykładzie wybrałem funkcje “begins with” oraz “ends with”, które to zdefiniują po prostu jak powinna się rozpoczynać oraz kończyć nazwa mojego pliku.

Powyższa transformacja spowoduje wywołanie następującego kodu M:

= Table.SelectRows(Source, each Text.StartsWith([Name], "Products") and Text.EndsWith([Name], ".txt"))

Po jego implementacji nasz proces staje się nieco bardziej uniwersalny i bezpieczny, co powinno i właściwie jest jednym z wymaganych czynników w przypadkach takich jak opisany.

Wiemy zatem jak pobrać te wszystkie metadane, no ale co z zawartością? Kryje się ona w kolumnie Content i możemy ją “wyodrębnić” na kilka sposobów – pierwszym z nich jest użycie transformacji o nazwie Combine Files znajdującej się na wstążce na zakładce Home:

Możemy również po prostu kliknąć w nagłówek kolumny Content:

Efektem obu działań będzie standardowe okno definiowania połączenie do pliku płaskiego:

Większość opcji konfiguracyjnych tego okna jest dopasowana poprawnie przez Power BI. Warto zwrócić uwagę na ustawienie Data Type Detection gdzie wskazujemy na podstawie jakiego zbioru mają być nadane typy (domyślnie jest to 200 wierszy z pierwszego pliku). Po kliknięciu OK zostanie dla nas stworzonych kilka obiektów oraz kroków, którym chciałbym się przyjrzeć.

Pierwszym obiektem jaki został stworzony jest parametr o generycznej nazwie Sample File Parameter1:

Jest to zwykły parametr, który przyjmuje jako wartość zawartość wybranego pliku. To który plik zostanie załadowany jako wartość do parametru definiuje inne zapytanie o nazwie Sample File, które po prostu wybiera pierwszy na liście plik z wybranej przez nas lokalizacji. Kod M tego zapytania przedstawia się następująco:

let
    Source = Folder.Files("C:\Users\adria\Desktop\Product data\"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

czyli tłumacząc powyższy kod na nieco bardziej przystępny język: pobieramy listę plików po czym wybieramy zawartość pierwszego z nich (wskazuje na to użycie indeksu 0). Dalej możemy dostrzec, że utworzone zostało zapytanie “Transform Sample File form C:\”, które jest niczym innym jak “rozpakowaną” zawartością pliku przechowywanego pod Sample File. W tym przypadku kod zapytania wygląda następująco:

let
    Source = Csv.Document(#"Sample File Parameter1",[Delimiter="	", Columns=14, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Na podstawie tego zapytania utworzona została funkcja Transform File from C:\

let
    Source = (#"Sample File Parameter1") => let
        Source = Csv.Document(#"Sample File Parameter1",[Delimiter="	", Columns=14, Encoding=1200, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

Ktoś w tym miejscu może zapytać po co to zapytanie skoro mam już gotową funkcję? Z całą pewnością jest to zasadne pytanie, a odpowiedź być może niektórych zaskoczyć bo brzmi: po nic 🙂 Jeśli chcemy to możemy go usunąć bo i tak w tym miejscu nie ma ono wpływu na nic.

Utworzone obiekty pomocnicze mamy za sobą tak więc przyjrzyjmy się naszemu głównemu zapytaniu, którego poszczególne kroki wyglądają następująco:

let
    Source = Folder.Files("C:\Users\adria\Desktop\Product data\"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from C:\", each #"Transform File from C:\"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from C:\"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from C:\", Table.ColumnNames(#"Transform File from C:\"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ProductKey", Int64.Type}, {"ProductAlternateKey", type text}, {"ProductSubcategoryKey", type any}, {"WeightUnitMeasureCode", type text}, {"SizeUnitMeasureCode", type any}, {"ProductName", type text}, {"StandardCost", type any}, {"FinishedGoodsFlag", type logical}, {"Color", type text}, {"Size", type any}, {"Weight", type text}, {"Class", type text}, {"Style", type any}, {"Description", type any}})
in
    #"Changed Type"

Bez wgłębiania się w strukturę kodu M możemy sobie powiedzieć co robi każdy z kroków:

• Source – wylistowanie listy plików z danej lokalizacji
• #”Filtered Hidden Files1″ – odfiltrowuje pliki oznaczone jako ukryte ( ciekawe zachowanie o którym warto pamiętać aby uniknąć nieporozumień – technicznie odbywa się t po prostu jako odfiltrowanie wierszy gdzie pole Hidden=TRUE)
• #”Invoke Custom Function1″ – wywołanie wspomnianej wcześniej funkcji Transform File from C:\, która po prostu ładuje zawartość pliku, którego ścieżka została przekazana jako parametr
• #”Renamed Columns1″- zamiana nazwy Name na Source.Name
• #”Removed Other Columns1″ – usunięcie wszystkich kolumn oprócz nazwy pliku (kolumny Source.Name) oraz jego zawartości
• #”Expanded Table Column1″ – wypakowanie zawartości każdego z plików. Ciekawe jest tutaj zastosowanie funkcji Tabe.ColumnNames, która odczytuje nazwy kolumn z naszego pliku przekazanego jako próbka (Sample File).
• #”Changed Type” – nadawanie typów danych

W tym miejscu możecie zauważyć jak wiele rzeczy jest wykonywanych w tle – z poziomu interfejsu graficznego sprowadzało się to do paru kliknięć. Oczywiście nie wszystko co wyszło automatycznie jest idealne – w moim mniemaniu warto wykonać jeszcze kilka dodatkowych kroków jeśli używamy właśnie tej techniki do ładowania plików.

Przede wszystkim powinniśmy:

• Dodać parametr ze ścieżką do folderu – w przypadku zmiany lokalizacji plików nie chcielibyśmy szukać wszystkich odwołań ręcznie prawda?
• Sprawdzić ustawione typy danych w kroku #”Changed Type” czy aby na pewno są one prawidłowe i zamienić typy Any na prawidłowe
• Zmienić nazwy poszczególnych zapytań – z pełną premedytacją zostawiłem nazwy, które były wygenerowane automatycznie ponieważ użytkownicy zazwyczaj tak robić. Czy jest to intuicyjne i proste w zrozumieniu? ODpowiedź jest raczej jednoznaczna
• Zawsze pamiętajmy aby stworzyć filtr zawężający listę plików do tylko tych, które nas interesują

A jeśli chcemy zrobić to naprawdę tak jak potrzeba bez zbędnego komplikowania? Może lepiej nie korzystać z interfejsu graficznego? Osobiście nie używam powyższego sposobu ze względu na poziom nadmiernego skomplikowania przy tak prostej operacji. Poniżej pokaże trochę uproszczoną metodę, która w gruncie rzeczy zrobi to samo.

Pierwszym krokiem będzie pobranie listy plików, a następnie stworzenie pełnej ścieżki do każdego z nich:

To co następnie należy zrobić to pobrać wybrany plik w standardowy sposób:

Gdy już mamy listę i załadowany przykładowy plik to większość drogi za nami. Teraz wystarczy nasze zapytanie z odwołaniem do przykładowego pliku zamienić na funkcję – zrobimy to w standardowy sposób dodając “()=>” przed sekcją LET. Ponadto w sam nawias podamy parametr tekstowy który podstawimy pod ścieżkę pliku do załadowania:

(Fullpath as text)=>
let
    Source = Csv.Document(File.Contents(Fullpath),[Delimiter="	", Columns=14, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProductKey", Int64.Type}, {"ProductAlternateKey", type text}, {"ProductSubcategoryKey", type text}, {"WeightUnitMeasureCode", type text}, {"SizeUnitMeasureCode", type text}, {"ProductName", type text}, {"StandardCost", type text}, {"FinishedGoodsFlag", type logical}, {"Color", type text}, {"Size", type text}, {"Weight", type text}, {"Class", type text}, {"Style", type text}, {"Description", type text}})
in
    #"Changed Type"

W międzyczasie zmieniłem nazwę zapytania na udf_GetPlanData aby bardziej odpowiadała ona temu czym ten obiekt jest. Jedyne co nam teraz pozostało to wywołać funkcję  dla każdego pliku z listy:

Finalnie otrzymaliśmy pożądany efekt, a struktura naszego ETL jest dużo bardziej przejrzysta. Ponadto technika ta jest nieco bardziej uniwersalna bo pozwala nam ładować dodatkowo przed łączeniem danych z kilku plików wykonać dodatkowe transformacje co z całą pewnością jest częstym wymogiem. Ogólnie rzecz biorąc ładowanie danych z interfejsu graficznego czy też sposobem “na piechotę” pozwala ładować dane nie tylko z plików płaskich ale również z m.in plików xml, json czy chociażby Excel.

Nikomu nie powinno to sprawić problemu, wystarczy naprawdę minimalna wiedza na temat narzędzia i języka aby osiągnąć fajne efekty i znacząco ograniczyć liczbę powstałych obiektów. Warto również zwrócić uwagę na fakt, że przedstawione tutaj przykłady wykonałem w Power BI Desktop, jednakże praca z  Power Query w Excelu w tym konkretnym przypadku powinna być niemal identyczna. W kolejnych wpisach powiemy sobie nieco więcej o tworzeniu funkcji oraz o implikacjach wydajnościowych w Power BI/ Power Query.