Max i Min wartość dla unikalnych wierszy — Power Query #17

Dziś wyznaczymy, za pomocą Power Query, minimalną i maksymalną wartość dla unikalnych wierszy. W pytaniu widzów 120 robiliśmy to za pomocą tabeli przestawnej. Zadanie to jest nawet prostsze gdy korzystamy z Power Query, bo nie trzeba zmieniać tylu domyślnych ustawień co w przypadku tabel przestawnych.

W przykładowych danych (rys. 1) chcemy wyciągnąć minimalną i maksymalną wartość czasu, ale analogicznie postępujemy dla dowolnej liczby, bądź daty.

rys. 1 – Przykładowe dane

rys. 1 – Przykładowe dane

Nasze dane są tabelą Excela, więc możemy je łatwo zaczytać do Power Query (Excel 2016 – Karta Dane – polecenie Z tabeli).

Może się zdarzyć, że Power Query źle zinterpretuje dane, ale łatwo wskazać mu ich właściwy format – wystarczy wybrać odpowiednią pozycję menu po kliknięciu w ikonę typu danych w nagłówku kolumny (rys. 2).

rys. 2 – Zmiana typu danych na prawidłowy

rys. 2 – Zmiana typu danych na prawidłowy

Gdy wszystkie kolumny mają już prawidłowy typ danych możemy znaleźć nasz minimnalne i maksymalne wartości po warunkach, tylko przed kliknięciem odpowiedniego polecenia (Grupowanie według – karta Narzędzia główne – rys. 3) warto zaznaczyć kolumny, po których będziemy grupować (kolumny po których chcemy wyznaczać unikalne wartości). Są to kolumny data i osoba.

rys. 3 – polecenie Grupowanie według

rys. 3 – polecenie Grupowanie według

Dzięki temu Power Query będzie od razu wiedział, że chcemy grupować dane po 2 kolumnach i odpowiednio wypełni nam okno grupowania (rys. 4).

rys. 4 – okno grupowania z wypełnionymi polami na podstawie zaznaczonych wcześniej kolumn

rys. 4 – okno grupowania z wypełnionymi polami na podstawie zaznaczonych wcześniej kolumn

Teraz musimy dodać tylko kolejny poziom agregacji (przycisk Dodawanie agregacji), żeby znaleźć Min i Max po kolumnie Czas (rys. 5)

rys. 5 – Okno grupowania z uzupełnionymi poziomami agregacji/sposobami podsumowania

rys. 5 – Okno grupowania z uzupełnionymi poziomami agregacji/sposobami podsumowania

Dzięki tej operacji mamy interesujący nas wynik i możemy go zaczytać do Excela (rys. 6).

rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba

rys. 6 – Wyciągnięte Min i Max dla kolumny Czas pogrupowane po kolumnach Data i Osoba

Adam Kopeć
Miłośnik Excela
Microsoft MVP

Przefiltrowana lista plików z katalogu i podkatalogów — Power Query #16

W poprzednim wpisie wyciągnęliśmy listę plików z katalogu za pomocą funkcji makr 4.0. Tym razem chcemy to zrobić za pomocą Power Query. Funkcje w porównaniu do Power Query mają jedną zaletę automatycznie się odświeżają. Natomiast zapytania Power Query przeważnie musimy odświeżać ręcznie. Ale w pozostałych kwestiach uważam, że Power Query się lepiej sprawdza do rozwiązania tego problemu m.in. dlatego, że automatycznie sięga do podkatalogów, a z makrami trzeba już pisać bardzo skomplikowany kod VBA.

Starczy wstępu. Znamy już ścieżkę katalogu (C:\Users\PC\Documents\Pliki), więc możemy zacząć budować zapytanie Power Query. W Excelu 2016 przechodzimy do karty Dane -> polecenie Nowe zapytanie -> Z pliku -> Z folderu…

Power Query #16 - Lista plików z katalogu i podkatalogów - 01

Pobieranie danych z folderu za pomocą Power Query

Wklejamy ścieżkę w okno, które się pojawi i naciskamy przycisk OK.

Power Query #16 - Lista plików z katalogu i podkatalogów - 02

Okno ścieżki do folderu

W następnym oknie przechodzimy do Edycji.

