Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzednich wpisów (PQ 5 i PQ 6)
Rozwiązanie w tym wpisie uwzględnia uwagi użytkownika The Power User (film https://www.youtube.com/watch?v=SOXvcBeXeyA) w komentarzu pod filmem (https://www.youtube.com/watch?v=jr2F-mrE1Uc) na podstawie, którego powstała seria 3 wpisów (PQ 5, PQ 6 i PQ 7). To rozwiązanie jest najbardziej skomplikowane, ale pozwala Ci się na wyższy poziom znajomości PowerQuery i języka M 😉
Standardowo nasze dane przechowywane są jako tabela, więc zaczytujemy je za pomocą polecenia Z tabeli (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).
W naszym zapytaniu nie potrzebujemy domyślnego kroku Zmieniania typów, więc możemy go usunąć klikając na czerwony x obok niego.
W pierwszym kroku będziemy potrzebowali dodać kolumnę (polecenie Kolumna niestandardowa z karty Dodaj kolumnę) łączącą wartości z dwóch pierwszych kolumn (Województwo i Miasto), czyli kolumn wspólnych dla poszczególnych zestawów danych (rekordów).
Formuła jest analogiczna jak w Excelu (nie jest istotna dla nas nazwa kolumny, która powstanie) i wykorzystuje symbol ampersand '&'.
=[Województwo]&";"&[Miasto]
Jak mamy połączone wartości z 2 pierwszych kolumn to ich nie potrzebujemy, więc je kasujemy, a następnie przeciągamy stworzoną przez nas kolumnę z formułę na początek danych.
Po tych krokach wykonujemy transponowania danych (polecenie z karty Przekształć), czyli zamieniamy miejscami wiersze z kolumnami.
W wyniku nasza pierwsza kolumna stanie się pierwszym wierszem i przechowuje całkiem inny zestaw danych. Dlatego potrzebujemy ją ochronić – najprostszym na to sposobem jest przeniesieniem ją do nagłówków (polecenie Użyj pierwszego wiersza jako nagłówków z karty Przekształć).
Nasze zestawy danych mają po dwa wiersze (wcześniej kolumny) i potrzebujemy je zidentyfikować, a następnie połączyć. Zaczniemy od tego, że dodamy kolumnę z indeksem od zera (polecenie z karty Dodaj kolumnę).
Tylko my nie potrzebujemy numeracji wierszy od początku danych, ale numeracji wierszy danych w grupach, czyli pierwszy wiersz będzie miał numer 0, drugi 1, trzeci 0 itd. Wykorzystamy do tego funkcję Modulo (po 2), którą znajdziemy na karcie Dodaj kolumnę, gdy będzie zaznaczona kolumna z indeksem.
Wartość dla Modulo wpisujemy jako 2 i uzyskujmy numerowanie wierszy jakiego potrzebujemy, ale potrzebujemy jeszcze kolumny, która określi pogrupuje nam grupy danych – chodzi o to, żeby wiersze z tej samej grupy danych miały taki sam numer (nie koniecznie w kolejności).
My zrobimy to za pomocą Kolumny warunkowej (karta Dodaj kolumnę).
Nazwijmy ją sobie L.p. i chcemy ją uzależnić od kolumny Wstawiono modulo. Jeśli wartość w tej kolumnie będzie równa zero to chcemy zwracać wartość z kolumny Indeks, jeśli wartość modulo będzie inna (1), to wtedy chcemy Indeks pomniejszony o jeden. Nie możemy dla kolumny warunkowej przypisać od razu wartości indeks minus jeden, ale możemy wybrać wartość z kolumny Indeks…
, a później odjąć jedynkę w stworzonej formule. Musi być zaznaczone pole wyboru (checbox) Pasek formuły na karcie Widok. Wtedy wystarczy w nim dopisać '-1' na koniec formuły przez zamykającym nawiasem.
= Table.AddColumn(#"Wstawiono modulo", "L.p.", each if [Wstawiono modulo] = 0 then [Indeks] else [Indeks]-1)
Nie będzie już nam potrzebna kolumna Indeks więc możemy ją usunąć.
Zaznaczamy teraz kolumny Modulo i L.p. i anulujemy przestawienie innych kolumn (np. dzięki poleceniu z podręcznego menu po kliknięciu prawym przyciskiem na zaznaczone kolumny).
Teraz czeka nas trudniejsze zadania, bo w dwóch kolejnych krokach potrzebujemy mieć dwa różne filtry na kolumnę Wstawiono modulo. W pierwszym chcemy widzieć zera, a w drugim jedynki.
Stworzenie pierwszego z tych kroków jest banalne, bo wystarczy przefiltrować odpowiednio dane po kolumnie Wstawiono modulo. To stworzenie kolejnego kroku sprawia trudność.
Mamy już widoczny pasek formuły, więc kopiujemy formułę dla pierwszego filtrowania.
= Table.SelectRows(#"Anulowano przestawienie innych kolumn", each ([Wstawiono modulo] = 0))
Widzimy dokładnie ile ma się równać wartość w kolumnie modulo. Potrzebujemy niemal identycznej formuły tylko musimy zamienić 0 na 1.
Zaczniemy od tego, że ustawiamy się na ostatnim kroku zapytania (filtrze po zerze) i klikamy w symbol fx znajdujący się obok paska z formułą.
Gdy doda się nowy krok do paska formuły wklejamy skopiowaną formułę na przefiltrowanie wierszy tylko zamieniamy 0 na 1.
Mamy teraz 2 kroki z różnymi wynikami filtrowania. Potrzebujemy im zmienić nazwę, żeby łatwiej je później zidentyfikować. Klikamy na nazwę kroku, a następnie naciskamy klawisz F2, żebyśmy mogli zmienić nazwy na np. DataSet1 i DataSet2.
Uff. Ten krok zdecydowanie wykracza ponad rozwiązania, które stosujemy na co dzień.
Teraz potrzebujemy scalić zapytania (polecenie na karcie Narzędzia główne).
Będziemy chcieli połączyć nasze z zapytanie z naszym zapytanie (bieżącym zapytaniem). Dokładnie tak dobrze przeczytałeś, chcemy scalić ze sobą to samo zapytanie. Trzymając klawisz Ctrl zaznaczamy u góry i u dołu kolumny L.p. i Atrybut (przechowuje wartości z nagłówka kolumn, te które wcześniej łączyliśmy). Jako rodzaj sprzężenia wybieramy wewnętrzne i klikamy przycisk OK.
Do zapytania doda się Nowa kolumna z wartościami typu Table. Żeby zobaczyć co się pod nimi kryje wystarczy kliknąć na dowolną komórkę i na dole edytora zobaczymy wartości (tabelę) kryjącą się w zaznaczonej komórce.
W tych komórkach kryją się identyczne wiersze tabeli zapytania. To dlatego, że jeszcze nie zmodyfikowaliśmy formuły kroku scalania. Teraz PowerQuery łączy ostatni krok zapytania (DataSet2) z ostatnim krokiem zapytania.
= Table.NestedJoin(DataSet2,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)
Musimy zmodyfikować formułę, żeby jedno z zapytań odwoływało się do wcześniejszego kroku (DataSet1), czyli wystarcz, że zmienimy jedną cyfrę 😉
Załóżmy, że zmiany dokonamy w pierwszy odwołaniu, bo najpierw chcemy mieć wiersz z nazwą sklepu, a później ulicą na której jest.
= Table.NestedJoin(DataSet1,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)
Po zmianie kodu formuły możemy rozwinąć (nie agregować) kolumnę Nowa kolumna klikając na strzałki wskazujące w przeciwnym kierunku (patrz nagłówek kolumny). Potrzebujemy tylko kolumny Wartość i nie chcemy, żeby nadana jej nazwa używała prefiksu kolumny, z której ją rozwijamy.
Jesteśmy już prawie na końcu drogi 😀 Wystarczy, że usuniemy niepotrzebne już kolumny Wstawiono modulo i L.p.
Następnie musimy podzielić kolumnę Atrybut (tą z nagłówków danych, która przechowuje informacje o Województwie i Mieście) po ograniczniku (którym jest średnik) na kolumny (polecenie z karty Narzędzia główne).
Na koniec zostanie tylko zmiana nagłówków kolumn i możemy załadować nasze zapytanie do Excela. Jak z poprzednimi zapytaniami z serii nie ma problemu gdy dodajemy nowe wiersze danych do oryginalnej tabeli. Jest problemy, gdy dodamy nowe kolumny.
Żeby poprawić zapytanie musimy zmodyfikować 4 krok zapytania, gdzie zmieniamy kolejność kolumn (kolumnę z połączonym Województwem i Miastem przesuwamy na początek danych). Kolumny układają się w nieprawidłowej kolejności i musimy poprawić ten krok.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
