Power Query 37 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Funkcje języka M

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.

Power Query 36 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Interfejs użytkownika

W dzisiejszym poście nauczymy się jak w Power Query stworzyć dynamiczną ilość kolumn przy podziale kolumny według ogranicznika. Rozwiązanie tego problemu zostało przedstawione na rysunku nr 1.

rys. nr 1 — Rozwiązanie zadania

W Power Query często pojawia się problem, kiedy dzielimy dane na kolumny według ogranicznika, to w zależności ile tych produktów było rozdzielonych tym ogranicznikiem, Power Query mógł się nie odświeżyć poprawnie. Power Query zapisuje konkretną ilość kolumn powstałych w kroku Podziel kolumnę według ogranicznika i kiedy dopiszemy więcej danych, nie pojawią się nowe kolumny. Rozwiązanie, które przedstawimy wykorzystuje interfejs użytkownika. W pliku do pobrania możecie znaleźć trzy rozwiązania. Pierwsze, którego nie będę omawiał, bo przy większej ilości danych (powyżej 10) Power Query się gubił. Drugie rozwiązanie podpowiedziane przez Billa Szysza, z wykorzystaniem interfejsu użytkownika oraz trzecie dla bardziej zaawansowanych użytkowników, wykorzystujące język M.

Temat ten omówimy na podstawie przykładowych danych z rysunku nr 2.

rys. nr 2 — Przykładowe dane

Wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 3).

rys. nr 3 — Z tabeli

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą danych. Usuwamy krok Zmieniono typ z Zastosowanych kroków. Interesuje nas tylko kolumna Produkt, więc klikamy na jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. nr 4 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 5.

rys. nr 5 — Dane po usunięciu innych kolumn

Do prawidłowego działania zapytania przy użyciu interfejsu musimy dodać Kolumnę indeksu z karty Dodaj kolumnę (rys. nr 6).

rys. nr 6 — kolumna indeksu

Bez znaczenia czy jest indeksowana od wartości 0 czy 1, ważne aby w każdym wierszu były inne liczby. Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — Dane z kolumną indeksu

W kolejnym etapie dzielimy naszą kolumnę Produkt po ograniczniku. Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 8) z karty Przekształć i z listy wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 8).

rys. nr 8 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry podziału. Wybieramy rodzaj ogranicznika — przecinek, następnie, kiedy dzielimy dane, czyli w naszym przykładzie przy każdym wystąpieniu ogranicznika. Następnie w opcjach zaawansowanych zaznaczamy podział na wiersze. Podział na kolumny ma ustawianą ilość kolumn na 11 ponieważ tyle najwięcej kolumny powstanie dla naszych danych, więc gdy dodamy większą ilość produktów Power Query ich nie pokaże, bo zapisze sobie w pamięci podział na 11 kolumn. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 — Dzielenie kolumny według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — Podzielone dane

Kolumna indeks odpowiednio się zduplikowała. Mamy w niej informacje, z którego wiersza pochodzą poszczególne dane. W kolejnym etapie możemy zrobić grupowanie po indeksie, żeby policzyć, ile było elementów w poszczególnych pozycjach w danych wejściowych. Wybieramy polecenie Grupowanie według z karty Przekształć (rys. nr 11).

rys. 11 — Grupowanie według

Otworzy nam się okno Grupowania według, w którym musimy określić jego parametry. W polu Grupowania według wybieramy kolumnę Indeks, Wpisujemy nazwę nowej kolumny (Liczność) i wybieramy operację jaka ma zostać wykonana, czyli w naszym przykładzie Zlicz wiersze. Zatwierdzamy przyjęte założenia przyciskiem OK (rys. nr 12).

rys. 12 — Parametry grupowania danych

Otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — pogrupowane dane

W powyższych wynikach interesuje nas maksymalna wartość z kolumny Liczność. Zaznaczamy kolumnę Liczność i rozwijamy polecenie Statystyka (punkt nr 2 na rysunku nr 14) z karty Przekształć, a następnie wybieramy polecenie Maksimum (punkt nr 3 na rysunku nr 14).

rys. nr 14 — Maksimum

Power Query wyciągnie nam, jako wynik, maksymalną wartość i otrzymamy daną przedstawioną na rysunku nr 15.

rys. 15 — Maksymalny wynik

Przechodzimy do kroku Usunięto inne kolumny w Zastosowanych krokach, czyli do danych przedstawionych na rysunku nr 5 wyżej, następnie kopiujemy po wciśnięciu klawisza F2 nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Dołożymy teraz nowy krok w działaniu – zmienimy formułę w pasku formuły, a dokładniej zastąpimy nazwę kroku Obliczona wartość maksymalna na tą, którą skopiowaliśmy wcześniej, czyli Usunięto inne kolumny (rys. nr 16).

rys. 16 — Pasek formuły

Zmianę tą zatwierdzamy przyciskiem Enter i otrzymamy dane przedstawione na rysunku nr 17.

rys. 17

W kroku Niestandardowe1 mamy kopię kroku Usunięto inne kolumny. Na tym etapie rozwijamy polecenie Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (rys. nr 18).