Power Query #16 - Lista plików z katalogu i podkatalogów - 03

Podgląd wyników zapytania z folderu

Pierwsza zaleta na korzyść Power Query w porównaniu do funkcji makr 4.0 – od razu widzimy wyniki zapytania i mamy więcej informacji o pliku niż tylko jego nazwa.

Dodatkowo jeśli odnajdziemy kolumnę Folder Path, to zobaczymy, że Power Query wyciągnął również pliki z podkatalogów wskazanego katalogu.

Power Query #16 - Lista plików z katalogu i podkatalogów - 04

Informacje o plikach wyciągnięte przez Power Query

W większości chcemy zaglądać również do podkatalogów wskazanego folderu, ale jeśli ich nie potrzebujesz wystarczy przefiltrować dane po kolumnie Folder Path.

Jeśli chcemy zobaczyć więcej informacji o plikach wystarczy, że rozwiniemy kolumnę Attributes (klikając w dwie strzałki wskazujące w przeciwnych kierunkach w nagłówku tej kolumny).

Power Query #16 - Lista plików z katalogu i podkatalogów - 05

Lista atrybutów pliku możliwa do rozwinięcia

My więcej atrybutów nie potrzebujemy, chcemy ich mieć wręcz mniej, ale to zrobimy na koniec zapytania. Teraz chcemy przefiltrować dane tak, żebyśmy widzieli tylko pliki Excela. Możemy do tego wykorzystać kolumnę Extension, ale musimy pamiętać, że Power Query jest Case Sensitive, czyli, w przeciwieństwie do Excela, zwraca uwagę na wielkość liter. Czasami się zdarza, że niektóre programy zapisują rozszerzenia wielkimi literami, co może się dla nas okazać problemem. Dlatego przed filtrowaniem zamienimy wszystkie litery w kolumnie Extension na małe (karta Przekształć -> polecenie Format -> małe litery)

Power Query #16 - Lista plików z katalogu i podkatalogów - 06

Polecenie zamiany tekstu na małe litery

Teraz możemy już przefiltrować dane po kolumnie Extension, tylko nie chcemy po prostu odhaczać niepasujących rozszerzeń, tylko chcemy przejść do filtrów tekstu – zaczyna się od.

Power Query #16 - Lista plików z katalogu i podkatalogów - 07

Filtry tekstu – zaczyna się od, dla kolumny Extension

W oknie, które się pojawi wpisujemy tekst ‘.xls’, ponieważ interesują nas takie rozszerzenia jak ‘.xls’, ‘.xlsx’, ‘.xlsm’ i podobne związane z Excelem.

Power Query #16 - Lista plików z katalogu i podkatalogów - 08

Okno filtrowania wierszy

Odfiltrowaliśmy nieinteresujące nas pliki, więc teraz wystarczy usunąć wszystkie kolumny poza Name. To bardzo proste, bo wystarczy na nią kliknąć prawym przyciskiem myszy i w podręcznym menu wybrać opcję Usuń inne kolumny.

Power Query #16 - Lista plików z katalogu i podkatalogów - 09

Podręczne menu dla kolumny

Teraz pozostaje nam tylko załadować dane do Excela.

