W dzisiejszym poście zajmiemy się kontynuacją poprzedniego, a dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Pierwszym krokiem jest zaczytanie danych wejściowych do Power Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

rys. 2 — Z tabeli/ zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z danymi przedstawioną na rysunku nr 3.

rys. 3 — Edytor zapytań

Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. 4 — Usuń inne kolumny

Otrzymamy interesujące nas dane przedstawione na rysunku nr 5.

rys.5 — Dane po usunięciu innych kolumn

Do tego momentu kroki w działaniu były takie same jak w poprzednim poście https://exceliadam.pl/?p=9720 . Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6). Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.

rys. 6 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie w polu Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać następująco:

=Text.Split([Produkt], ", ")

W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek  spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7).

rys. 7 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 8.

rys. 8 — Kolumna niestandardowa w postaci list

Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr 9).

rys. 9 — Dane ukryte pod nazwą List

Kolejnym krokiem jest policzenie elementów każdej z tych list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:

=List.Count(Text.Split([Produkt], ", "))

rys. 10 — zmiany wprowadzone w oknie kolumny niestandardowej

Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy dane przedstawione na rysunku nr 11.

rys. 11 — zmieniona kolumna niestandardowa

Podsumowując najpierw podzieliliśmy dane na Poszczególne listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak na rysunku nr 12.

=List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", ")))

rys. 12 — Formuła funkcji List.Transform

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — Wynik funkcji List.Transform

Otrzymaliśmy listę wszystkich elementów na konkretnych listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję List.Max. Formuła powinna wyglądać jak na rysunku nr 14.

=List.Max(List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", "))))

rys. 14 — Zapis funkcji List.Max

Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik przedstawiony na rysunku nr 15.

rys. 15 Wynik maksymalny

Kolejny krok pokrywa się z krokiem z wcześniejszego filmu, czyli Power Query odcinek 36.

Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.

rys. 16 Dane z kroku Usunięto inne kolumny

Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 17).

rys. 17 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe (punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK.

rys. 18 — parametry dzielenia kolumny

Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy niepotrzebny krok o nazwie Zmieniono typ.

rys. 19 — Podzielone dane

W pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn (podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku

rys.20 zmiana nazw kolumn na odwołanie do poprzedniego kroku w pasku formuły

Klikamy nazwę wcześniejszego kroku — Dodano kolumnę niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku, czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien wyglądać jak na rysunku nr 21.

rys. 21 — Zapis formuły

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 22.

rys. 22 — Dane uzyskane z działania funkcji Table.TransformColumn

Otrzymaliśmy jedenaście kolumn dynamicznych, czyli zmieniających się pod wpływem zmian w danych wejściowych.

Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.

rys. 23 zmiana nazw kroków w pasku formuły

Następnie możemy usunąć już zbędny krok Niestandardowe1. Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys. nr 24).

rys. 24 — Usuwanie kroku

Tak przygotowane dane możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).

rys. 25 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 26)

rys. 26 — Okno importowania danych

Otrzymamy dane zaimportowane do Excela przedstawione na rysunku nr 27.

rys. 27 — Dane zaimportowane do Excela

Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi, aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z podręcznego menu.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.