rys. 18 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika. W polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe, a następnie wpisujemy ogranicznik przecinek i spacja (, ). Na liście Podziel przy wybieramy opcję Każde wystąpienie ogranicznika. Następnie w opcjach zaawansowanych wybieramy podział na kolumny i wpisujemy ilość kolumn, na która zostanie podzielona kolumna jako wartość 2. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 19).

rys.19 — Parametry dzielenia według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 20. Usuwamy krok Zmieniono typ z zastosowanych kroków.

rys. 20 — Podzielone dane

Gdybyśmy mieli wpisaną wartość domyślną 11, jako liczbę kolumn, na która podzielić kolumny na rysunku nr 19, to Power Query w pasku formuły wypisałby 11 nazw (rys. nr 21).

rys.21 Ilość nazw kolumn wyznaczona na pasku formuły

Użyliśmy tutaj funkcji Table.SplitColumn, więc kiedy podejrzymy sobie jej działanie tej funkcji otrzymamy między innymi informacje przedstawione na rysunku nr 22.

rys. 22 — Table.SplitColumn

Interesuje nas opcja ColumnNamesOrNumbers, czyli liczba kolumn bądź ich nazwy. W pasku formuły zamiast nazw kolumn (podświetlone na rysunku nr 23) wpisujemy wartość 11.

rys. nr 23 — Pasek formuły

Zatwierdzamy zmiany w formule przyciskiem Enter i otrzymujemy dane przedstawione na rysunku nr 24.

rys. 24 — Dane

Nie chcemy, aby wartość 11 była wpisana na stałe. Chcemy aby Power Query pobierał nam tą wartość z poprzedniego kroku, czyli z kroku Obliczona wartość maksymalna. W tym celu kopiujemy (po wciśnięciu klawisza F2) nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C. Teraz zamiast wartości 11 w pasku formuły wklejamy nazwę skopiowanego kroku poprzedzoną znakiem # (hash) i w cudzysłowie (rys. nr 25).

rys. 25 — Zmiany w pasku formuły

Po zatwierdzeniu formuły klawiszem Enter, otrzymujemy dane przedstawione na rysunku nr 26.

rys. 26 — Końcowe dane

Tak przygotowane dane możemy zaimportować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 27).

rys. 27 — 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 28)

rys. 28 — Okno importowania danych

Otrzymamy dane przedstawione na rysunku nr 29.

rys. 29 Dane zaimportowane do Excela

Jeśli w danych wejściowych w wierszu nr 5, który rozpatrujemy, dopiszemy sobie dodatkowy produkt, a następnie odświeżymy nasze dane z Power Query, otrzymamy powiększoną tabelę o ten dopisany produkt. Tabela wczytana z Power Query jest tabelą dynamiczną, która reaguje po odświeżeniu na wprowadzane zmiany w danych bazowych. To rozwiązanie wykonaliśmy za pomocą interfejsu, natomiast w kolejnym filmie skorzystamy z kodu M (rozwiązanie podpowiedziane przez Billa Szysza).


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.

Power Query 35 — Liczba arkuszy w pliku, ukryte i widoczne arkusze

W dzisiejszym poście policzymy liczbę arkuszy w pliku z podziałem na arkusze ukryte,  widoczne i wszystkie. W odcinku VBA #24 (
https://exceliadam.pl/wp-admin/post.php?post=9634&action=edit ) wykonaliśmy to zadanie w VBA. W tej poradzie policzymy arkusze za pomocą dodatku do Excela – Power Query. Przykładowe wyniki naszej operacji możemy zobaczyć na rysunku nr 1.

rys. nr 1

Rozwijamy polecenie Pobierz dane (punkt nr 2 na rysunku nr 2) z karty Dane (grupa poleceń Pobieranie i przekształcanie danych), następnie rozwijamy polecenie Z pliku (punkt nr 3) i wybieramy polecenie Ze skoroszytu (punkt nr 4).

rys. nr 2 — Ze skoroszytu

Otworzy nam się okno Importowania danych, gdzie musimy odszukać nasz plik (odpowiedni dla tego postu). Wybieramy plik i zatwierdzamy nasz wybór przyciskiem Importuj (rys. nr 3).

rys. nr 3 — Importowanie danych

Otworzy nam się okno Nawigatora, gdzie wybieramy dowolny arkusz z pliku, a następnie zatwierdzamy wybór przyciskiem Edytuj (rys. nr 4). Ten krok nie do końca jest właściwy, ale ze względu na to iż korzystamy z interfejsu, ułatwia nam pracę.

rys. nr 4 — Nawigator

Otworzy nam się okno Edytora Power Query z pustym arkuszem, który wybraliśmy. Nie potrzebujemy tutaj żadnych kroków, więc w panelu bocznym Ustawień zapytania usuwamy z Zastosowanych kroków kroki: Zmieniono typ oraz Nawigacja (oznaczone strzałką na rysunku nr 5).

rys. nr 5 — edytor zapytań

My potrzebujemy zachować tylko jeden krok, mianowicie Źródło. Otrzymamy wtedy dane przedstawione na rysunku nr 6.

