Power Query: połączenie z innym plikiem

Za pomocą Power Query w prosty i szybki sposób można stworzyć dynamiczne łącze do innego pliki Excela. Każda zmiana w pliku źródłowym będzie od razu zaktualizowana i widoczna w tabeli. 

Karta Dane-> grupa Pobierz dane –> Z pliku –>
Ze Skoroszytu

kliknij, aby powiększyć

W kolejnym oknie wybieramy plik Excela, z którego chcemy pobrać dane. 

kliknij, aby powiększyć

Następnie zaznaczamy arkusz:

kliknij, aby powiększyć

 Po kliknięciu Załaduj w prawym dolnym rogu,  nastąpi ładowanie danych do tabeli. 

kliknij, aby powiększyć

Tak jak każdą tabelę również tabelę PQ  można formatować, sortować czy filtrować.  Choć po odświeżeniu (opcja Odśwież po kliknięciu prawym przyciskiem w dowolnym miejscu tabeli) – wszystko wróci do ustawień jak w arkuszu źródłowym. Oczywiście wszystkie zmiany w nim będą też widoczne. 

To najprostszy sposób pobierania danych. Bardziej rozbudowane przykłady – to temat na osobną notkę. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Power Query: lista plików w katalogu

Listę plików w katalogu (oraz w jego podkatalogach)  można szybko i w prosty sposób pobrać za pomocą Power Query. 
Robimy to tak: 

Karta Dane-> grupa Pobierz dane –> Z pliku –> Z folderu

kliknij, aby powiększyć

Otworzy się okno, w którym wskazujemy folder, z którego chcemy pobrać listę plików:

kliknij, aby powiększyć

W kolejnym kroku widoczny jest podgląd zawartości katalogu:

kliknij, aby powiększyć

 Po kliknięciu Załaduj,  nastąpi ładowanie danych do tabeli. Może to chwilę potrwać – w tym czasie na ekranie widoczna będzie informacja:

kliknij, aby powiększyć

Finalnie otrzymujemy tabelę:

kliknij, aby powiększyć

Poszczególne kolumny to:

    • Name
      nazwa pliku
    • Extension
      rozszerzenie
    • Date accessed
      data ostatniego otworzenia pliku
    • Date modified
      data ostatnio zapisanych zmian w pliku
    • Date utworzenia
      data utworzenia pliku

Tak jak każdą tabelę również tabelę PQ  można formatować, sortować czy filtrować.  

Tabelę PQ wstawiamy do pliku raz, potem wystarczy ją tylko odświeżyć. Najszybciej i najprościej – poprzez kliknięcie prawym przyciskiem myszy na tabeli i wybranie opcji Odśwież. 

kliknij, aby powiększyć

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Power Query

Power Query to jedno z narzędzi Excela o bardzo szerokim zastosowaniu. Umożliwia pobranie i połączenie, a finalnie także przetwarzanie danych z różnych źródeł.
Korzystanie z PQ nie wymaga znajomość VBA. Od wersji Excela 2016 jest to narzędzie wbudowane, w wersjach wcześniejszych Excela – konieczne jest (było?) pobranie i zainstalowanie oddzielnego dodatku do Excela. 

Power Query jest dostępne na karcie Dane –> grupa opcji Pobieranie i przekształcanie danych.

Power Query umożliwia pobieranie danych z wielu różnych źródeł, jest tu naprawdę sporo możliwości.
Podstawowe grupy typów to:

    • Z pliku
      do pobierania danych z plików m.in Excela, tekstowych lub JSON, a nawet całych folderów. 

    • Z bazy danych
      tu źródłem mogą być różne typy baz danych
    • Z platformy Azure
      dane pobrane z połaczenia z usługą Azure
    • Z usług online
      czyli to, co można pozyskać z sieci Microsoftu
    • Z innych źródeł
      to, co jest dostępne na stronach internetowych

Każde z nich wymaga osobnego omówienia, co postaram się sukcesywnie robić i rozbudowywać. 

Właściwości pliku

GetFile to jedna z metod obiektu FileSystem.Object.

Ma jeden parametr wejściowy:

    • filespec– pełna nazwa pliku
      Argument obowiązkowy.

Za pomocą tej metody można odczytać następujące właściwości pliku:

    • Name – nazwa folderu.
    • Path – ścieżka do folderu.
    • Size– rozmiar wszystkich plików folderze.
    • DateCreated– data utworzenia.
    • DateLastModified– data ostatniej modyfikacji.
    • ParentFolder– folder nadrzędny.

Lista podfolderów w katalogu

W jaki sposób wylistować listę podfolderów w katalogu?  Jednym z lepszych sposobów jest wykorzystanie metody FileSystemObject.GetFolder. 

Załóżmy, że mamy taki taki układ folderów (przykład z mojego dysku):

kliknij, aby powiększyć

Katalog z listą podfolderów można wylistować w komórkach Excela do takiej postaci:

kliknij, aby powiększyć

Przykładowy kod może wyglądać tak:

Public Sub Foldery()
Dim FSO As Object
Dim Folder As Object
Dim Podfolder As Object
Dim JNazwa As String
Dim NF As String
Dim i As Long
Dim k As Long
Set FSO = CreateObject(„Scripting.FileSystemObject”)
Set Folder = FSO.GetFolder(Range(„A2”).Value)
i = 2
For Each Podfolder In Folder.SubFolders
i = i + 1
JNazwa = Podfolder.Name
Cells(i, 2) = JNazwa
i = Podfoldery(Folder & „\” & JNazwa, 3, i)
Next Podfolder
End Sub
_________________________________
Public Function Podfoldery(JF As String, k As Long, w As Long)
Dim FSOP As Object
Dim FolderP As Object
Dim PodfolderP As Object
Dim JNazwaP As String
Set FSOP = CreateObject(„Scripting.FileSystemObject”)
Set FolderP = FSOP.GetFolder(JF)
For Each PodfolderP In FolderP.SubFolders
w = w + 1
JNazwaP = PodfolderP.Name
Cells(w, 3) = JNazwaP
Next PodfolderP
Podfoldery = w
Set FolderP = Nothing
Set FSOP = Nothing
End Function

Oczywiście kod można rozbudować do wylistowania kolejnych poziomów. 
Jeśli trzeba – mogę pomóc. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela