W
dzisiejszym poście omówimy numerowanie poszczególnych elementów danej kategorii
sprzedaży. Rozwiązanie takiego problemu w Excelu omówiliśmy w poradzie 373.
Dziś zajmiemy się rozwiązaniem tego problemu w Power Query. Przykładowe dane do
zadania zostały przedstawione na rys. nr 1.
Rys. nr 1 – przykładowe dane do zadania
Rozwiązanie,
które omówimy opiera się na pomyśle Gena Pulsa (www.excelguru.ce). Zacznijmy od pobrania naszych
danych do zadania. Wybieramy polecenie Z tabeli/ zakresu z karty Dane
(rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się edytor zapytań Power Query z wczytaną tabelą z Excela przedstawiony na rys.
nr 3.
Rys. nr 3 – edytor zapytań z wczytaną tabelą
We wczytanych
danych musimy zrobić drobną korektę. Mianowicie zmienić typ danych w kolumnie
Data, czyli klikamy ikonę kalendarza w tytule kolumny i wybieramy z podręcznego
menu polecenie Data (rys. nr 4).
Rys. nr 4 – zmiana typu danych na Datę
Otworzy nam
się okno Zmiany typu kolumny, gdzie musimy zatwierdzić naszą decyzję za pomocą
przycisku Zamień bieżącą (rys. nr 5).
Rys. nr 5 – okno Zmiany typu kolumny
Naszym celem
jest ponumerowanie elementów poszczególnych kategorii, ale pod warunkiem
zachowania aktualnej kolejności elementów. Z tego powodu w pierwszej kolejności
musimy dodać kolumnę indeksu. W tym celu rozwijamy polecenie KolumnaIndeksu
(punkt 2 na rys. nr 6) z karty Dodaj kolumnę (punkt 1), a następnie
wybieramy polecenie Od 0 (punkt 3).
Rys. nr 6 – ścieżka dostępu do polecenia Od 0 (kolumna indeksu)
Otrzymamy
dane z nową kolumną (kolumna indeksu), która sprawi, że nasze dane zachowają
swoją kolejność (rys. nr 7).
Rys. nr 7 – Dane z dodaną kolumną indeksu
Będziemy
potrzebować paska formuły w dalszych działaniach. Jeśli nie macie go
włączonego, można go uruchomić za pomocą polecenia Pasek formuły na
karcie Widok (oznaczone strzałką na rys. nr 8).
Rys. nr 8 – uruchomienie paska formuły
Dodatkowo
możemy zmienić nazwę nowej kolumny w pasku formuły, żeby nie dodawać kolejnego
kroku do Zastosowanych kroków. Zmieniamy nazwę nowej kolumny na IndeksTmp (rys.
nr 9), żebyśmy wiedzieli że jest on tymczasowy i służy tylko do zachowania
oryginalnej kolejności elementów w tabeli.
Rys. nr 9 – zmiana nazwy nowej kolumny w pasku formuły
W następnym
kroku musimy pogrupować dane po kategorii, czyli wybieramy polecenie Grupowanie
według z karty Narzędzia główne (rys. nr 10).
Rys. nr 10 – polecenie Grupowanie według
Otworzy nam
się okno Grupowania według, gdzie w polu Grupuj według wybieramy grupowanie
według Kategorii (punkt 1 na rys. nr 11), następnie ustawiamy rodzaj Operacji
jako Wszystkie wiersze (punkt 2). Ponadto możemy zmienić nazwę kolumny na TMP
(punkt 3), ponieważ znowu jest to kolumna tymczasowa i w następnych etapach
będziemy ją modyfikować. Tak ustawione parametry grupowania zatwierdzamy
przyciskiem OK.
Rys. nr 11 – parametry grupowania według
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12. Istotne jest, że mamy podział na
4 główne grupy produktów i w kolumnie TMP mamy tabele z poszczególnymi
elementami danej grupy. Warto zaznaczyć, że są to tabele ze wszystkimi
kolumnami z naszej oryginalnej tabeli z zachowaniem kolejności z kolumny
IndeksTmp.
Rys. nr 12 – Pogrupowane dane
Naszym
zadaniem teraz jest dołożenie kolejnej kolumny indeks w tych wewnętrznych
tabelach z kolumny TMP. Dodajemy nową kolumnę indeks, czyli rozwijamy polecenie
Kolumna indeksu z karty Dodaj kolumnę, a następnie wybieramy
polecenie Od 1 (tym razem numerowanie zaczynamy od 1) jak na rys. nr 13.
Rys. nr 13 – polecenie Od 1 (kolumna indeks)
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 14.
Rys. nr 14 – Dane z nową kolumną Indeks
Dzięki
dodaniu tej kolumny w pasku formuły mamy gotowy kod na dodanie kolumny indeksu
(rys. nr 15).
Rys. nr 15 – kod na dodanie kolumny indeks numerowanej od 1
Kopiujemy
ten kod za pomocą skrótu klawiszowego Ctrl+C i usuwamy dodatkowy krok (Dodano
indeks 1), ponieważ był nam potrzebny tylko do otrzymania kodu. Krok usuwamy za
pomocą oznaczenia x przed nazwą danego kroku (rys. nr 16).
Rys. nr 16 – usuwanie kroku
Następnie
wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę
(rys. nr 17).
Rys. nr 17 – polecenie Kolumna niestandardowa
Otworzy nam
się okno Kolumny niestandardowej, gdzie w polu Formuła kolumny niestandardowej
wklejamy nasz skopiowany wcześniej kod (z paska formuły) za pomocą skrótu
klawiszowego Ctrl+V. W formule tej musimy zmienić jedną rzecz ponieważ ta
formuła opierała się na wcześniejszym kroku, czyli na pogrupowaniu wierszy).
Naszym celem jest dodanie kolumny indeksu do tabeli, która znajduje się w
kolumnie TMP. Czyli kasujemy z zapisu formuły nazwę kroku a wstawiamy nazwę
kolumny. Zapis formuły powinien wyglądać następująco:
=Table.AddIndexColumn([TMP],
"Indeks", 1, 1)
Tak
ustawione parametry kolumny niestandardowej zatwierdzamy przyciskiem OK (rys.
nr 18).
Rys. nr 18 – parametry kolumny niestandardowej
Otrzymamy
dane z nową kolumną niestandardową przedstawione na rys. nr 19, gdzie w
kolumnie są ukryte tabele z poprzedniej kolumny ale z dodatkową kolumną indeksu
(ponumerowane elementy w każdej kategorii).
Rys. nr 19 – dane z nową kolumną niestandardową
Możemy teraz
usunąć dwie pierwsze kolumny, ponieważ one nic nam nie wnoszą do wyników,
jakich potrzebujemy. Zaznaczamy dwie kolumny Kategoria i TMP przytrzymując
klawisz Ctrl, następnie usuwamy je za pomocą klawisza Delete. Otrzymamy kolumnę
Niestandardowe, którą będziemy musieli rozwinąć. W tym celu klikamy ikonkę ze
strzałkami obok tytułu kolumny i odznaczamy checkbox przy opcji Użyj
oryginalnej nazwy kolumny jako indeksu. Parametry rozwinięcia zatwierdzamy
przyciskiem OK (rys. nr 20).
Rys. nr 20 – Rozwijanie kolumny niestandardowej
Otrzymamy
rozwinięte dane przedstawione na rys. nr 21.
Rys. nr 21 – rozwinięte dane z kolumny niestandardowej
Przez to że
pogrupowaliśmy dane po kategoriach mamy najpierw wszystkie myszki, potem
klawiatury itd. Pamiętajmy, że dodaliśmy kolumnę IndeksTmp po to, aby móc
wrócić do oryginalnej kolejności elementów. Wystarczy teraz zanzcayć kolumnę
IndeksTmp i wybrać polecenie sortuj od A do Z z karty Narzędzia
główne (rys. nr 22).
Rys. nr 22 – polecenie Sortuj od A do Z
Otrzymamy
dane posortowane według pierwotnej kolejności przedstawione na rys. nr 23.
Rys. nr 23 – posortowane dane
Kolumna
IndeksTmp jest nam już niepotrzebna, więc zaznaczamy ją i usuwamy za pomocą
klawisza Delete. Otrzymamy dane z numerowaniem (indeksem) elementów odpowiednio
w kategoriach przedstawione na rys. nr 24.
Rys. nr 24 – Dane z numerowaniem elementów poszczególnych kategorii
Tak
przygotowane dane możemy zaczytać do Excela. W tym celu wybieramy polecenie Zamknij
i załaduj do z karty Narzędzia główne (rys. nr 25).
Rys. nr 25 – polecenie Zamknij i załaduj do
W Excelu
otworzy nam się okno Importowania danych, gdzie ustawiamy sposób wyświetlania
danych jako Tabela, a następnie wskazujemy miejsce wstawienia danych, czyli
istniejący arkusz i wskazujemy konkretną
komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 26).
Rys. nr 26 – parametry importowania danych do Excela
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 27.
Rys. nr 27 – dane zaimportowane z Power Query
Musimy
pamiętać o odpowiednim formatowaniu danych, ponieważ w kolumnie Data
otrzymaliśmy liczby zamiast dat. Zaznaczamy kolumnę Data i na karcie Narzędzia
główne możemy zmienić formatowanie w kategorii Liczba na Data krótka
(rys. nr 28).
Rys. nr 28 – zmiana formatowania na Datę krótką
Otrzymamy
końcowe dane, odpowiednio sformatowane a przede wszystkim z numeracją
poszczególnych elementów danych kategorii (rys. nr 29).
Rys. nr 29 – Dane z numeracją elementów poszczególnych kategorii
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W
dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany
element jest na liście. W przykładowych danych przedstawionych na rys. nr 1
mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się
dany sprzedawca, to z tej listy chcemy pobierać dane.
Rys. nr 1 – przykładowe dane
Zakładamy,
że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie
stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z
tabel pobrać do Power Query. Możemy to zrobić wybierając polecenie Z
tabeli/zakresu z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się edytor zapytań Power Query z wczytaną tabelą pobraną z Excela przedstawioną
na rys. nr 3.
Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi
W
analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w
obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu z karty Dane (jak na rys. nr 2).
Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).
Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)
Możemy
przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=.
Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie
kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z
kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 5).
Rys. nr 5 – polecenie Kolumna warunkowa
Otworzy nam
się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży
nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo
będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy
dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny
region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy
rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy
domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę
dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4),
natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i
wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i
w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy
przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.
W drugiej
regule w polu Nazwa kolumny wybieramy kolumnę Sprzedawca, pole Operator
zostaje bez zmian, w polu Wartość wpisujemy Dominik, następnie zmieniamy typ Wartości
wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy
kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej
osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a
później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla
dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null
(oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej
zatwierdzamy przyciskiem OK.
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region
stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji
JEŻELI.
Rys. nr 8 – Dane z nową kolumną warunkową
W zapisie
formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do
konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca
jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains
(rys. nr 9).
Rys. nr 9 – zapis formuły kolumny warunkowej
Funkcja
List.Contains potrzebuje jako pierwszego argumentu listy wszystkich
sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie
sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego
wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową.
Zapis formuły powinien wyglądać następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains
({"Aleksandra", "Robert"}, [Sprzedawca]) then
[Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie]
else null))
Zmieniliśmy
tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły
otrzymamy zmienione dane przedstawione na rys. nr 10.
Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej
Ten sposób
jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich
sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z
danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na
województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego
zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy
poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał
następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains (tRegiony_k
[Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if
[Sprzedawca]="Dominik" then [Mazowieckie] else null))
Rys nr 11 – odwołanie do kolumny Świętokrzyskie z zapytania tRegiony_k
Po
zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z
regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej
reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą
regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains (tRegiony_k
[Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains
(tRegiony_k [Mazowieckie] then [Mazowieckie] else null))
Power Query
przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys.
nr 12.
Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains
W danych
mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list.
Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się
tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną
spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym
razie na rys. nr 7).
Tak
przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i
załaduj do z karty Narzędzia
główne (rys. nr 13).
Rys. nr 13 – polecenie 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 – konkretny arkusz oraz wskazujemy
konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy
przyciskiem OK (rys. nr 14).
Rys. nr 14 – okno Importowania danych
Otrzymamy
dane zaimportowane do Excela i przedstawione na rys. nr 15.
Rys. nr 15 – Dane zaimportowane do Excela
Jak widać na
rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej
Główny region.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W
dzisiejszym poście omówimy usuwanie części adresów, czyli np. nazw ulic czy
placów. Rozwiążemy to zadanie w Power Query. To samo zagadnienie omawialiśmy w
poradzie 369, ale za pomocą Excela. Zagadnienie to omówimy na podstawie
przykładowych danych przedstawionych na rys. nr 1.
Rys. nr 1 – Przykładowe dane do zadania
Przykładowe
dane musimy zaczytać do Power Query za pomocą polecenia Z tabeli/ zakresu
z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się Edytor zapytań Power Query z wczytaną tabelą z adresami, następnie na tych
danych wykonamy przekształcenia, aby otrzymać pożądany efekt. Przekształcenia,
które wykonywaliśmy ręcznie w Excelu, czyli zamiana poszczególnych nazw ulic,
alei i placów na ulice (ujednolicenie danych), w Power Query możemy zapisać
jako kroki. Nawet kiedy nasze dane się zmienią, Power Query znów zamieni te
instancje na ulice i będziemy mogli wyciągnąć tylko miasto z naszych danych.
Zaznaczamy
kolumnę Miasto, Adres i wybieramy polecenie Zamienianie wartości z karty
Narzędzia główne (rys. nr 3).
Rys. nr 3 – polecenie Zamienianie wartości
Polecenia
tego musimy użyć dwukrotnie. Otworzy nam się okno Zamieniania wartości, gdzie w
polu Wartość do znalezienia wpisujemy pl. (oznaczenie placu w adresie), następnie
w polu Zamień na wpisujemy ul. (oznaczenie ulicy). Tak ustawione parametry
zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 4).
Rys. nr 4 – okno Zamieniania wartości
Otrzymamy
dane, gdzie wszystkie wystąpienia skrótu pl. zostały zamienione na ul.
Następnie musimy powtórzyć te czynności, czyli wybieramy polecenie Zamienianie
wartości z karty Narzędzia główne. Otworzy nam się okno Zamieniania
wartości, gdzie znowu musimy ustawić jego parametry. Tym razem w polu Wartość
do znalezienia wpisujemy al. (oznaczenie alei), a w polu Zamień na wpisujemy
ul. (analogicznie jak na rys. nr 4).
Otrzymamy
zamienione dane przedstawione na rys. nr 5.
Rys. nr 5 – Zamienione dane
Otrzymaliśmy
dane, gdzie różne określenia adresów zostały ujednolicone, zamienione na skrót
od ulicy (ul.). Teraz wystarczy wyciągnąć z danych tekst przed ulicą. W tym celu rozwijamy polecenie Wyodrębnij (punkt
2 na rys. nr 6) z karty Przekształć (punkt 1), a następnie wybieramy
polecenie Tekst przed ogranicznikiem (punkt 3).
Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem
Otworzy nam
się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik wpisujemy skrót
ul. (ujednolicone oznaczenie ulicy w adresie). Tak ustawiony ogranicznik
zatwierdzamy przyciskiem OK (rys. nr 7).
Rys. nr 7 – okno Testu przed ogranicznikiem
Otrzymamy
wyodrębnione dane z nazwami miast, po usunięciu niepotrzebnego tekstu
przedstawioną na rys. nr 8.
Rys. nr 8 – Wyodrębnione dane
Musimy
pamiętać, że mieliśmy takie adresy, gdzie po nazwie miast była spacja, a
dopiero potem nazwa ulicy, jak również sytuacje, kiedy tej spacji nie było w
ogóle. Na końcu nazw niektórych miast pozostały spacje, które są nam niepotrzebne
(mimo że ich nie widać). W tym celu rozwijamy polecenie Format (punkt 2
na rys. nr 9) z karty Przekształć (punkt 1), a następnie wybieramy
polecenie Przycięcie (punkt 3).
Rys. nr 9 – ścieżka dostępu do polecenia Przycięcie
Polecenie
Przycięcie usuwa zbędne spacje na końcu tekstu. Otrzymamy przycięte dane
przedstawione na rys. nr 10.
Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)
Możemy
zrobić jeszcze jedno przekształcenie, mianowicie przy dwuczłonowych nazwach
miast powinien być myślnik pomiędzy słowami (Skarżysko – Kamienna). W tym celu
wybieramy polecenie Zamienianie wartości z karty Narzędzia główne
(jak na rys. nr 3). Otworzy nam się okno Zamieniania wartości, gdzie w polu
Wartość do znalezienia wpisujemy spację, a w polu Zamień na wpisujemy myślnik
(-). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK
(rys. nr 11).
Rys. nr 11 – okno Zamieniania wartości
Otrzymamy
zmienione dane. W kolejnym kroku powinniśmy zmienić nazwę kolumny, ponieważ po
przekształceniach zawiera ona tylko nazwę miasta. Klikamy dwukrotnie na tytuł
kolumny i wpisujemy nową nazwę, czyli Miasto (rys. nr 12).
Rys. nr 12 – Zmiana nazwy kolumny
Otrzymamy
dane przedstawione na rys. nr 13.
Rys. nr 13 – Dane po przekształceniach
Po tych
wszystkich krokach przekształceń, powinniśmy otrzymać te same wyniki, co w
poradzie nr 369. Dzięki temu, że zadanie to wykonaliśmy w Power Query i mamy
zapisane poszczególne kroki to w sytuacji, kiedy zmieniłyby nam się dane, wystarczy
odświeżyć dane aby otrzymać aktualne, poprawne wyniki. Tak przygotowane dane
możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z
karty Narzędzia główne (rys. nr 14).
Rys. nr 14 – polecenie Zamknij i załaduj do
W Excelu
otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania
danych jako Tabel i wskazujemy miejsce wstawienia danych – istniejący arkusz
oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych
zatwierdzamy przyciskiem OK (rys. nr 15).
Rys. nr 15 – okno Importowania danych
Otrzymamy
dane wstawione do Excela z wyciągniętymi nazwami miast z całych adresów
przedstawione na rys. nr 16.
Rys. nr 16 – Dane wstawione do Excela
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście
zajmiemy się usuwaniem pustych kolumn w Power Query. Do mojego kursu na Udemy
dostałem zapytanie czy jest możliwe szybkie usunięcie pustych kolumn z danych.
W tego typu sytuacjach sam szukam odpowiedzi w gogle. Rozwiązanie znalazłem na
stronie www.community.powerbi.com, mianowicie kod funkcji, którą
będziemy mogli wykorzystać w naszym zadaniu (rys. nr 1).
Rys. nr 1 – kod funkcji do usuwania pustych kolumn
Kopiujemy
funkcję za pomocą skrótu klawiszowego Ctrl+C, a następnie wklejamy za pomocą
skrótu Ctrl+V do Notepada ++, gdzie możemy podejrzeć elementy funkcji w
kolorach (rys. nr 2).
Rys. nr 2 – formuła funkcji do usuwania pustych kolumn w Notepadzie ++
Co istotne w
tym zapisie – funkcja pobiera nam dane jako tabelę i usuwa puste kolumny z
danych. Pobranej funkcji możemy użyć w Power Query. Załóżmy, że mamy
przykładowe dane pokazane na rys. nr 3. Wynika z nich że zawierają one dużo
pustych kolumn.
Rys. nr 3 – Przykładowe dane
Rozwijamy
polecenie Pobierz dane (punkt 2 na rys. nr 4) z karty Dane (punkt
1), następnie rozwijamy polecenie Z innych źródeł (punkt 3) i wybieramy
polecenie Pustezapytanie (punkt 4).
Rys. nr 4 – ścieżka dostępu do polecenia Puste zapytanie
Otworzy nam
się pusty Edytor zapytań Power Query, gdzie musimy wkleić nasz kod M, czyli
zapis funkcji skopiowany z Notepada++. Wybieramy polecenie Edytor
zaawansowany z karty Narzędzia główne (rys. nr 5).
Rys. nr 5 – polecenie Edytor zaawansowany
Otworzy nam
się okno Edytora zaawansowanego, gdzie wklejamy za pomocą skrótu klawiszowego
Ctrl+V naszą skopiowaną wcześniej funkcję (rys. nr 6). Zapis powinien wyglądać
następująco:
(tbl as table) =>
let
Headers
= Table.ColumnNames(tbl),
Result
= Table.SelectColumns(tbl),
List.Select(Headers,
each List.MatchesAny(Table.Column(tbl,_),
each_
<> null)))
in
Result
Rys. nr 6 – Edytor zaawansowany z zapisem funkcji
Wklejoną
funkcję zatwierdzamy w Edytorze zaawansowanym przyciskiem Gotowe. Otrzymamy
gotową funkcję przedstawioną na rys. nr 7.
Rys. nr 7 – Funkcja w Power Query
Nie ma znaczenia,
że zapis tej funkcji był w języku angielskim. Istotne jest, że ta funkcja
pobiera tabelę i odpowiednio ją przekształca. Nasze zapytanie nazywa się
Zapytanie 1, więc zmienimy jego nazwę w Ustawieniach zapytania na
DeleteEmptyColumns, czyli Usuń puste kolumny. Co istotne nasze zapytanie ma
postać funkcji co widać przy nazwie zapytania – skrót fx (rys. nr 8).
Rys. nr 8 – Zapytanie w postaci funkcji – oznaczenie fx
Sposób,
którego używamy jest najprostszym sposobem użycia tej funkcji, czyli zaczytanie
pustego zapytania i wstawienie do niego funkcji, następnie zaczytanie danych z
Excela w formie tabeli do drugiego zapytania. Nie wychodząc z Power Query
możemy rozwinąć sobie polecenie Nowe źródło (punkt 1 na rys. nr 9) na
karcie Narzędzia główne, następnie rozwinąć polecenie Plik (punkt
2) i wybrać polecenie Excel (punkt 3).
Rys. nr 9 – ścieżka dostępu do polecenia Excel
Otworzy nam
się okno Importowania danych, w którym musimy znaleźć plik z naszymi
przykładowymi danymi z rys. nr 3. Wybieramy plik i klikamy na niego dwukrotnie
lub zatwierdzamy nasz wybór przyciskiem Otwórz (rys. nr 10).
Rys. nr 10 – Okno Importowania danych
Otworzy nam
się okno Nawigatora, gdzie na liście elementów wybieramy jeden arkusz, ten
który chcemy przekształcić – o nazwie Dane. Nasz wybór zatwierdzamy przyciskiem
OK (rys. nr 11).
Rys. nr 11 – okno Nawigator
Arkusz ten
zostanie zaczytany do Power Query jako odrębne zapytanie co widać na rys. nr 12.
Rys. nr 12 – Arkusz zaczytany jako odrębne zapytanie
Dodatkowo
możemy usunąć krok Zmieniono typ z Zastosowanych kroków (Ustawienia zapytania),
ponieważ krok ten nic nie wnosi do naszych danych.
Aby usunąć
puste kolumny wystarczy przejść na zapytanie z naszą funkcją i w polu tbl
(Wprowadź parametr) wybrać naszą zaczytaną tabelę z drugiego zapytania. Wybór
tabeli zatwierdzamy przyciskiem Wywołaj (rys. nr 13).
Rys. nr 13 – Wprowadzanie parametru funkcji (wybór tabeli z drugiego zapytania)
W wyniku
zadziałania funkcji otrzymamy dane w formie tabeli, z których zostały usunięte
wszystkie puste kolumny (rys. nr 14).
Rys. nr 14 – Dane w formie tabeli po usunięciu pustych kolumn
Co istotne
do poprawnego zadziałania tej funkcji musi być całkowicie pusta kolumna (np.
kolumna nr 7 ma test w przedostatnim wierszu i już nie została usunięta). Po
nagłówkach kolumn doskonale widać jak dużo kolumn zostało usuniętych.
Podsumowując
pokazaliśmy sposób usunięcia pustych kolumn w Power Query znaleziony w
internecie. Jeśli jesteś bardziej zaawansowanym użytkownikiem możesz zaczytać
dane do Power Query i wkleić tą funkcję pod razu w jednym zapytaniu. Innym
sposobem wywołania funkcji jest polecenie Wywołajfunkcjęniestandardową
z karty Dodaj kolumnę (rys. nr 15).
Rys. nr 15 – polecenie Wywołaj funkcję niestandardową
Tak
przygotowane dane musimy załadować do Excela, w tym celu wybieramy polecenie Zamknij
i załaduj z karty Narzędzia główne (rys. nr 16).
Rys. nr 16 – polecenie Zamknij i załaduj
Nie
przekształcaliśmy dodatkowo danych, zależało nam jedynie na usunięciu pustych
kolumn z danych. Zapytania zostały załadowane do Excela jako połączenia (ze
względu na to, że mamy kilka zapytań) – rys. nr 17.
Rys. nr 17 – Zapytania załadowane do Excela jako połączenia
Zaznaczamy
zapytanie Wywołano funkcję i klikamy na nie prawym przyciskiem myszy, następnie
z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 18).
Rys. nr 18 – polecenie Załaduj do
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Tabela i wskazujemy miejsce wstawienia danych, czyli Istniejący arkusz oraz
wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych
zatwierdzamy przyciskiem OK (rys. nr 19).
Rys. nr 19 – okno Importowania danych
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 20.
Rys. nr 20 – Dane zaczytane do Excela
Tak
przygotowane dane wymagają jeszcze obróbki, mianowicie uporządkowania ich,
usunięcia zbędnych wierszy i zbędnych informacji. Najważniejsze że udało nam
się wykonać pierwszy krok, czyli automatyczne usuwanie niepotrzebnych kolumn za
pomocą funkcji znalezionej w internecie.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