rys. nr 6 — Dane w edytorze zapytań

Power Query sam napisał ten krok, nie musiałem tego robić sam, ponieważ jest on skomplikowany. Widać na pasku formuły cała ścieżkę dostępu do tego pliku (rys. nr 7) oraz funkcję jaka jest potrzebna do wczytania pliku Excela. Power Query wyciągnął również elementy jakie zawiera dany skoroszyt.

rys. nr 7 — Pasek formuły

Nasz plik Power Query jest plikiem "prostym", ponieważ w kolumnie Kind ma elementy tylko jednego rodzaju – Sheet czyli arkusz. Gdyby nasz plik zawierał tabele lub oznaczone zakresy to one również pojawiły by się w tej tabeli.

Gdybyśmy mieli więcej rodzajów elementów w kolumnie Kind to moglibyśmy przefiltrować dane, żeby otrzymać tylko te które nas interesują. Rozwijamy ikonę trójkącika przy nazwie kolumny Kind i z podręcznego menu rozwijamy polecenie Filtry tekstu a następnie wybieramy polecenie Równa się (rys. nr 8).

rys. nr 8 — Filtry tekstu

Otworzy nam się okno Filtrowania wierszy, gdzie wpisujemy, że chcemy zachować wiersze, w których Kind równa się Sheet. Tak wpisany parametr zatwierdzamy przyciskiem OK (rys. nr 9).

rys. nr 9 — Filtrowanie wierszy

Nałożyliśmy dodatkowy filtr na wypadek gdyby w naszych danych pojawiły się nowe elementy takie jak np. tabela. Na rysunku nr 6 w kolumnie Hidden mamy zawartą informację, czy dany arkusz jest widoczny czy ukryty. False dla widocznego arkusza, natomiast True dla arkusza ukrytego.

Aby na podstawie takiej tabelki zliczyć ilość wszystkich arkuszy wybieramy polecenie Zlicz wiersze na karcie Przekształć (rys. nr 10).

rys. nr 10 — Zlicz wiersze

Otrzymamy wynik przedstawiony na rysunku nr 11. Ta postać wyniku nie jest zbyt użyteczna do dalszych działań.

rys. nr 11

Chcemy później wykonać proste operacje więc wystarczy te dane przekształcić do postaci tabeli. W tym celu wybieramy polecenie Do tabeli z karty Przekształć (rys. nr 12).

rys. nr 12 — Do tabeli

Otrzymamy poprzedni wynik w formie tabelarycznej, co ułatwi nam kolejne działania na tym wyniku (rys. nr 13).

rys. nr 13 — Dane

W kolejnym etapie chcemy policzyć arkusze ukryte i widoczne. W tym celu musimy zduplikować nasze zapytanie. Klikamy prawym przyciskiem myszy na nazwę zapytania PQ35 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 14).

rys. nr 14 — Duplikuj

W zduplikowanym zapytaniu pousuwać Zastosowane kroki do momentu Przefiltrowano wiersze, ponieważ właśnie w tym kroku mamy informacje o ukrytych i widocznych arkuszach. Otrzymamy dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane

Najłatwiej policzyć ilość poszczególnych arkuszy grupując dane. W tym celu wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 16).

rys. nr 16 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie możemy ustawić parametry grupowania danych. Zmieniamy nazwę nowej kolumny na Liczba arkuszy, wybieramy rodzaj operacji jako Zlicz wiersze i tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 — Parametry grupowania

Otrzymamy zliczone arkusze ukryte i widoczne przedstawione na rysunku nr 18.

rys. nr 18 — Pogrupowane dane

W kolejnym etapie chcemy zmienić nazwy elementów z Hidden, FALSE i TRUE na widoczne i ukryte. Najprostszym sposobem jest dodanie nowej kolumny. Zaznaczamy kolumnę Hidden i wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 19).

rys. nr 19 — kolumna warunkowa

Otworzy nam się okno Dodawania kolumny warunkowej. W punkcie oznaczonym na rysunku nr 20 numerem 1 zmieniamy nazwę nowej kolumny na Typ arkuszy, następnie w polu Nazwa kolumny wybieramy Hidden (punkt nr 2). Operator ustawiamy jako Równa się (punkt nr 3). W polu Wartość wpisujemy FALSE (punkt nr 4). Jeśli warunek się zgadza (ma wartość FALSE) to chcemy otrzymać Widoczny (wartość wyjściowa – punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość Ukryty (punkt nr 6). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 20 — Dodawanie kolumny warunkowej

Otrzymamy dane, w których usuwamy niepotrzebną kolumnę Hidden, klikając prawym przyciskiem myszy na tytuł kolumny i wybierając z podręcznego menu polecenie Usuń. Ponadto przenosimy kolumnę Typ arkusza przed kolumnę Liczba arkuszy. Uzyskamy postać danych przedstawioną na rysunku nr 21.

rys. nr 21

W zapytaniu PQ35 mamy podaną liczbę wszystkich arkuszy, natomiast w zapytaniu PQ35 (2) mamy liczbę arkuszy widocznych i ukrytych. W Power Query nie mamy standardowego pojęcia Sumy jak w Excelu, więc musimy połączyć oba zapytania. W tym celu rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 22) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3).

