Łączenie kolumn z danymi – Excel PowerQuery #2

Mamy problem, gdyż nasze dane rozdzielone są na wiele kolumn przechowujących te same informacje.

PowerQuery 2 - Łączenie kolumn z danymi 01

Takie dane bardzo trudno analizować, więc potrzebujemy je złączyć w pojedyncze kolumn. Wykorzystamy rozwiązanie Oza du Soleil, które opiera się o użycie dodatku do PowerQuery do Excela.

https://www.youtube.com/watch?v=hW9eVTbRbgI

Najpierw będziemy musieli zrobić pewną operację na danych – będziemy musieli połączyć informacje z dwóch kolumn do jednej. Tylko potrzebujemy dodać pomiędzy nimi ogranicznik, po którym później będziemy mogli podzielić je ponownie. Ważne, żebyśmy byli pewni, że ten ogranicznik nie występuje w naszych danych. Mogą to być np.: 2 symbole ^^

=A3&”^^”&B3

PowerQuery 2 - Łączenie kolumn z danymi 02

Następnie oprócz komórki z formułą zaznaczamy pustą komórkę po prawej stronie i przeciągamy je do końca danych. Następnie tak stworzony wiersz kopiujemy w dół do końca wszystkich danych. Musimy jeszcze dodać (skopiować) do naszych danych nagłówek, ponieważ dane do PowerQuery w Excelu należy pobierać z tabeli Excela.

PowerQuery 2 - Łączenie kolumn z danymi 03

Teraz możemy pobrać dane do zapytania PowerQuery. Korzystając z odpowiedniego polecenia z karty dodatku lub z karty Dane od Excela 2016.

PowerQuery 2 - Łączenie kolumn z danymi 04

Po wczytaniu danych do PowerQuery musimy wykonać następujące kroki:
1. Musimy przenieść nagłówki do pierwszego wiersza

PowerQuery 2 - Łączenie kolumn z danymi 05

2. Transponować dane

PowerQuery 2 - Łączenie kolumn z danymi 06

3. Odfiltrować wszystkie wiersze, które zawierają słowo Ilość (w naszym przykładzie), czyli te wiersze, które dla których kolumny były puste. Ważne, że nie możemy po prostu odznaczyć ich w filtrze, tylko musimy skorzystać z filtrów tekstowych i wybrać kryterium nie zawiera.

PowerQuery 2 - Łączenie kolumn z danymi 07

Ważne, że PowerQuery zwraca uwagę na wielkość liter, więc tutaj musimy odpowiednio wypełnić pole zgodnie z zapisanym przez nas tekstem.

4. Anulujemy przestawienie wszystkich kolumn poza pierwszą

PowerQuery 2 - Łączenie kolumn z danymi 08

5. Usuwamy środkową kolumnę, bo jej nie potrzebujemy

PowerQuery 2 - Łączenie kolumn z danymi 09

6. Dzielimy kolumnę po użytym przez nas ograniczniku

PowerQuery 2 - Łączenie kolumn z danymi 10

7. Odfiltrowujemy po jednej z kolumn wynikowych wiersze puste lub z wartością null. Możemy tak teraz postąpić, gdyż PowerQuery zapisze to jako filtr wartości różne od pustych (null).

PowerQuery 2 - Łączenie kolumn z danymi 11

8. Na koniec zmieniamy nazwy kolumn i wczytujemy dane do Excela.

PowerQuery 2 - Łączenie kolumn z danymi 12

Trzeba zwrócić uwagę, że wartości w pierwszej kolumnie nie są takie jakbyśmy chcieli ponieważ w pierwszym kroku skopiowaliśmy nagłówki bezpośrednio skopiowane z danych, które przekształcamy, a one nie przechowują dodatkowej informacji o produktach. W tym przykładzie powinniśmy zamiast nich wstawić jako nagłówki dane naszych sprzedawców, a następnie odświeżyć tabelę wynikową zapytania PowerQuery.

PowerQuery 2 - Łączenie kolumn z danymi 13

