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ójkurs 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.
Wstęp do aplikacji PowerBI Desktop i jej możliwości
Tworzenie i modyfikowanie zapytań (pobieranie danych)
Modelowanie danych w PowerBI Desktop
Wizualizacja danych i tworzenie raportów
Usługa internetowa
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.
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ójkurs 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.
Wstęp do aplikacji PowerBI Desktop i jej możliwości
Tworzenie i modyfikowanie zapytań (pobieranie danych)
Modelowanie danych w PowerBI Desktop
Wizualizacja danych i tworzenie raportów
Usługa internetowa
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.
W dzisiejszym poście omówimy sobie wyznaczenie ceny produktu na podstawie daty zamówienia. Zadanie to w Power Query rozwiązaliśmy już w poprzednim poście nr 33 https://exceliadam.pl/?p=9575 , za pomocą poleceń ze wstążki. W tym poście rozwiążemy ten problem przy użyciu funkcji Table.SelectRows. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Zadanie polega na znalezieniu ceny danego produktu na
podstawie daty zamówienia. Mamy podane również daty, kiedy cena ta ulegała
zmianie.
Zakładamy, że dwie tabelki z rysunku nr 1 mamy zaczytane do
Power Query z rozwiązania przedstawionego w poprzednim poście. Aby mieć podgląd
zapytań wybieramy polecenie Pokaż zapytania z karty Dane (rys. nr 2).
rys. nr 2 — pokaż zapytania
Następnie klikamy dwa razy myszką na nazwę naszego zapytania
w oknie Zapytań dotyczących skoroszytu, aby przejść do Power Query (rys. nr 3).
rys. nr 3 — Lista zapytań dotyczących skoroszytu
Otworzy nam się edytor zapytań z wczytaną tabelą tCeny oraz
tZamówienia (oznaczone zielonym prostokątem na rysunku nr 4). Naszym zadaniem
jest scalenie tych zapytań. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku
nr 4) z karty Narzędzia główne, następnie rozwijamy polecenie Scal zapytania
(punkt nr 3) i wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).
rys. nr 4 — Scal zapytania jako nowe
Otworzy nam się okno Scalanie, w którym musimy określić jego
parametry. W obu zapytaniach zaznaczamy kolumnę Produkt, ponieważ po tym
elemencie chcemy scalić zapytania. Wybieramy rodzaj sprzężenia jako Lewe
zewnętrzne, czyli wszystkie elementy z pierwszego zapytania i pasujące z
drugiego. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 5).
rys. nr 5 — Parametry scalania
Otrzymamy scalone dane przedstawione na rysunku nr 6.
rys. nr 6 — Scalone dane
W kolejnym etapie zmieniamy nazwę kroku na Scalone2 we
właściwościach w Ustawieniach zapytania. Pierwszy krok w Power Query jest
identyczny z rozwiązaniem przedstawionym w poprzednim poście. W danych tych
mamy już ukryte interesujące nas informacje, czyli pod słowem Table kryje się
tabela z datami zmiany ceny produktu (rys. nr7).
rys. nr 7 — Dane ukryte pod Table
Dane w tabelce poniżej musimy odfiltrować, czyli wyrzucić z
niej te daty które nie pasują do daty zamówienia. Dla przypomnienia – nasze
dane posortowaliśmy wcześniej po produkcie i dacie zmiany ceny, dzięki temu
możemy teraz relatywnie prosto znaleźć rozwiązanie. Z karty Dodaj kolumnę
wybieramy polecenie Kolumna niestandardowa (rys. nr 8).
rys. nr 8 — Kolumna niestandardowa
Otworzy nam się okno kolumny niestandardowej, gdzie zmieniamy
nazwę nowej kolumny na REC (jak Bill Szysz w swoim rozwiązaniu). Następnie w
polu Formuła kolumny niestandardowej wpisujemy formułę funkcji
Table.SelectRows. Zapis jej powinien wyglądać następująco:
=Table.SelectRows([tCeny] each
[Data zmiany ceny] <= #date (2018,2,1))
Funkcja ta pozwoli nam wyciągnąć konkretne wiersze z kolumny
tCeny pod nałożonym warunkiem, czyli dla każdego wiersza gdzie Data zmiany ceny
jest mniejsza bądź równa dacie zamówienia. Wpiszemy w formule zamiast Daty
zamówienia znak # ponieważ na razie nie możemy sięgnąć po datę z tej kolumny.
Tak ustawione parametry zatwierdzamy klikając przycisk OK (rys. nr 9).
rys. nr 9 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 10.
rys. nr 10 — Dane
Formuła ta ogranicza nam wyniki lecz nie we właściwy sposób.
My chcemy odwoływać się nie do konkretnej podanej daty a do innej daty dla
każdego wiersza, czyli do daty zamówienia każdego produktu. Nie możemy ręcznie
wpisać w formule odwołania do kolumny Data zamówienia ponieważ funkcja zwróci
nam błąd (nie może znaleźć pola). Rys. nr 11.
rys. nr 11 — Error
Kiedy wpiszemy nazwę funkcji w pasku formuły Power Query
wyświetli nam informacje o niej. Możemy wpisywać w niej albo wartości stałe
albo wartości z wierszy z tej tabeli. Nie możemy użyć kolumny Data zamówienia
ponieważ nie zawiera się w tabelach ukrytych pod Table w kolumnie tCena (rys.
nr 12).
rys. nr 12
Bill Szysz podpowiedział mi rozwiązanie tego problemu. Możemy
sobie wkleić funkcję (x) w funkcję Table.SelectRows. Funkcja ta to tak naprawdę
cały wiersz naszych danych. Zapis powinien wyglądać następująco:
=Table.AddColumn (Źródło, "REC", (x) =>
Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia]))
Otrzymamy dane przedstawione na rysunku nr 13, gdzie nadal
czasem w wynikach mamy dwa wiersze.
rys.nr 13 — dane
Aby uzyskać pojedyncze rozwiązanie musimy wstawić kolejną
funkcję. Pamiętamy, że nasze dane posortowaliśmy według Produktu i Daty zmiany
ceny więc wynikiem będzie ostatni wiersz.
rys. nr 14 — Wynik prawidłowy
Zapis funkcji będzie wyglądał następująco:
= Table.AddColumn (Źródło, "REC", (x) =>
Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data
zamówienia])))
Otrzymamy tym razem pojedyncze wyniki dla każdego wiersza
przedstawione na rysunku nr 15.
rys. nr 15 pojedyncze wyniki dla każdego wiersza
Aby wyciągnąć dane, które nas interesują, czyli cenę, klikamy
na ikonkę ze strzałkami w nazwie kolumny REC i zaznaczamy checkbox przy
wartości Cena i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy
kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy klikając przycisk
OK (rys. nr 16).
rys. nr 16 — Odfiltrowanie danych
Otrzymamy
dane przedstawione na rysunku nr 17.
rys. nr 17 — Odfiltrowane dane
Na tym etapie możemy już usunąć kolumnę tCeny.
Aby uczyć się formuł w Power Query możemy usunąć krok Rozwinięty element REC z Zastosowanych kroków i wprowadzić kolejny element do formuły naszej funkcji. Poprzednia formuła zwróciła nam Rekord, czyli jeden cały wiersz. Kiedy dopiszemy w formule [Cena], otrzymamy już interesujący nas wynik (rys. nr 18). Zapis formuły powinien wyglądać następująco:
= Table.AddColumn (Źródło, "REC", (x) => Table.Last( Table.SelectRows (x[tCeny], each [Data zmiany ceny] <= x[Data zamówienia])) [Cena])
rys. nr 17 — Aktualne dane
Klikamy prawym przyciskiem myszy na nazwę kolumny tCeny i za
pomocą polecenia Usuń z podręcznego menu usuwamy niepotrzebną kolumnę (rys. nr
19).
rys. nr 19 — Usuń kolumnę
Możemy jeszcze w zapisie formuły zmienić nazwę kolumny REC na
Cena. Otrzymamy dane przedstawione na rysunku nr 20.
rys. nr 20 — Dane
Podsumowując dziś omówiony sposób może wydawać się
trudniejszy, ale gdy się nauczymy funkcji i formuł Power Query rozwiązywanie
tego typu problemów zajmie nam chwilę. Skomplikowane w tym rozwiązaniu jest to,
że łączymy pracę na tabelach wewnętrznych (ukrytych pod wartościami Table) oraz
na tabelach danych z zapytań (rys. nr 21).
rys. nr 21 — Dane ukryte pod Table
Tak przygotowane dane możemy zaczytać do Excela za pomocą polecenia Zamknij i załaduj do z karty
Narzędzia główne (rys. nr 22).
rys. nr 22 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób
wyświetlania danych jako Tabela oraz miejsce wstawienia danych – istniejący
arkusz i wskazujemy konkretną komórkę (rys. nr 23).
rys. nr 23 — Okno ładowanie do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr
24.
rys. nr 24 — Dane wczytane do Excela
Pierwsze rozwiązanie przedstawione w poprzednim poście jest czasochłonne
i zawiera wiele kroków, ale wszystkie kroki możemy wykonać z interfejsu
użytkownika. Drugie rozwiązanie jest szybsze ale wymaga dobrej znajomości
języka M i funkcji Power Query.
Właśnie dodałem mójkurs 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.
Wstęp do aplikacji PowerBI Desktop i jej możliwości
Tworzenie i modyfikowanie zapytań (pobieranie danych)
Modelowanie danych w PowerBI Desktop
Wizualizacja danych i tworzenie raportów
Usługa internetowa
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.
W dzisiejszym poście nauczymy się jak znaleźć cenę produktu
dla konkretnej daty. Pomysł na ten odcinek zaczerpnąłem z Excel Magic Trick nr
1483, gdzie Mike
Girvin rozwiązywał podobny problem w Excelu. W danych źródłowych mamy
tabelkę z cenami produktów oraz datami,
kiedy te ceny ulegały zmianie (rys. nr 1).
rys. nr 1 — Przykładowe dane
Według danych bazowych produkt ABC dnia 2018‐01‐01 kosztował
10zł, następnie cena ta zmieniła się dnia 2018‐03‐01 na 12zł. Kolejna zmiana
ceny miała miejsce 2018‐07‐15 kiedy spadła na 9zł. Naszym zadaniem jest znaleźć
aktualną cenę dla konkretnej daty zamówienia (rys. nr 2). Mike Girvin pokazał
różne metody rozwiązania tego problemu w Excelu.
rys. nr 2 — Dane z datą zamówienia
W tym poście pokażemy jak rozwiązać takie zadanie w Power Query za pomocą poleceń ze wstążki. W kolejnym poście (Power Query 34 https://exceliadam.pl/?p=9634 ) omówimy rozwiązanie bardziej eleganckie, o które zapytałem na forum Excela (ponieważ czułem że moje rozwiązanie jest zbyt skomplikowane). Rozwiązanie do kolejnego odcinka podpowiedział mi Bill Szysz – w jego pomyśle wystarczyły trzy kroki aby rozwiązać ten problem.
Przygotowałem odpowiednio tabelki, które wczytamy do Power
Query. Pierwsza nazywa się tCeny, a druga tZamówienia. Nazwę tabel możemy
sprawdzić w karcie Projektowanie, w grupie poleceń Właściwości (oznaczone
zieloną strzałką na rysunku nr 3).
rys. nr 3 — Karta Projektowanie
Aby pobrać tabelę do Power Query zaznaczamy dowolną komórkę w
tej tabeli a następnie wybieramy polecenie Z tabeli z karty Dane (rys. nr 4).
rys. nr 4 — Z tabeli
Otworzy nam się Edytor zapytań z wczytaną tabelą tCeny
przedstawioną na rysunku nr 5.
rys. nr 5 — Edytor zapytań
Power Query błędnie zmienił typ danych w kolumnie Data zmiany
ceny. Klikamy na ikonkę kalendarza przy nazwie tej kolumny i z podręcznego menu
wybieramy polecenie Data (rys. nr 6).
rys. nr 6 — Format danych Data
Wyświetli nam się komunikat dotyczący zmiany typu kolumny,
który musimy zatwierdzić przyciskiem Zamień bieżącą (rys. nr 7).
rys. nr 7 — Zamień bieżącą
W kolejnym kroku powinniśmy posortować nasze dane w kolumnie
Produkt. W tym celu klikamy na ikonkę trójkąta obok nazwy kolumny i z
podręcznego menu wybieramy polecenie Sortuj rosnąco (rys. nr 8).
rys. nr 8 — Sortuj rosnąco
Następnie analogicznie postępujemy dla drugiej kolumny (Data
zmiany ceny), korzystając z polecenie Sortuj rosnąco. Ważnym jest, że w Power
Query kolejność sortowania działa w odwrotnej kolejności niż w Excelu.
Kolejność sortowania jest widoczna w tytułach kolumn (rys. nr 9). W naszym
przykładzie dane zostaną najpierw posortowane po produkcie a później po dacie.
rys. nr 9 — Kolejność sortowania kolumn
Dla odmiany w Excelu przy takiej kolejności sortowania jak
wyżej, ważniejsza było ostatnie sortowania, czyli najpierw dane zostały by
posortowane po dacie a później po produkcie.
Otrzymamy dane przedstawione na rysunku nr 10.
rys. nr 10 — Posortowane dane
Drugą tabelę przygotowaliśmy wcześniej. Jej wczytywanie do
Power Query odbywa się w sposób identyczny jak pierwszej. Mamy dwa zapytania
tZamówienia i tCeny (rys. nr 11).
rys. nr 11 — Lista zapytań
W kolejnym kroku chcemy scalić tabelki z tych dwóch zapytań.
Klikamy na zapytanie tZamówienia, potem rozwijamy polecenie Połącz (punkt nr 2
na rysunku nr 12) z karty Narzędzia główne i polecenie Scal zapytania (punkt nr
3), a następnie wybieramy polecenie Scal zapytania jako nowe (punkt nr 4).
rys. nr 12 — Scal zapytania jako nowe
Otworzy nam się okno Scalania, w którym wybieramy tabele i
pasujące kolumny, po których chcemy utworzyć scaloną tabelę (punkt nr 1 i nr 2
na rysunku nr 13). Wybieramy również rodzaj sprzężenia (punkt nr 3) – Lewe
zewnętrzne, czyli wszystkie z pierwszej tabeli i pasujące z drugiej tabeli).
Wybrane parametry zatwierdzamy przyciskiem OK.
rys. nr 13 — Okno Scalania
W ustawieniach zapytania możemy zmienić nazwę scalonych
danych na Scalone1 (rys. nr 14).
rys. nr 14 — Zmiana nazwy zapytania
Otrzymamy scalone dane przedstawione na rysunku nr 15.
rys. nr 15 — Scalone dane
W późniejszym etapie zrobimy kolejne scalanie – będzie to
Scalone 2 więc możemy sobie od razu Zduplikować aktualne scalanie i zmienić mu
nazwę na docelową. Klikamy prawym przyciskiem myszy na nazwę zapytania i z
podręcznego menu wybieramy polecenie Duplikuj (rys. nr 16).
rys. nr 16 — Duplikuj zapytanie
W ustawieniach zapytania zmieniamy nazwę tego zapytania na
Scalone2. W otrzymanych danych pod każdym skrótem Table kryje się tabelka z
danymi(rys. nr 17).
rys. nr 17 — Table
W każdym wierszu z Table dla danego produktu, wyciągane są
odpowiednie wiersze z tabeli tCena z tym samym produktem. Wartości te są
skumulowane w małych tabelkach odpowiadających każdemu wierszowi.
Interesuje nas cena, która jest aktualna dla naszej daty
zamówienia. Na przykład w pierwszym wierszu mamy 2018‐02‐26, data ta jest
mniejsza od 2018‐03‐01, czyli obowiązuje dla tej konkretnej daty pierwsza cena
(10 zł). Chcielibyśmy usunąć z tej tabelki pozostałe ceny (rys. nr 18).
rys. nr 18 — Właściwy wynik
Możemy to zrobić klikając ikonkę ze strzałkami w nazwie
kolumny tCeny, a następnie rozwinąć naszą tabelkę. Potrzebujemy z tej tabelki
Datę zmiany ceny i Ceny, nie potrzebujemy natomiast Produktu (rys. nr 19).
Parametry rozwijania danych zatwierdzamy przyciskiem OK.
rys. nr 19 — filtrowanie danych
Otrzymamy dane przedstawione na rysunku nr 20.
rys. nr 20 — Przefiltrowane dane
Mamy liczbę porządkową zamówienia, do każdej daty zamówienia
mamy przypisane daty ze zmianą ceny oraz z ceny. Cofniemy poprzedni krok za
pomocą ikonki koła zębatego przy nazwie kroku, aby nie dodawał nam się prefiks
– usuwamy zawartość pola Domyślny prefiks nazwy kolumny (rys. nr 21).
rys. nr 21 — Rozwijanie danych
Otrzymamy dane przedstawione na rysunku nr 22.
rys. nr 22 — rozwinięte dane
Interesuje nas maksymalna data poniżej daty zamówienia.
Musimy dodać kolumnę warunkową. Wybieramy polecenie Kolumna warunkowa z karty
Dodaj kolumnę (rys. nr 23).
rys. nr 23 — Kolumna warunkowa
Otworzy nam się okno Dodawania kolumny warunkowej, w którym
określamy nazwę nowej kolumny (punkt nr 1 na rysunku nr 24 – nazwa Daty) i
warunek jaki musi spełnić jej wynik. Nasz warunek będzie wyglądał następująco:
Jeśli Data zmiany ceny (pole nr 2) jest przed lub równa (pole nr 3) wartości z
kolumny Data zamówienia (punkt nr 4), to chcemy zwrócić wartość z kolumny Data
zmiany ceny (punkt nr 5). W przeciwnym wypadku chcemy otrzymać wartość null
(punkt nr 6). Wybraliśmy wartość null ponieważ później możemy łatwo odfiltrować
dane po tej wartości. Powyższe parametry zatwierdzamy przyciskiem OK.
rys. nr 24 — Parametry kolumny warunkowej
Otrzymamy dane przedstawione na rysunku nr 25.
rys. nr 25 — Dane z kolumną warunkową
Możemy łatwo zauważyć dla pierwszego wiersza, że mamy już
podany wynik. Prawidłowa cena to 10, ponieważ dla innych wartość mamy wartości
null (rys. nr 26).
rys. nr 26 — właściwy wynik
Natomiast dla np. wiersza piątego mamy dwie daty, co może być
mylące (rys. nr 27). Z tych danych interesuje nas cena, która jest przy
starszej dacie.
rys. nr 27 dwa wyniki spełniające dany warunek
Przede wszystkim musimy odfiltrować nasze dane po wartościach null. Klikamy na ikonkę trójkącika przy nagłówku kolumny Daty i podręcznym menu odznaczamy checkbox przy wartości null (rys. nr 28). Nałożony filtr zatwierdzamy przyciskiem OK.
rys. nr 28 — Odfiltrowanie danych po wartości null
Otrzymamy dane przedstawione na rysunku nr 29.
rys. nr 29 — Aktualne dane
W następnej kolejności musimy zgrupować nasze dane. Wybieramy
polecenie Grupowanie według z karty Narzędzia główne (rys. nr 30)
rys. nr 30 — Grupowanie według
Otworzy nam się okno Grupowania według, gdzie ustawiamy
grupowanie zaawansowane (punkt nr 1 na rysunku nr 31) i trzy grupy po których
chcemy pogrupować dane (L.p., Data zamówienia i Produkt – punkt nr 2 na rysunku
nr 31)). W punkcie oznaczonym numerem 3 na rysunku nr 31 wybieramy rodzaj
operacji – Maksimum z kolumny Daty. Zmieniamy nazwę nowej kolumny na Najnowsza
cena. Ustawione parametry zatwierdzamy przyciskiem OK.
rys. nr 31 — Parametry grupowania
Otrzymamy dane przedstawione na rysunku nr 32, czyli najnowsze daty z tabeli tCena.
rys. nr 32 — Pogrupowane dane
Kiedy mamy już wyznaczone te daty, brakuje nam wartości ceny
dla tych dat. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 33) z karty
Narzędzia główne, a następnie wybieramy polecenie Scal zapytania (punkt nr 3).
rys. nr 33 — Scal zapytania
Otworzy nam się okno Scalania, gdzie wybieramy tabele i
pasujące w nich kolumny (zaznaczamy dwie kolumny przytrzymując klawisz Ctrl).
Interesują nas kolumny Produkt i Data. Wybieramy rodzaj sprzężenia Lewe
zewnętrzne (wszystkie z pierwszej i pasujące z drugiej). Tak ustawione
parametry zatwierdzamy przyciskiem OK (rys. nr 34).
rys. nr 34 — Parametry scalania
Otrzymamy dane z kolumną tCeny, w której dane są ukryte pod
postacią tablic. Klikamy ikonkę ze strzałkami przy nazwie kolumny tCeny a
następnie wybieramy z podręcznego menu polecenie Rozwiń i zaznaczamy checkbox
przy wartości Cena. Odznaczamy opcję Użyj oryginalnej nazwy kolumny jako
prefiksu i zatwierdzamy przyciskiem OK. (rys. nr 35).
rys. nr 35 — Rozwiń dane
Otrzymamy dane, z których usuwamy kolumnę najnowsza data
ponieważ jest niepotrzebna (rys. nr 36).
rys. nr 36 — Rozwinięte dane
Tak przygotowane dane możemy wczytać do Excela. Rozwijamy
polecenie Zamknij i załaduj z karty Narzędzia główne, a następnie wybieramy
polecenie Zamknij i załaduj do (rys. nr 37).
rys. nr 37 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowania do, gdzie ustawiamy
sposób wyświetlania danych jako Utwórz tylko połączenie i zatwierdzamy
przyciskiem Załaduj (rys. nr 38).
rys. nr 38 — Parametry ładowania danych
W oknie Zapytania dotyczące skoroszytu wybieramy zapytanie,
które chcemy wstawić do arkusza (Scalone1), a następnie wybieramy polecenie
Pokaż zapytania z karty Dane (rys. nr 39).
rys. nr 39 — Załadowane dane do Excela
Klikamy prawym przyciskiem myszy na nazwę zapytania Scalone1
i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 40).
rys. nr 40 — Załaduj do
Otworzy nam się okno Ładowania do, gdzie wybieramy wstawienie
danych jako tabela w istniejącym arkuszu, a następnie wskazujemy konkretną
komórkę (rys. nr 41). Tak ustawione parametry zatwierdzamy przyciskiem Załaduj.
rys. nr 41 — Parametry ładowania danych
Otrzymamy dane przedstawione na rysunku nr 42
rys. nr 42 — Dane w Excelu
Zapomnieliśmy posortować dane po liczbie porządkowej, więc
klikamy dwukrotnie na nazwę naszego zapytania Scalone1 i przechodzimy do
Edytora zapytań Power Query. Klikamy na ikonkę trójkącika przy nazwie kolumny
L.p. i w menu wybieramy polecenie Sortuj rosnąco (rys. nr 43).
rys. nr 43 — Sortuj rosnąco
Klikamy polecenie Zamknij załaduj z karty Narzędzia główne.
Po powrocie do Excela otrzymujemy prawidłowe, posortowane dane przedstawione na
rysunku nr 44.
rys. nr 44 — Prawidłowe dane w Excelu
Podsumowując przedstawione rozwiązanie było długie i nazbyt skomplikowane, ale najważniejsze, że działa prawidłowo. Co ważne w tym zadaniu korzystaliśmy tylko z poleceń, które są na wstążce. W kolejnym odcinku przedstawimy szybsze rozwiązanie, ale będziemy musieli w nim skorzystać z funkcji języka M.
Właśnie dodałem mójkurs 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.
Wstęp do aplikacji PowerBI Desktop i jej możliwości
Tworzenie i modyfikowanie zapytań (pobieranie danych)
Modelowanie danych w PowerBI Desktop
Wizualizacja danych i tworzenie raportów
Usługa internetowa
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.
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:
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:
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ójkurs 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.
Wstęp do aplikacji PowerBI Desktop i jej możliwości
Tworzenie i modyfikowanie zapytań (pobieranie danych)
Modelowanie danych w PowerBI Desktop
Wizualizacja danych i tworzenie raportów
Usługa internetowa
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.