rys. nr 22 — Dołącz zapytania

Otworzy nam się okno Dołączania, gdzie wybieramy ilość tabel, jakie chcemy połączyć. A następnie wybieramy tabelę do dołączenia, czyli PQ35 (rys. nr 23). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 23 — Dołączanie

Otrzymamy dane przedstawione na rysunku nr 24.

rys. nr 24

Przy dołączaniu pojawia się problem, polegający na tym, że zapytanie PQ35 zawierało tylko jedną kolumnę, brakuje kolumny Typ arkusza. Pierwszym sposobem rozwiązania tego problemu jest zmiana wartości null na Wszystkie, w tym celu wybieramy ikonkę oznaczoną strzałką na rysunku nr 25 w karcie Przekształć.

rys. nr 25 ikonka na karcie Przekształć

Otworzy nam się okno Zamieniania wartości, gdzie możemy zmienić wartość null na dowolną jaką wpiszemy, w naszym przykładzie Wszystkie (rys. nr 26). Zmianę tą zatwierdzamy przyciskiem OK.

rys. nr 26 — Zamienianie wartości

Otrzymamy dane przedstawione na rysunku nr 27.

rys. nr 27

Drugim sposobem rozwiązania tego problemu jest dodanie nowej kolumny w zapytaniu PQ35. W tym celu wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 28).

rys. nr 28 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie zmieniamy jej nazwę na Typ arkusza, a następnie w polu Formuła kolumny niestandardowej wpisujemy stałą wartość "Wszystkie". Tak ustawione parametry kolumny zatwierdzamy przyciskiem OK (rys. nr 29).

rys. nr 29 — dodawanie kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 30.

rys. nr 30

Po przejściu na zapytanie PQ 35 (2) otrzymamy zaktualizowane dane przedstawione na rysunku nr 31.

rys. nr 31

Nie ma znaczenia kolejność kolumn w zapytaniu które dołączamy, ponieważ o tej kolejności decyduje nasze główne zapytanie. Tak przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 32).

rys. nr 32 — Zamknij i załaduj do

Otworzy nam się okno Importowanie danych. Okienko to może wyglądać różnie w zależności jaka aktualizację Power Query posiadamy. Ustawiamy sposób wyświetlania danych jako Tabela. Mamy dwa zapytania dlatego nie możemy podać konkretnej lokalizacji. Wybieramy Nowy arkusz i zatwierdzamy przyciskiem OK. (rys. nr 33).

rys. nr 33 — Importowanie danych

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 34 .

rys. nr 34 — Dane w Excelu

Co jest ważne stworzyliśmy właśnie nowe arkusze, więc kiedy odświeżymy nasze dane, otrzymamy zaktualizowane wyniki z dodatkowymi arkuszami przedstawione na rysunku nr 35.

rys. nr 35

Należy bezwzględnie pamiętać o zapisaniu zmian w pliku przed odświeżeniem zapytania. Nie chcemy mieć poszczególnych wyników z zapytań. Interesuje nas tylko wynik sumaryczny z zapytania PQ35 (2). Wystarczy, że klikniemy prawym przyciskiem myszy na arkusz z zapytaniem PQ35 i z podręcznego menu wybierzemy polecenie Usuń (rys. nr 36).

rys. nr 36 — Usuń

Zapytanie to zostaje tylko w pamięci, a nie zajmuje osobnego arkusza. Zapytanie PQ35 (2) możemy zaznaczyć i wyciąć jak zwykłą tabelkę. Po czym wkleić do arkusza, w którym chcemy mieć wynik. Pusty arkusz z którego wycięliśmy zapytanie możemy również usunąć jak wyżej na rysunku nr 36. Na tym etapie po wprowadzeniu zmian musimy zapisać plik za pomocą skrótu klawiszowego Ctrl + S. Następnie klikamy prawym przyciskiem myszy na zapytanie i z podręcznego menu wybieramy polecenie Odśwież. Otrzymamy odświeżone dane bez arkuszy które usunęliśmy (rys. nr 37).

rys. nr 37 Ostateczne dane

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.

Power Query 32 — Powtarzanie kodów kreskowych w kolejnych wierszach

W dzisiejszym poście zajmiemy się tematem powtarzania kodów kreskowych bądź innych wartości w kolejnych wierszach. Omówimy rozwiązanie tego zagadnienia w Power Query na podstawie przykładowych danych źródłowych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Podczas jednej z moich konsultacji online pojawił się problem – potrzeba, aby powtarzać kodu kreskowe kilka razy pod rząd w kolejnych wierszach. Można to zrobić za pomocą VBA, ale tutaj omówimy rozwiązanie w Power Query. W naszych danych źródłowych kody kreskowe zapisane są w postaci ciągów cyfr, w drugiej kolumnie mamy podaną ilość powtórzeń danego kodu.

Aby wczytać dane do Power Query wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tKody (rys. nr 3). W Ustawieniach zapytania usuwamy krok Zmieniono typ z Zastosowanych kroków, ponieważ jest zbędny. Musimy zmienić typ danych ręcznie.