Power Query w porównaniu do funkcji makr 4.0 sięga do podkatalogów, pozwala łatwo odfiltrować niechciane katalogi i daje więcej możliwości jeśli chodzi o filtrowanie plików i ich rozszerzeń. Niestety nie odświeża się automatycznie i trudniej mu zmienić ścieżkę do folderu, gdzie funkcja PLIKI patrzy na wartość w komórce, którą łatwo umiemy zmienić (żeby nakłonić Power Query, żeby pobierał ścieżkę do folderu zobacz mój film/wpisu — Porada #305). Czyli jak to często powtarzam w Excelu, jak w życiu, jest wiele sposobów na uzyskanie tego samego, z jakiego sposobu skorzystasz, zależy głównie od Twoich potrzeb i możliwości 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Podział tekstu ciągiem kolejnych ograniczników separatorów — Power Query #15

W tym poście zajmiemy się dzielenie tekstu za pomocą ciągu kolejnych ograniczników. W naszym przykładzie zakładamy, że najpierw dane chcemy podzielić (stworzyć podział na kolumny), gdy znajdziemy pierwszy średnik, potem przecinek i dalej zgodnie z układem danych (kolejne separatory to średnik, przecinek, pionowa linia i spacja).

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 01

Ważne, że ten podział działa jako ciąg, czyli najpierw znajdowany jest średnik (w tym miejscu wstawiany jest pierwszy podział na kolumny). Później od miejsca znalezionego średnika jest szukany przykładowy przecinek (kolejny podział na kolumny), a potem kolejne znaki, które wstawimy.

Standardowo zaczytujemy dane do Power Query i pozbywamy się kroków związanych ze zmianą typu danych w kolumnach. Kolejnym krokiem będzie wstawienie podziału kolumny po ograniczniku.

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 02

Pierwszy podział chcemy zrobić po średnik najbardziej z lewej strony.

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 03

Oczywiście to nie wszystkie podziały bo w sumie mamy 4 ograniczniki (separatory). Moglibyśmy zrobić analogiczną operację na kolejno rozdzielanych kolumnach, ale możemy zrobić to szybciej w jednym kroku, wystarczy, że mamy włączony pasek formuły w Power Query (karta Widok odpowiedni checkbox). Wtedy widzimy jako formuła (funkcja języka M została użyta do podziału na 2 kolumny po pierwszy ograniczniku (średniku). Została użyta funkcja

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 04

której możemy podać po kolei (w ciągu) kolejne ograniczniki (separatory) i później odpowiednio nadać nazwy kolumną, czyli formuła po zmianach powinna wyglądać tak:

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 05

Zauważ, że w danych specjalnie zostały popełnione błędy. Dlatego podział robi się niepoprawnie, bo Power Query musi znaleźć ograniczniki (separatory) w ciągu (w ustalonej kolejności) jeśli nie znajdzie kolejnego ogranicznika (separatora) z ciągu nie będzie szukał kolejnego, czyli nie wstawi podziału na kolumny i pojawią się puste kolumny (wypełnione wartościami 'null'). Na szczęście jeśli poprawimy dane to nasze zapytanie poprawi wynik po jego odświeżeniu.

Można zauważyć, że w niektórych kolumnach mamy spacja na początku danych. Żeby je usunąć wystarczy dodać spacja jako tekst ogranicznika (ogranicznik/separator może być więcej niż 1 znakiem).

Prawidłowy wynik końcowy (wgrany do Excela) powinien wyglądać tak:

Power Query 15 - Podział tekstu ciągiem kolejnych ograniczników (separatorów) - 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Usuwanie pierszych, ostatnich, naprzemiennych i pustych wierszy — Power Query #14

Dzisiaj chce porozmawiać o usuwaniu niepotrzebnych wierszy w Power Query, bo niedawno musiałem sporo zbędnych informacji (wierszy) usunąć zanim przeszedłem do analizy danych.

Mamy odpowiednio spreparowaną tabelę, która pomoże nam w poznaniu funkcjonalności usuwania wierszy w Power Query. Główne jej założeniem jest, że są to dane eksportowane z naszego głównego programu, który dzieli informacje na strony. Każda strona ma określoną ilość wierszy (15, żeby nie było ich za dużo w przykładzie 😉 ).Na każdej stronie są 2 linijki stopki. Cały dokument na wiersze Tytułu (zaczynające dokument/dane) oraz wiersze z informacjami końcowymi kończące dokument, które są nam zbędne.

Mamy jeszcze kilka pustych wierszy, zduplikowanych wierszy, i wierszy z "błędami".

Power Query 14 - Usuwanie wierszy - 01

Wczytujemy dane z tabeli do Power Query i zaczynamy usuwanie zbędnych wierszy (Na początku mamy 56 wierszy). Rozwijamy polecenie Usuń wiersze na karcie Narzędzia główne i w pierwszej kolejności usuwamy pierwsze wiersze (jest ich 7)

Power Query 14 - Usuwanie wierszy - 02

Otworzy się okno, w które wpisujemy ile pierwszych wierszy chcemy usunąć (7) i zatwierdzamy wybór przyciskiem OK – zostaje nam 49 wierszy.

Power Query 14 - Usuwanie wierszy - 03

Kolejnym krokiem jest usuwanie wierszy sekwencyjnie (naprzemiennie).

Power Query 14 - Usuwanie wierszy - 04

Mogliśmy to zrobić równie dobrze jako pierwszy krok, tylko musielibyśmy odpowiednio policzyć, kiedy chcemy zacząć usuwanie. Patrząc na nasze dane, po tym jak usunęliśmy 7 pierwszych wierszy tytułu, to chcemy zacząć usuwanie od 7 wiersza, usunąć 2 wiersze, a następnie zachować kolejne 13 wierszy (przypominam strona ma 15 linijek).

Power Query 14 - Usuwanie wierszy - 05

Po pierwszym usunięciu wiersze dalej są usuwane sekwencyjnie (naprzemienne), czyli, jak usunęli 2 wiersz od 7 linijki (7 i 8 wiersz) następnie zachowaliśmy 13 wierszy, to jeśli jeszcze nie doszliśmy do ostatniego wiersze, to znowu usuwamy 2 wiersze i zachowujemy 13 i tak aż do końca danych. Zostaje nam 43 wiersze.
Teraz usuwamy ostatnie wiersze.

Power Query 14 - Usuwanie wierszy - 06

Czyli wpisujemy 5 w okno usuwania ostatnich wierszy (zostaje 38).

Power Query 14 - Usuwanie wierszy - 07

Kolejną operacją jaką chcemy wykonać jest usuwanie pustych wierszy, czyli takich wierszy, gdzie we wszystkich wierszach występuje wartość null. Teraz są to wiersze nr 10 i 11. Wiersz 8 ma wartość null tylko w drugiej kolumnie, więc nie zostanie usunięty (zostaje 36 wierszy).

Power Query 14 - Usuwanie wierszy - 08

Jeśli chcielibyśmy usunąć wiersze z wartością null w kolumnie NrWierszy, to najprościej byłoby je odfiltrować i tak najprościej postąpić w sytuacji w której w danej kolumnie są wartości dla których chcemy usunąć całe wiersze (zostaje 35 wierszy).

Power Query 14 - Usuwanie wierszy - 09

Teraz chcemy usunąć błędy, tylko wartości w drugiej kolumnie podpisane jako 'błąd' nie są faktycznie błędem tylko tekstem. Zakładamy, że jeśli drugiej kolumnie występuje tekst, a nie liczba, to ten wiersz chcemy usunąć. Najprościej jest zmienić typ danych dla drugiej kolumny na liczbę (w tym przykładzie całkowitą). Ponieważ tekstów nie da się zamienić na liczbę Power Query w ich miejsce wstawia informację o błędzie (Error) i teraz możemy usunąć błędy. Musimy tylko pamiętać, że ma być zaznaczona kolumna, która zawiera błędy, które chcemy usunąć (zostaje 30 wierszy).

Power Query 14 - Usuwanie wierszy - 10

Została nam ostatnia operacja usuwania duplikatów. Przy niej musimy bacznie zwracać uwagę ile i które wiersze mamy zaznaczone, gdyż wiersz będzie uznany za duplikat, kiedy wcześniej istniał wiersz, który miał takie same wartości we wszystkich zaznaczonych kolumnach.

Jeśli w naszym przykładzie zaznaczymy tylko pierwszą kolumnę (Dane zaimportowane).

Power Query 14 - Usuwanie wierszy - 11

To tylko ta kolumna będzie przy sprawdzaniu duplikatów, więc, jeśli jakaś wartość się w niej powtórzy (wcześniej już był wiersz, który miał taką samą wartość w kolumnie Dane zaimportowane), to zostanie usunięty cały wiersz. Po tym zostałoby nam tylko 15 wierszy (jeśli tak zrobiłeś usuń ostatni krok zapytania).

My szukamy duplikatów po obu wierszach, a identyczne wartości w obu wierszach mamy tylko w wierszach 27 i 28,

Power Query 14 - Usuwanie wierszy - 12

więc usuwamy te duplikaty po obu kolumnach i zostaje nam 29 wierzy. Teraz możemy je załadować do Excela.
P.S. Jak mogliśmy na podane powyżej sposoby usuwać wiersze, możemy je też zachować. Zachowujemy tylko wiersze wskazane. Pozostałe są usuwane.

Power Query 14 - Usuwanie wierszy - 13

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Automatyczne odświeżanie zapytań — Power Query #13

Często chcemy, żeby nasze zapytania odświeżały się automatycznie, albo przynajmniej co jakiś czas. W tym wpisie zobaczysz jakie możliwości są w samych połączeniach/zapytaniach (w poradzie VBA 22 zobaczysz kod VBA do podpięcia pod przycisk, który odświeża wszystkie zapytania).

Pierwszą możliwością jak możesz się dostać do właściwości połączenia jest kliknięcie prawym przycisku na jego wyniku w arkuszu Excela, rozwinięciu w podręcznym menu pozycji Tabela i wybraniu Właściwości danych zewnętrznych.

PQ 13 - Automatyczne odświeżanie zapytań 01

Otworzy się okno właściwości danych zewnętrznych, gdzie możesz zmienić właściwości formatowania danych oraz ich zachowanie podczas odświeżania danych.

PQ 13 - Automatyczne odświeżanie zapytań 02

Możesz w tym oknie kliknąć ikonkę, która Cię przeniesie do okna właściwości połączenia, gdzie znajdują się opcje bardziej nas interesujące.

PQ 13 - Automatyczne odświeżanie zapytań 03

Można tam między innymi zaznaczyć checkboxa, który będzie automatycznie odświeżał zapytania przy otwieraniu pliku lub po określonym czasie.

Do właściwości połączenia możesz dostać się też przez polecenia karty Dane.

PQ 13 - Automatyczne odświeżanie zapytań 04

Tylko, żeby zobaczyć właściwości połączenia, które umożliwiają odświeżanie automatyczne przy otwieraniu pliku (rys. 3) będziesz musiał je jeszcze wybrać z listy po naciśnięciu polecenia Połączenia z karty Dane.

PQ 13 - Automatyczne odświeżanie zapytań 05

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak usunąć grupy wierszy, które kończy konkretny tekst — kolumny indeksów — Power Query #12

Wykonamy ponownie usuwanie z danych niepotrzebnych bloków wierszy jak w PQ 11, tylko tym razem zrobimy to prościej – głównie za pomocą kolumn indeksów i sprawdzaniu prostych warunków.
Przeważnie takie bloki zaczynają się lub kończą konkretnym wierszem (zawierającym konkretną unikalną frazę). W tym wpisie rozpatrzymy sytuację, gdzie znamy wiersz kończący zbędne dla nas informacje w danych oraz wiemy, że blok niepotrzebnych informacji ma zawsze 5 wierszy.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 01

Standardowo pobieramy dane z tabeli Excela do zapytania PowerQuery i kasujemy Zmieniono typ, ponieważ jest nam niepotrzebny w tym przykładzie. Następnie dodajemy kolumnę indeksu (od jakiej wartości zaczynamy liczenie jest drugorzędne).

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 02

Następnie za pomocą kolumny warunkowej (polecenie z karty Dodaj kolumnę) sprawdzamy kiedy pojawia się ostatni wiersz zbędnego bloku, czyli ten, który zawiera tekst – ‘Koniec Niepotrzebnego bloku’. Musimy następująco wypełnić pola w oknie wstawiania kolumny warunkowej:

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 03

Teraz potrzebujemy wypełnić tą kolumnę w górę (polecenie z karty Przekształć).

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 04

Wtedy każda liczba będzie kopiowana w górę, zastępując wartości null, dopóki nie dojdzie do kolejnej liczby. Teraz musimy sprawdzić czy wartość z kolumny Indeks jest większa niż wartość z kolumny Custom (tej przed chwilą wypełnianej w górę) pomniejszona o 5. Możemy to zrobić dodając kolumnę niestandardową (polecenie z karty Dodaj kolumnę). Nazwa kolumny jest nieistotna bo za chwilę ją skasujemy, a formułą prosta 😉

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 05

Teraz wystarczy odfiltrować wiersze z wartościami TRUE.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 06

A następnie usunąć wszystkie kolumny poza kolumną Nagłówek, bo służyły one tylko temu, żeby odfiltrować dla nas zbędne wiersze – wystarczy kliknąć prawym przyciskiem myszy w nagłówek kolumny Nagłówek.

PQ 12 - Jak usuwać grupy wierszy, który kończy konkretny tekst - kolumny indeksów 07

A później możemy już wczytać zapytanie do Excela.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP