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