rys. nr 3 — Edytor zapytań

Nasz kod kreskowy jest zmieniony na liczby, a my chcemy by był traktowany jako tekst, ponieważ funkcja z jakiej będziemy korzystać w Power Query radzi sobie z tekstem, natomiast z liczbami nie (trzeba by dokonywać konwersji). Klikamy na ikonkę ABC123 z boku nazwy tytułu kolumny Kod kreskowy i z podręcznego menu wybieramy polecenie Tekst (rys. nr 4).

rys. nr 4 — Polecenie Tekst

W kolejnym kroku musimy dodać kolumnę za pomocą polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 5) z karty Dodaj kolumnę.

rys. nr 5 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie będziemy korzystać z funkcji. Zmieniamy nazwę kolumny na Powt, a następnie w polu Formuła kolumny niestandardowej wpisujemy formułę funkcji dla tej kolumny. Do zapisania formuły użyjemy kolumn z pola Dostępne kolumny (oznaczone strzałkami na rysunku nr 6). Zapis formuły będzie wyglądał następująco:

=Text.Repeat([#"Kod#(1f)kreskowy"],#"Powtórz [x] razy"])

Tak wpisane parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.

rys. nr 6 — Tworzenie kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — dane z nową kolumną

Dane te mają widoczne od razu niedociągnięcie, mianowicie poszczególne kody nie są od siebie niczym oddzielone. W naszym przykładzie kody kreskowe mają po 5 znaków, ale nie każde kody są jednakowe. Musimy wprowadzić modyfikacje w naszą kolumnę niestandardową. Klikamy na ikonkę koła zębatego obok nazwy kroku Dodano kolumnę niestandardową w Zastosowanych krokach. Otworzy nam się po raz kolejny okno Kolumny niestandardowej (rys. nr 8), gdzie musimy zmienić naszą formułę funkcji i wstawić rozdzielenie kodów za pomocą jakiegoś symbolu (w naszym przykładzie @, bo lubię małpki 😊). Zapis formuły będzie wyglądał następująco:

=Text.Repeat([#"Kod#(1f)kreskowy"] & "@",#"Powtórz [x] razy"])

Wprowadzoną zmianę zatwierdzamy przyciskiem OK.

rys. nr 8 Kolumna niestandardowa

Otrzymamy dane przedstawione na rysunku nr 9, gdzie kody zostały rozdzielone symbolem @.

rys. nr 9 — Dane z kodami rozdzielonymi znakiem @

Symbole te posłużą nam do rozdzielenia kolumn. Rozwijamy polecenia Podziel kolumny (punkt nr 2 na rysunku nr 10) z karty Narzędzia główne i wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 10).

rys. nr 10 — Podziel kolumny

Otworzy nam się okno Dzielenia kolumny według ogranicznika (rys. nr 11), gdzie Power Query rozpozna nasz ogranicznik. Musimy rozwinąć sobie Opcje zaawansowane, ponieważ chcemy zrobić podział na Wiersze (zaznaczone zielonym prostokątem na rysunku nr 11) , a nie kolumny, czyli podział ma być w dół danych (zaznaczone zieloną strzałką na rysunku nr 11) a nie w bok.

rys. nr 11 _ dzielenie kolumny według ogranicznika

Wprowadzone parametry podziały kolumny zatwierdzamy przyciskiem OK. i otrzymamy dane przedstawione na rysunku nr 12.

rys. nr 12 — Wydzielone kolumny

Usuwamy krok Zmieniono typ1 z Zastosowanych kroków i otrzymamy dane przedstawione na rysunku nr 13.

rys. nr 13 — Dane

W danych tych odfiltrujemy puste komórki, czyli klikamy ikonkę trójkąta przy nazwie kolumny Powt i w podręcznym menu odznaczamy checkbox przy nazwie Puste (rys. nr 14).

rys. nr 14 — Odfiltrowanie pustych

Nasz filtr zatwierdzamy przyciskiem OK i otrzymamy dane przedstawione na rysunku nr 15, które możemy wczytać do Excela.

rys. nr 15 — Odfiltrowane dane

Tak naprawdę nie potrzebujemy aż trzech kolumn, ponieważ mamy te same dane w kolumnie pierwszej i trzeciej (Kod kreskowy i Powt). Wystarczy nam jedna kolumna z powtórzonymi kodami. Zaznaczamy kolumnę Powt i klikamy na jej tytuł prawym przyciskiem myszy, z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 16).

rys. nr 16 — Usuń inne kolumny

Tak przygotowane dane możemy załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 17).

rys. nr 17 — zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a następnie określamy miejsce wstawienia danych jako Istniejący arkusz i wskazujemy konkretną komórkę w tym arkuszu (rys. nr 18). Tak ustawione parametry wstawienia danych zatwierdzamy przyciskiem Załaduj.

rys. nr 18 — Okno ładowania do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 19.

rys. nr 19 — Dane w Excelu

Mamy obliczoną dodatkowo ilość wszystkich powtórzeń – 88 sztuk. Taką samą ilość pokazuje nam zapytanie tKody w Zapytaniach dotyczących arkusza (oznaczone zielonymi strzałkami na rysunku nr 20).

rys. 20 — Ilość powtórzeń

Możemy sprawdzić działanie zapytania zmieniając dane w tabeli z danymi źródłowymi. Wystarczy odświeżyć dane z zapytania aby otrzymać aktualne wyniki.


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.

Power Query 31 — Rozrastająca się lista rozwijana

W dzisiejszym poście omówimy temat rozrastającej się listy rozwijanej z unikatowymi wartościami w Power Query. Rozwiązanie tego zadania w Excelu przedstawione zostało w poradzie nr 322 https://exceliadam.pl/?s=porada+322 . W danych źródłowych mamy już listę rozwijaną a naszym zadaniem jest dodanie do niej kolejnych elementów, chcemy dopisać do listy nowe osoby. Dane, na których omówimy to zagadnienie zostały przedstawione na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Rozwiążemy takie zadanie nie korzystając z formuł, ale przy użyciu Power Query – dodatku do Excela. Pierwszym krokiem jest zaczytanie danych do Power Query. Wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli

Otworzy nam się edytor zapytań z wczytaną tabelą tSprzedawcy.

rys. nr 3 — Edytor zapytań

Usuwamy krok Zmieniono typ z zastosowanych kroków, bo jest on zbędny. Następnie odfiltrowujemy kolumnę Sprzedawca po wartościach null, czyli klikamy na ikonkę trójkąta w nazwie kolumny Sprzedawca i w podręcznym menu ozdnaczamy checkbox przy wartości null (rys. nr 4). Nasz filtr zatwierdzamy przyciskiem OK.

rys. nr 4 — Odfiltruj dane po wartości null

W kolejnym kroku usuwamy inne kolumny, czyli klikamy prawym przyciskiem myszy na tytuł kolumny Sprzedawca i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).