Niestety spowoduje to błąd :(, ale klikając dwukrotnie w oknie zapytań wrócimy do edycji zapytania i będziemy mogli je poprawić. Przy okazji PowerQuery podpowiada nam, gdzie wystąpił błąd.

PowerQuery 2 - Łączenie kolumn z danymi 14

Błędny okazuje się drugi krok ponieważ w pobieranych danych nie istnieje już kolumna Produkt itp. Najprostszym rozwiązaniem jest tutaj usunięcie tego kroku ponieważ mamy proste dane i jego usunięcie nie wpłynie negatywnie na wynik końcowy.

PowerQuery 2 - Łączenie kolumn z danymi 15

Teraz wystarczy ponownie załadować dane zapytania i w naszej tabeli wynikowej zapytania PowerQuery mamy wszystkie istotne dla nas dane.

PowerQuery 2 - Łączenie kolumn z danymi 16

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

Zmiana ludzkiej tabelki na bardziej bazodanową – porada #280

Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-01

Na podstawie danych nie da się stworzyć Tabeli Przestawnej i innych analiz danych dostępnych w Excelu. Trzeba je najpierw przekształcić .

Niedawno tego samego dnia dwóch ekspertów od Excela zamieściło filmy, w których znalazło się również rozwiązanie mojego problemu za pomocą Power Query.

Oz du Solei https://www.youtube.com/watch?v=EM15idCJXXU
Mike Girvin https://www.youtube.com/watch?v=_csX8sCzJd0

Więc jeśli masz taki sam problem jak ja zobacz jak go możesz rozwiązać za pomocą PowerQuery (jeśli nie możesz zainstalować u siebie tego dodatku do Excela zobacz porada 281, gdzie opisuję, jak to robię za pomocą formuł.
Pierwszą rzeczą, którą musimy zrobić to zamienić nasz zakres danych na tabelę, ale odznaczamy, że nasza tabela ma nagłówki. Ułatwi nam to później operacje.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-02

Zwróć uwagę, że miesiące były wpisywane w scalonych komórkach, a teraz się rozdzieliły. W odpowiednim kroku szybko to naprawimy. Najpierw musimy wczytać naszą tabelę do Power Query. Ponieważ mam w końcu Excel 2016, to robię to z karty dane (wcześniej musiałem instalować dodatek i korzystać z karty Power Query).

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-04

Naszym danym nie jest potrzebna zmiana rodzaju danych, więc możemy ten krok usunąć.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-03

Kolejnym krokiem będzie transponowanie danych.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-05

Następnie możemy wykorzystać pierwszy wiersz danych jako nagłówki.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-06

Kolejny krok to wypełnienie w dół kolumny miesiące, czyli wypełnianie pustych komórek wartościami, które znajdują się nad nimi (w pewnym momencie musi znaleźć się wypełniony wiersz ;))

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-08

Następnie musimy zaznaczyć 2 pierwsze kolumny i anulować przestawienie pozostałych kolumn.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-07

Pozostaje jeszcze zmiana nazw kolumn (wystarczy, że klikniesz w nią dwukrotnie), żeby bardziej odpowiadały danym i już możesz je załadować do nowego arkusza Excela.
porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-09

Dla naszych przykładowych danych powstało 720 wierszy, na podstawie których możesz już bez problemu stworzyć Tabelę Przestawną lub inaczej je analizować.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Podział kolumny na wiersze – Excel PowerQuery #1

Dostałeś polecenie podziału kolumny na wiersze. Ale o co dokładnie chodzi?

W przykładowych danych możesz zobaczyć w pierwszej kolumnie imię Pracownika, a w drugiej, rozdzielone przecinkami, ID Klientów, z którymi współpracuje/których obsługuje.

powerquery-1-podzial-kolumny-na-wiersze-01

Dane takie są bardzo trudne do analizy, dlatego potrzeba je podzielić tak jak to widać w tabeli obok, czyli dla każdego numeru ID Klienta ma być osobny wiersz i przypisany mu odpowiedni pracownik.

Najprościej to zrobić za pomocą PowerQuery. Najpierw załaduj dane z tabeli do zapytania PowerQuery (polecenie z tabeli).

powerquery-1-podzial-kolumny-na-wiersze-02

Kolejnym krokiem, już w zapytaniu PowerQuery, będzie podział kolumny ID Klienta po przecinku.

powerquery-1-podzial-kolumny-na-wiersze-03

Tu musisz wybrać ogranicznik, czyli przecinek i w tym przykładzie podział ma nastąpić przy każdym jego wystąpieniu.

powerquery-1-podzial-kolumny-na-wiersze-04

Gdy już dokonałeś podziału na kolumny, nie ma tu znaczenia, że część danych jest interpretowana jako liczby, a część jako tekst, to możesz podzielić kolumny na wiersze, czyli klikasz prawym przyciskiem myszy na nazwę pierwszej kolumny i z podręcznego menu wybierasz polecenie Anuluj przestawienie innych kolumn.

powerquery-1-podzial-kolumny-na-wiersze-05

Teraz jesteś już na finiszu – wystarczy usunąć zbędną kolumnę.

powerquery-1-podzial-kolumny-na-wiersze-06

Zmienić nazwę drugiej kolumny na ID Klienta i jeszcze ewentualnie posortować dane. Pamiętaj, że w PowerQuery kolejność sortowania jest odwrotna niż w Excelu, czyli najpierw sortujesz najważniejszą kolumnę, a potem kolejne.
Po tych kosmetycznych zmianach możesz już Zamknąć i załadować zapytania do Excela:

powerquery-1-podzial-kolumny-na-wiersze-07

Wybieramy opcję poniżej, żeby móc wybrać miejsce docelowe:

powerquery-1-podzial-kolumny-na-wiersze-08

Czekasz chwilę, żeby dane załadowały się do Excela i już masz wykonaną całą operację.

powerquery-1-podzial-kolumny-na-wiersze-09

Teraz jak zmienią się dane wystarczy, że odświeżysz zapytanie PowerQuery i odpowiednio zmieni się wynik. Uważaj tylko jeśli podepniesz więcej klientów pod jednego pracownika, niż było Twoje wcześniejsze maksimum. Zapytanie PowerQuery może ograniczyć się do podziału tylko do wcześniejszego maksimum, co zmusi Cię do usunięcia poszczególnych kroków zapytania i wykonaniu ich ponownie, ale znasz już całą ścieżkę 😉

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