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 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.
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ó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 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ó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 połączyć listy osób
zapisanych na różne kursy za pomocą Power Query – dodatku do Excela. W
przykładowych danych do tego zagadnienia przedstawionych na rysunku nr 1 mamy
podane trzy listy adresów mailowych związanych z różnymi kursami. Naszym
zadaniem jest połączenie tych list i stworzenie skumulowanej tabeli z adresami
mailowymi z zaznaczonymi kursami, na jaki właściciel danego maila się zapisał.
rys. nr 1 — Przykładowe dane
Nasze zadanie ma również na celu to, żeby konkretna osoba
która zapisała się na wszystkie trzy kursy w naszej tabeli występowała jeden
raz (np. Andrzej@gazeta.pl zapisał
się na trzy kursy).
Pierwszym krokiem jest zaczytanie naszych danych bazowych, w
tym celu zaznaczamy dowolną komórkę w pierwszej tabeli i wybieramy polecenie Z
tabeli (punkt nr 2 na rysunku nr 2) z karty Dane. Ułatwieniem tutaj jest, że
tabele wejściowe zostały nazwane Tabela1, Tabela2 i Tabela3.
rys. nr 2 — Z tabeli
Otworzy nam się Edytor zapytań z wczytaną tabelą Tabela1
(rys. nr 3). Z Zastosowanych kroków (Ustawienia zapytania) usuwamy krok
Zmieniono typ, ponieważ jest on niepotrzebny.
rys. nr 3 — Edytor zapytań
Pozostałe tabele możemy wczytać w łatwy sposób, wystarczy że
zduplikujemy sobie Zapytanie. Klikamy prawym przyciskiem myszy na nazwę
Zapytanie1 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 4).
Czynność tę powtarzamy dwa razy ponieważ potrzebujemy trzech tabel.
rys. nr 4 — Duplikuj zapytanie
Zaznaczamy sobie zapytanie Tabela1 (2) i w pasku formuły
zmieniamy nazwę Tabela1 na Tabela2 (oznaczone zieloną strzałką na rysunku nr
5). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.
rys. nr 5 — Zmiany w pasku formuły
Otrzymamy zapytanie Tabela1 (2) z wczytaną drugą tabelą z
danymi (osoby zapisane na kurs Dashboardy) przedstawione na rysunku nr 6. Po
raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.
rys. nr 6 — Połączone zapytania
Analogicznie wygląda sytuacja dla trzeciego zapytania.
Klikamy na nazwę zapytania Tabela1 (3) i w pasku formuły zmieniamy
nazwę Tabela1 na Tabela3. Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.
Otrzymamy trzecie zapytanie z wczytanymi danymi z tabeli numer 3
przedstawionymi na rysunku nr 7. Po raz kolejny usuwamy niepotrzebny krok
Zmieniono typ z Zastosowanych kroków.
rys. nr 7 — Dane z trzech tabel
Kolejnym krokiem jest zmiana nazwy zapytań nr 2 i 3 we
Właściwościach w panelu bocznym z Ustawieniami zapytania (oznaczone zieloną
strzałką na rysunku nr 8).
rys. nr 8 — Zmiana nazwy zapytania
Kiedy już mamy zaczytane wszystkie trzy tabele, możemy je
połączyć. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 9) z karty
Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3 na rysunku
nr 9).
rys. nr 9 — Dołącz zapytania
Pojawi nam się okno Dołączanie, gdzie zaznaczamy checkbox
przy opcji Co najmniej trzy tabele (punkt nr 1 na rysunku nr 10). Następnie
dodajemy Tabelę2 i Tabelę3 z pola Dostępne tabele do pola Tabele do dołączenia
za pomocą przycisku Dodaj (punkt nr 2 na rysunku nr 10). Dołączone tabele
zatwierdzamy przyciskiem OK.
rys. nr 10 — Okno Dołączania zapytań
Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich
trzech tabel (rys. nr 11).
rys. nr 11 — Skumulowane dane
Mimo że dane zostały dołączone to nadal osoby które zapisały
się na większą ilość kursów są wyświetlane osobno, a nam chodzi o to by takie
osoby wyświetlane były w tabeli tylko raz. W tym celu musimy anulować
przestawienie innych kolumn poza kolumną Mail. Klikamy prawym przyciskiem myszy
na tytuł kolumny Mail i z podręcznego menu wybieramy polecenie Anuluj
przestawienie innych kolumn (rys. nr 12).
rys. nr 12 — Anuluj przestawienie innych kolumn
Otrzymamy dane przedstawione na rysunku nr 13, gdzie stworzą
nam się nowe kolumny. Kolumna Atrybuty będzie zawierała nazwy kolumn, natomiast
kolumna Wartość będzie zawierała wartości jakie były przypisane do odpowiednich
adresów mailowych w tych kolumnach.
rys. nr 13 — Dane z nowymi kolumnami
Najważniejszy efekt działania tego polecenia to pozbycie się
z danych wartości null. W kolejnym etapie musimy przekształcić jedną z tych
kolumn (Atrybuty bądź Wartość), zrobimy operację odwrotną. Z karty Przekształć
wybieramy polecenie Kolumna przestawna (punkt nr 2 na rysunku nr 14).
rys. nr 14 — Kolumna przestawna
Otworzy nam się okno Tabeli przestawnej, gdzie stworzymy nowe
kolumny przy użyciu kolumny Atrybyt, czyli powstaną kolumny z nazwami VBA,
Dashboardy i Excel (rys. nr 15). Odpowiednio w każdej z tych kolumn będzie
wpisana wartość z kolumny Wartość (punkt nr 1 na rysunku nr 15). Ponadto musimy
wybrać w Opcjach zaawansowanych wybieramy w jaki sposób będą agregowane nasze
dane – Nie agreguj (punkt nr 2 na rysunku nr 15). Wybrane parametry
zatwierdzamy przyciskiem OK.
rys. nr 15 — Okno kolumny przestawnej
Otrzymamy dane przedstawione na rysunku nr 16, gdzie mamy już
skumulowane dane. Przykładowo właściciel maila Andrzej@gazeta.pl występuje tylko raz na
liście mimo że zapisał się aż na trzy kursy.
rys. nr 16 — Skumulowane dane
Tak przygotowane dane możemy załadować do Excela za pomocą
polecenia Zamknij i załaduj (punkt nr 2 na rysunku nr 17) z karty Narzędzia
główne.
rys. nr 17 Zamknij i załaduj
Ponieważ jednocześnie pobieraliśmy dane z trzech tabel, to
utworzyło nam się w Excelu tylko połączenie. Dane te będą widoczne w panelu
bocznym Zapytania. Gdyby nie były widoczne należy wybrać polecenie Pokaż
zapytania z karty Dane (rys. nr 18).
rys. nr 19 — Pokaż zapytania
Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu
połączyliśmy wszystkie dane (rys. nr 19).
rys. nr 19 — Zapytanie Tabela1
Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i
z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).
rys. nr 20 — Załaduj do
Otworzy nam się okno Ładowania do, gdzie wybieramy sposób
przedstawienia załadowanych danych jako Tabela, a następnie lokalizację tych
danych jako Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 21).
Powyższe parametry zatwierdzamy przyciskiem Załaduj.
rys. nr 21 — Okno Ładowanie do
Otrzymamy dane załadowane do Excela przedstawione na rysunku
nr 22.
rys. nr 22 — Dane w Excelu
Przy tworzeniu danych został zrobiony błąd, którego nie
zauważyliśmy. W Excelu w oknie Zapytania dotyczące skoroszytu jest on
wyszczególniony (rys. nr 23).
rys. nr 23 — Błąd w zapytaniu
Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z
wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).
rys. nr 24 — Edytor zapytań z błędem
W panelu bocznym Ustawienia zapytania klikamy na Źródło w
Zastosowanych krokach. Mamy podany numer wiersza w którym był błąd, więc możemy
go łatwo znaleźć. Kiedy klikniemy na ten wiersz wyświetli nam się pod tabelą
informacja o tym błędzie (rys. nr 25).
rys. nr 25 — Informacja o błędzie
Błąd ten dotyczy maila Sebastian@yahoo.com, który wystąpił w danych
źródłowych dwa razy (dla tego samego kursu) co widać na rysunku nr 26.
rys. nr 26 — Miejsce błędu
Musimy poprawić te dane W Excelu i usunąć powtarzający się
adres mailowy, następnie klikamy prawym przyciskiem myszy na dowolną komórkę w
danych z zapytania i z podręcznego menu wybieramy polecenie Odśwież (rys. nr
27).
rys. nr 27 — Odśwież
Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.
rys. nr 28 — Prawidłowe 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 różnice pomiędzy poleceniami
Usuń inne kolumny a Usuń wybrane kolumny w Power Query. Tak naprawdę będą to
różnice pomiędzy funkcjami, które kryją się pod tymi poleceniami, czyli
odpowiednio Table.RemoveColumns oraz Table.SelectColumns. Zagadnienie to
omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Aby obrazowo wyjaśnić temat – chodzi o to, że mamy za dużo kolumn z danymi i po prostu część z nich chcemy usunąć lub chcemy usunąć wszystkie oprócz zaznaczonych. Mamy tabelę danych w Excelu, zaznaczamy w jej obszarze dowolną komórkę, a następnie wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
rys. nr 2 — Z tabeli
Otworzy nam się edytor zapytań w Power Query z wczytaną
tabelą tSprzedaż (rys. nr 3).
rys. nr 3 — Edytor zapytań
Przede wszystkim, aby dane dobrze się prezentowały musimy
zmienić format danych z kolumny Data. Klikamy na ikonkę po lewej stronie nazwy
kolumny Data a następnie z podręcznego menu wybieramy polecenie Data (rys. nr
4).
rys. nr 4 — Zmiana typu wyświetlania danych
Wyświetli się nam komunikat dotyczący zmiany typu kolumny, w
którym musimy zatwierdzić zmianę klikając przycisk Zamień bieżącą (rys. nr 5).
rys. nr 5 — Komunikat o zmianie typu kolumny
Zakładamy, że w naszym przypadku interesują nas trzy kolumny:
Data, Sprzedawca i Zysk. Zaznaczamy pozostałe kolumny (od kolumny Region do
kolumny KWS) i klikamy prawym przyciskiem myszy na nagłówek dowolnej kolumny z
tych zaznaczonych i z podręcznego menu wybieramy polecenie Usuń kolumny (rys.
nr 6)
rys. nr 6 — Usuń kolumny
Otrzymamy interesujące nas kolumny (rys. nr 7). W pasku
formuły pokaże się nam funkcja Table.RemoveColumns odpowiadająca poleceniu Usuń
kolumny.
rys. nr 7 funkcja Table.RemoveColumns
Jeśli nie mamy widocznego paska formuły możemy to łatwo
zmienić, klikając checkbox przy poleceniu Pasek formuły na karcie Widok
(zaznaczone na rysunku nr 8).
rys. nr 8 — Pasek formuły
Na pasku formuły dla funkcji Table.RemoveColumns w formule
mamy wypisane nazwy kolumn, które usunęliśmy (rys. nr 9).
rys. nr 9 — Argumenty funkcji Table.RemoveColumns
W Power Query w formułach funkcji są podawane nazwy kolumn. Wystarczy, że nazwa kolumny ulegnie zmianie i pojawiają się błędy.
Możemy usunąć ostatni krok z Zastosowanych kroków, aby wrócić do danych wejściowych. Na danych wejściowych pokażemy bowiem drugie polecenie, które moim zdaniem lepiej się sprawdza, czyli polecenie Usuń inne kolumny. Zaznaczamy kolumny, które nas interesują (które chcemy zostawić). Zaznaczamy w pierwszej kolejności kolumnę Sprzedawca, następnie przytrzymując klawisz Ctrl Kolumnę Data i Zysk. Klikamy na tytuł którejś z tych zaznaczonych kolumn i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 10).
rys. nr 10 — Usuń inne kolumny
Po użyciu tego polecenia zostały usunięte kolumny, które nie
były zaznaczone (rys. nr 11).
rys. nr 11 — Dane po usunięciu kolumn
W pasku formuły pojawi się zapis funkcji Table.SelectColumns, w której mamy wypisane nazwy kolumn, które nas interesują (rys. nr 12). Przy tej funkcji nie interesuje nas, że ktoś dołoży kolejną kolumnę do danych wejściowych. Nowa kolumna nie zostanie tutaj uwzględniona i nasze końcowe dane się nie zmienią.
rys. nr 12 — Zapis funkcji Table.SelectColumns
Bez względu na zmianę w danych wejściowych ilość
wyświetlanych kolumn się nie zmieni. Kolejnym plusem jest to, że kolumny są wyświetlane
w takiej kolejności w jakiej je zaznaczyliśmy przed użyciem polecenia Usuń inne
kolumny. Możemy również ręcznie zmienić kolejność wyświetlania kolumn w kodzie
w pasku formuły.
W Power Query, jeśli znamy nazwę interesującej nas funkcji i
wpiszemy ją w pasku formuły a następnie zatwierdzimy przyciskiem Enter, to
wyświetlą nam się informacje o tej funkcji z oficjalnej strony Microsoftu (rys.
nr 13).
Możemy się tutaj dowiedzieć, że funkcja ta posiada dodatkowy
parametr missingField, który może przyjąć jedną z trzech wartości. Będzie się
wyświetlał błąd jeśli podaliśmy złą nazwę kolumny lub zignoruje ten brak albo
wypełni je wartościami null. Poniżej mamy także podane przykłady działania tej
funkcji.
Zrobimy przykład, w którym dopiszemy do formuły
MissingField.UseNull i zmienimy nazwy kolumn Data i Zysk na Data2 i Zysk2,
czyli na takie które nie występują w danych. Power Query wypełni je wartościami
null (rys. nr 14).
rys. nr 14 — Dane wypełnione wartościami null
Jeśli do zapisu formuły użyjemy MissingField.Ignore to
zostanie nam tylko kolumna ze Sprzedawcami. Jeśli nie podamy tego parametru, to
Power Query domyślnie wybierze wyświetlenie błędu (rys. nr 15).
rys. nr 15 — Domyślny komunikat jeśli nie podamy parametru MissingField
Ja wole funkcje Table.SelectColumns czyli usunięcie innych
kolumn a pozostawienie tych, które są mi potrzebne. Przede wszystkim dlatego,
że dodawanie innych kolumn nie wpłynie na wynik tej funkcji. Łatwiej jest
korzystać z tych domyślnych opcji na pasku narzędzi niż z kodu w pasku formuły,
ponieważ przy słabej znajomości języka M w Power Query możemy nie wiedzieć jak
wpisać poszczególne parametry danej funkcji.
Wynik tej funkcji z rysunku nr 11 możemy sobie załadować do
Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr
16).
rys. nr 16 — Zamknij i załaduj
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr
17.
rys. nr 17 — Dane wczytane do Excela
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.