rys. nr 5 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 6.

rys. nr 6

Interesuje nas tylko kolumna Sprzedawca. Chcemy mieć unikatową listę sprzedawców, więc rozwijamy polecenie Usuń wiersze (punkt nr 2 na rysunku nr 7) z karty Narzędzia główne, a następnie wybieramy polecenie Usuń duplikaty (punkt nr 3 na rysunku nr 7).

rys. nr 7 — Usuń duplikaty

Tak przygotowaną listę danych możemy załadować do Excela. W tym celu rozwijamy polecenie Zamknij i załaduj (punkt nr 2 na rysunku nr 8) z karty Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (punkt nr 3 na rysunku nr 8).

rys. nr 8 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a nastepnie określamy lokalizaję wstawienia danych – Istaniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem Załaduj (rys. nr 9).

rys. nr 9 — Okno Ładowania do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 10.

rys. nr 10 — Dane wczytane do Excela

Zaznaczamy zakres danych w tabeli z zapytania z Power Query a następnie w polu obok paska formuły zmieniamy nazwę tego zakresu na Sprzedawcy (pole oznaczone zieloną strzałką na rysunku nr 11).

rys. nr 11 — Zmiana nazwy zakresu

W kolejnym etapie zaznaczamy zakres w tabeli z danymi źródłowymi i wybieramy polecenie Poprawność danych (punkr nr 2 na rysunku nr 12) z karty Dane.

rys. nr 12 — Poprawność danych

Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia (rys. nr 13) ustalamy Kryteria poprawności danych i podajemy źródło danych (klawisz F3) – wcześniej nazwany zakres Sprzedawcy z tabeli zaczytanej z Power Query. W karcie Komunikat wejściowy odznaczamy checkbox przy opcji Pokazuj komunikat wejściowy przy wyborze komórki. W karcie Alert o błędzie odznaczamy checkbox przy opcji Pokazuj alerty po wprowadzeniu nieprawidłowych danych. Nie chcemy informacji o błędnie wpisanych danych ponieważ chcemy dopisywać nowe osoby do listy sprzedawców. Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 13 — Użycie klawisza F3

Teraz możemy sobie dopisać sprzedawcę w tabeli z danymi źródłowymi, ale nie ma jej na liście rozwijanej w tej tabeli co przedstawia rysunek nr 14.

rys. nr 14 — Lista rozwijana

Wynika to z podstawowej wady Power Query – nie odświeża się automatycznie. Musimy kliknąc prawym przyciskiem myszy na dowolną komórkę w zakresie Sprzedawcy i z podręcznego menu wybrać polecenie Odśwież (rys. nr 15).

rys. nr 15 — Odśwież

Po odświeżeniu danych z Power Query  dodany sprzedawca będzie widoczny na liście rozwijanej (rys. nr 16).

rys. nr 16

Istnieje możliwość ustawienia automatycznego odświeżania danych za pomocą kodu VBA. Korzystając ze skrótu klawiszowego Alt+F11 możemy przejść do okna Edytora VBA. Jest tam wcześniej przygotowany kod (rys. nr 17).

rys. nr 17 — Alt+F4 przejście do VBA

W arkuszu (Arkusz4 (PQ31)- punkt nr 1 na rysunku nr 18), w którym mamy te listy , musimy dopisać kod VBA. Kod ten będzie działał tylko w momencie, kiedy w naszym arkuszu (Worksheet – punkt nr 2 na rysunku nr 18)) dokona się zmiana (Change – Punkt nr 3 na rysunku nr 18). W sytuacji zmiany w kolumnie A, chcemy aby odpalił się kod VBA i sprawdził czy zmieniane komórki miały część wspólną z kolumną A. Konkretnie sprawdzamy czy zakres który był zmieniany ma część wspólną z kolumną która nas interesuje. Jeśli zmiana nastąpiła w kolumnie A, to nastąpi automatyczne odświeżenie danych w tabeli z Power Query.

rys. nr 18 — Edytor VBA

Zapisujemy nasz kod za pomocą skrótu klawiszowego Ctrl+S. Przechodzimy do Excela i możemy sprawdzić działanie kodu VBA. Dopisujemy kolejnego sprzedawcę (Agnieszka) do danych źródłowych i dane automatycznie się odświeżą i nasz nowy sprzedawca zostanie dodany do listy rozwijanej, co widać na rysunku nr 19.

rys. nr 19 — Dane z kodem z VBA

Podsumowując rozrastającą się listę rozwijaną z unikatowymi wartościami robi się prościej za pomocą Power Query, ale niestety nie jest automatyczna i musimy pamiętać o odświeżaniu danych. Jedynym sposobem na zautomatyzowanie jest dodanie kodu VBA, ale to już temat dla bardziej zaawansowanych użytkowników Excela.

Możemy również zarejestrować makro odświeżania danych w karcie Deweloper, wybierając polecenie Rejestruj makro (punkt nr 2 na rysunku nr 20).

rys. nr 20 — Zarejestruj makro

Otworzy się okno Rejestrowania makra, gdzie wpisujemy nazwę makra i zatwierdzamy przyciskiem OK (rys. nr 21).

rys. nr 21 — Okno rejestrowania makra

Następnie klikamy prawym przyciskiem myszy na dowolną komórkę z zakresu zapytania z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 22).

rys. nr 22 — Odśwież

Następnie klikamy polecenie Zatrzymaj rejestrowanie (punkt nr 2 na rysunku nr 23) z karty Deweloper.

rys. nr 23 — zatrzymaj rejestrowanie makra

Teraz w VBA mamy dostępny nowy Moduł, odpowiadający odświeżeniu danych (zaznaczony zieloną strzałką na rysunku nr 24).

rys. nr 24 — Nowy moduł

Dzięki stworzeniu takiego makra mamy automatycznie rozrastającą się listę rozwijaną.


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.

Power Query #28 — Pary produktów, które wystąpiły razem

W dzisiejszym poście nauczymy jak w Power Query znaleźć pary produktów, które wystąpiły razem w danych. Nie ma znaczenia czy będziemy bazować na Id produktu czy na jego nazwie. Załóżmy, że mamy listę zakupów z produktami oznaczonymi numerami Id. Ręczne wyszukiwanie par jest pracochłonne i męczące. Rozwiązanie tego problemu omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

W pierwszym kroku musimy wczytać nasze dane do Power Query, w tym celu wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą tIdProduktu. W polu Zastosowane kroki usuwamy krok Zmiana typu, ponieważ nie jest nam potrzebny. Otrzymamy dane przedstawione na rysunku nr 3.

rys. nr 3 — edytor zapytań

W kolejnym etapie musimy zduplikować tą kolumnę. Wybieramy polecenie Duplikuj kolumnę (punkt nr 2 na rysunku nr 4) z karty Dodaj kolumnę.

rys. nr 4 — duplikuj kolumnę

Następnie dokładamy jeszcze kolumnę z indeksem, żebyśmy widzieli które to były zakupy. Wybieramy polecenie Kolumna indeksu (punkt nr 2 na rysunku nr 5) a następnie od 01 (punkt nr 3 na rysunku nr 5) z karty Dodaj kolumnę.

rys.. nr 5 — Dodaj kolumnę indeksu

Aby w Power Query znaleźć pary produktów musimy podzielić dane po ogranicznikach a następnie odpowiednie dane połączyć. Wybieramy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 6) z karty Narzędzia główne, a następnie polecenie Według ogranicznika (punkt nr 3 na rysunku nr 6).

rys. nr 6 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie musimy określić parametry tego dzielenia. W polu Wybierz lub wprowadź ogranicznik wybieramy Niestandardowy (punkt nr 1 na rysunku nr 7), a następnie wpisujemy użyty u nas ogranicznik, czyli przecinek i spacja. W opcji Podziel przy wybieramy checkbox przy Każdym wystąpieniu ogranicznika (punkt nr 2 na rysunku nr 7). Natomiast w Opcjach zaawansowanych Podziel na (punkt nr 3 na rysunku nr 7) zaznaczamy checkbox przy Wierszach. Tak dobrane ustawienia zatwierdzamy przyciskiem OK.

rys. nr 7 — Dzielenie kolumny według ogranicznika

Otrzymamy dane, których fragment został przedstawiony na rysunku nr 8.

rys. nr 8 Podzielone dane

Power Query przy okazji dzielenia kolumny zamienił nam dane na liczby. Nie ma to większego znaczenia. Analogicznie postępujemy dla drugiej kolumny (kopii pierwszej). Wybieramy polecenie Podziel kolumny, a następnie według ogranicznika z karty Narzędzia główne (jak na rysunku nr 6). Pojawi się nam okno Dzielenia kolumny według ogranicznika, w której ustawiamy takie same parametry jak dla pierwszej kolumny (jak na rysunku nr 7) i wybrane parametry zatwierdzamy klikając przycisk OK. Otrzymamy dane przedstawione na rysunku nr 9.

rys. nr 9 — takie same wartości w dwóch kolumnach

W danych tych pojawia się problem – mamy zduplikowane pary (zaznaczone na rysunku nr 9 zielonym prostokątem). Można łatwo rozwiązać ten problem, ponieważ interesuje nas sytuacja, kiedy danej z kolumny pierwszej są mniejsze od danych z kolumny drugiej. Wystarczy dodać kolumnę warunkową z karty Dodaj kolumnę (rys. nr 10)

rys. nr 10 — Dodaj kolumnę warunkową

Otworzy nam się okno Dodawania kolumny warunkowej, gdzie musimy określić Nazwę kolumny – Pary (punkt nr 1 na rysunku nr 11), Następnie wypełnić warunek w punkcie 2 na rysunku nr 11. Ważne jest, że jeśli warunek zostanie spełniony to chcemy otrzymać wartość z pierwszej kolumny, natomiast jeśli nie zostanie spełniony chcemy otrzymać wartość null (punkt nr 3 na rysunku nr 11). Tak dobrane parametry kolumny warunkowej zatwierdzamy przyciskiem OK.

rys. nr 11 — okno dodawania kolumny warunkowej

Konkretnie chcemy otrzymać wartość z pierwszej kolumny tylko wtedy kiedy wartość z pierwszej kolumny jest mniejsza od wartości z drugiej kolumny. Otrzymamy dane przedstawione na rysunku nr 12. Nie jest to jeszcze wynik docelowy, bo chcemy otrzymać parę, ale ważne jest, że para pokazuje się dla jednej sytuacji w pierwszym zakupie.

rys. nr 12 — Dane z kolumną warunkową

Aby otrzymać jako wynik parę, musimy wprowadzić zmiany w zapisie formuły w pasku formuły. Dołożyć z ogranicznikiem wartość z kolumny drugiej. Zapis formuły został przedstawiony na rysunku nr 13 (zieloną strzałką zaznaczono wprowadzoną zmianę). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.


rys. nr 13 — zapis formuły

W wyniku otrzymamy błąd ponieważ nasze dane zostały zmienione na liczby. Aby obejść ten problem musielibyśmy użyć funkcji Number.ToText. My użyjemy łatwiejszego sposobu – z zastosowanych kroków usuniemy krok zmiany typu danych z tekstu na liczby – Zmieniono typ i Zmieniono typ1 (rys. nr 14).

rys. nr 14 — usunięcie kroków

Otrzymamy wtedy prawidłowe dane przedstawione na rysunku nr 15.

rys. nr 15 — Dane w formie tekstowej

Pozostaje nam pogrupować te produkty po ilości, użyjemy do tego polecenia Grupowanie według (punkt nr 2 na rysunku nr 16) z karty Narzędzia główne.

rys. nr 16 — Grupowanie według

Otworzy się nam okno Grupowania według, w którym musimy określić nazwę nowej kolumny i rodzaj operacji jaką chcemy wykonać. Wybrane parametry zatwierdzamy przyciskiem OK (rys. nr 17).

rys. nr 17 — Okno Grupowania według

Otrzymamy dane przedstawione na rysunku nr 18.

rys. nr 18 — pogrupowane dane

W danych tych wiele razy powtórzyła nam się wartość null, więc wystarczy przefiltrować dane, żeby odfiltrować wyniki dla null. Klikamy na ikonkę trójkącika w nazwie kolumny Pary i odznaczamy w filtrach null (rys. nr 19).

rys. nr 19 — Odfiltrowanie danych dla null

W kolejnym etapie chcemy posortować dane malejąco w obu kolumnach. Korzystamy z podręcznego menu po naciśnięciu ikonki trójkącika w tytule kolumn, gdzie wybieramy polecenie Sortuj malejąco (rys. nr 20).

rys. nr 20 — Sortuj malejąco

Otrzymamy końcowe dane, które możemy załadować do Excela. Korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 21).

rys. nr 21 — Zamknij i załaduj do

W Excelu otworzy nam się okno Ładowania do, gdzie ustawiamy parametry wstawianych danych. Sposób wyświetlania danych jako tabela i miejsce wstawienia danych jako istniejący arkusz i wskazujemy konkretną komórkę. Powyższe parametry zatwierdzamy przyciskiem Załaduj (rys. nr 22).

rys. nr 22 — Okno Ładowanie do

Otrzymamy dane wstawione do Excela przedstawione na rysunku nr 23.

rys. nr 23 — dane wczytane do Excela

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.