W dzisiejszym poście nauczymy się jak załadować ponad milion
wierszy do tabeli przestawnej, czyli ponad 1048576 wierszy, które mieszczą się
w arkuszu Excela. Będziemy używać do tego zadania dodatku do Excela – Power
Query.
Dla ułatwienia dane pobierzemy sobie z pliku tekstowego,
czyli wybieramy polecenie Z pliku tekstowego /CSV z karty Dane (rys. nr 1).
rys. nr 1 — Z pliku tekstowego/ CSV
Otworzy nam się okno, w którym musimy znaleźć lokalizację
pliku (danych bazowych), jaki chcemy zaimportować do Power Query. Wybieramy
plik a następnie nasz wybór zatwierdzamy przyciskiem Importuj (rys. nr 2).
rys. nr 2 — Wybór źródła danych
Otworzy nam się okno z danymi Sprzedaż zagranicznak.csv z
danymi, które będziemy chcieli edytować w Power Query, więc klikamy przycisk
Edytuj (rys. nr 3).
rys. nr 3 — Edycja pliku CSV
Otworzy nam się Edytor zapytań Power Query, w którym chcemy
sprawdzić czy wszystkie dane zostały załadowane poprawnie. Kolumna Kod pocztowy
jest błędna, ponieważ Power Query źle rozpoznaje dane (np. dla kodu z wartością
0 z przodu Power Query pomija tą wartość 0 i otrzymujemy kod 4‐ cyfrowy).
Wystarczy zmienić formatowanie tej kolumny. Klikamy na ikonkę 123 przy nazwie
kolumny Kod pocztowy, a następnie z podręcznego menu wybrać formatowanie Tekst
(rys. nr 4).
rys. nr 4 — Zmiana formatu danych
Pojawi się komunikat, w którym musimy zatwierdzić zmianę typu
kolumny za pomocą przycisku Zamień bieżącą (rys. nr 5).
rys. nr 5 — Zmień typ kolumny
W kolejnym etapie wybieramy polecenie Zamknij i załaduj do z
karty Narzędzia główne (rys. nr 6).
rys. nr 6 — Zamknij i załaduj do
Otworzy nam się okno Importowania danych. Jeśli posiadasz
subskrypcję Excela 365 w tym okienku znajduje się bezpośrednia opcja, aby
załadować dane jako Raport w formie tabeli przestawnej (rys. nr 7).
rys. nr 7 — Parametry importowania danych
Jeśli posiadasz starszą wersję pakietu Office to wybieramy
polecenie Utwórz tylko połączenie, następnie nasz wybór zatwierdzamy
przyciskiem OK (rys. nr 8).
rys. nr 8 — Utwórz tylko połączenie
W Excelu w bocznym panelu stworzyło się zapytanie Power Query,
tylko w formie połączenia (rys. nr 9).
rys. nr 9 — Zapytanie Power Query w panelu bocznym
Na tym etapie musimy ręcznie wstawić sobie tabelę przestawną.
Klikamy polecenie Tabela przestawna z karty Wstawianie (rys. nr 10).
rys. nr 10 — Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej. W polu
wybierz dane, które chcesz analizować, zaznaczamy opcję Użyj zewnętrznego
źródła danych, następnie klikamy przycisk Wybierz połączenie (rys. nr 11).
rys. nr 11 — Okno tworzenia tabeli przestawnej
Otworzy nam się okno z Istniejącymi połączeniami. Zaznaczamy
połączenie w tym arkuszu Zapytanie – Sprzedaż Zagranicznak, a następnie klikamy
przycisk Otwórz (rys. nr 12).
rys. nr 12 — Istniejące połączenia
Następnie w oknie Tworzenie tabeli przestawnej z rysunku nr 11,
wybieramy miejsce wstawienia naszej tabeli (Istniejący arkusz i konkretna
komórka $A$1) i tak ustawione parametry zatwierdzamy przyciskiem OK.
Mamy dużą ilość danych, więc musimy trochę poczekać, ponadto
Excel robi sobie kopię zapytania, co dodatkowo wydłuża proces przetwarzania
danych.
Otrzymamy w Excelu pustą tabelę, przestawną którą stworzymy
przeciągając odpowiednie dane do pól etykiet wierszy i kolumn (rys. nr 13).
rys. nr 13 — Pusta tabela przestawna
Przeciągamy pola Miasto i Kraj do obszaru etykiet wierszy, a
następnie przeciągamy pole Przychód do obszaru Sumy wartości (rys. nr 14).
rys. nr 14 — Przeciąganie danych do obszaru etykiet
Otrzymamy tabelę przestawną przedstawioną na rysunku nr 15.
rys. nr 15 — Tabla przestawna
Możemy sobie podejrzeć ilość załadowanych wierszy w panelu
Zapytania i połączenia. W tym arkuszu zostało stworzonych 2 501 580
wierszy (rys. nr 16).
rys. nr 16 — ilość wierszy
Klikamy skrót klawiszowy Ctrl+S, aby zapisać nasze dane.
Skopiowane zapytanie Sprzedaż zagraniczna (2) zostało stworzone tylko pod tą
tabelę przestawną. Połączenie źródłowe możemy dalej wykorzystywać do innych
zadań.
Ciekawostką jest, że plik w Excelu dotyczący tego odcinka
zajmuje niecałe 30 Mb, w porównaniu do 130 Mb danych w pliku źródłowym csv.
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ę wyszukiwaniem najdłuższego
czasu operacji oraz opisu tej konkretnej operacji. Zakładamy, że mamy linię
produkcyjną, na której są wykonywane pewne czynności. Naszym zadaniem jest
znalezienie operacji, która zajmuje najwięcej czasu, a następnie uzyskanie
opisu nazwy tej operacji. Dodatkowym warunkiem tego zadania jest opcja, że
najdłuższy czas operacji może się powtórzyć – uzyskamy wtedy np. dwie operacje
w jednym wierszu. Zadanie to wykonamy w Power Query na podstawie przykładowych
danych przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Pierwszym krokiem jest zaczytanie danych do Power Query, w
tym celu wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy nam się edytor zapytań z zaczytaną tabelą z danymi
produkcyjnymi (rys. nr 3).
rys. nr 3 — Edytor zapytań
Zaznaczamy kolumnę Produkt ID a następnie wybieramy polecenie
Grupowanie według z karty Narzędzia główne (rys. nr 4).
rys. nr 4 — Grupowanie według
Otworzy
nam się okno Grupowania według, gdzie możemy ustalić parametry grupowania. Dane
grupujemy według kolumny Produkt ID. W polu nazwa nowej kolumny wpisujemy nazwę
Najdłuższy czas, a następnie w polu Operacja wybieramy Maksimum. W polu Kolumna
wybieramy kolumnę Czas bo na tej kolumnie mają się opierać nasze wyniki. Tak
przygotowane parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 5).
rys. nr 5 — Parametry grupowania
Otrzymamy pogrupowane dane dla każdego ID produktu z
maksymalnym czasem trwania operacji przedstawione na rysunku nr 6.
rys. nr 6 — Pogrupowane dane
Otrzymaliśmy kolumnę z numerem ID produktów i najdłuższy czas
trwania operacji. Potrzebujemy teraz wyciągnąć informację z nazwą tych
operacji. Z założenia opis ten pokazany jest w danych krok wcześniej w
zastosowanych krokach. Możemy scalić zapytanie z tym samym zapytaniem lecz z
innym krokiem. Rozwijamy polecenie Połącz (punkt 2 na rysunku nr 7) z karty Narzędzia
główne, a następnie wybieramy polecenie Scal zapytania (punkt 3).
rys. nr 7 — Scal zapytania
Otworzy nam się okno Scalania danych. Dla tabeli tProdukcja
(2) na górze okna zaznaczamy obie kolumny, czyli Produkt ID oraz Najdłuższy czas. W drugim
polu również wybieramy tabelę tProdukcja (2) (bieżący), a następnie w tej samej
kolejności co powyżej zaznaczamy obie kolumny z danymi. W polu Rodzaj
sprzężenia wybieramy rodzaj operacji, czyli Lewe zewnętrzne (wszystkie z
pierwszej, pasujące z drugiej). Tak przygotowane parametry zatwierdzamy
przyciskiem OK (rys. nr 8).
rys. nr 8 — Parametry scalania
Otrzymujemy dane przedstawione na rysunku nr 9, gdzie pod
słowem Table ukryta jest tabela z danymi (wynik porównania dwóch tabel).
rys. nr 9 — Scalone dane
Tabelki te zawierają tylko jeden wiersz ponieważ grupowanie wykonaliśmy
po ostatnim kroku zapytania, czyli Pogrupowano wiersze. Naszym zadaniem jest
scalenie danych z kroków Pogrupowano wiersze i Zmieniono typ. Klikamy na nazwę
kroku Zmieniono typ, następnie przechodzimy w tryb edycji tekstu za pomocą
klawisza F2 i kopiujemy nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C.
Sprawdzamy czy mamy widoczny pasek formuły. Jeśli nie to
możemy go włączyć na karcie Widok klikając checkbox przy poleceniu Pasek
formuły.
Będziemy teraz wprowadzać zmiany zapisie naszej formuły
scalania w pasku formuły. Pierwszy krok się zgadza, czyli Pogrupowano wiersze.
Drugi krok jest taki sam, więc musimy zaznaczyć nazwę kroku (zaznaczone na
niebiesko na rysunku nr 10), a następnie wkleić nazwę skopiowanego wcześniej
zapytania (Zmieniono typ) za pomocą skrótu klawiszowego Ctrl+V.
rys. nr 10 — Zmiany w formule
Jeśli teraz zatwierdzimy naszą zmianę klawiszem Enter Power
Query zwróci nam błąd ponieważ w kroku Zmieniono typ nie było kolumny
Najdłuższy czas (rys. nr 11).
rys. nr 11 — Error
W kroku zmieniono typ mamy natomiast kolumnę Czas [s], której
możemy użyć zamiennie. Wystarczy zmienić w zapisie formuły dla tego kroku nazwę
Najdłuższy czas na Czas [s].
Otrzymamy dane przedstawione na rysunku nr 12. Mamy tutaj sytuację, że dwie operacje mają taki sam czas.
rys. nr 12 — dwie operacje z takim samym czasem
Gdybyśmy nie mieli duplikatów w danych, wystarczyło by teraz rozwinąć
kolumnę Pogrupowano wiersze po kolumnie Opis operacji (jak pokazano na rys. nr
13).
rys. nr 13 — Rozwiń
Otrzymalibyśmy wtedy dane przedstawione na rysunku nr 14.
rys. nr 14 — Dane po rozwinięciu
Mamy tutaj dwa wiersze z tym samym czasem, a nam zależy aby
były one w jednym wierszu. Usuwamy ostatni krok jaki wykonaliśmy, czyli wracamy
do danych z rysunku nr 12.
W kolumnie Pogrupowano dane mamy dane w formie tabeli. Musimy
dodać kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 15).
rys. nr 15 — Kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej. Zmieniamy nazwę
nowej kolumny na Opis operacji, a następnie wpisujemy formułę tej kolumny.
Będzie się ona opierać na funkcji Table.Column. Pierwszy argument funkcji to
miejsce gdzie jest nasza tabela, czyli kolumna Pogrupowano wiersze. Drugi
argument to nazwa kolumny, którą chcemy
wyciągnąć, czyli kolumna Opis operacji (rys. nr 16). Zapis będzie wyglądał
następująco:
Powyższe parametry kolumny niestandardowej zatwierdzamy
przyciskiem OK.
rys. nr 15 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 17. Dla pierwszego
wiersza pod List ukryte są dwie wartości – dwie nazwy operacji.
rys. nr 17 — Dane z nową kolumną
W kolejnym kroku usuwamy kolumnę Pogrupowano wiersze. Klikamy
na jej nagłówek prawym przyciskiem myszy, a następnie z podręcznego menu
wybieramy polecenie Usuń (rys. nr 18).
rys. nr 18 — Usuń kolumnę
Następnie klikamy ikonkę strzałek przy nazwie kolumny Opis
operacji i wybieramy polecenie Wyodrębnij wartości (rys. nr 19).
rys. nr 19 — Wyodrębnij wartości
Otworzy nam się okno wyodrębniania wartości z listy, gdzie w
polu Wybierz ogranicznik, który zostanie użyty do połączenia wartości listy
wybieramy Niestandardowe, a następnie w polu poniżej wpisujemy nasz
ogranicznik, czyli przecinek i spacja. Powyższe parametry zatwierdzamy
przyciskiem OK (rys. nr 20).
rys. nr 20 — Parametry wyodrębniania wartości
Otrzymamy wyodrębnione dane przedstawione na rysunku nr 21.
rys. nr 21 — Wyodrębnione dane
Pozostaje nam wczytać tak przygotowane dane do Excela. W tym
celu korzystamy z 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 wybieramy sposób
wyświetlania danych jako Tabela, a następnie miejsce ich wstawienia –
Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem Załaduj (rys. nr 23).
rys. nr 23 — Parametry ładowania danych
Otrzymamy dane zaczytane do Excela przedstawione na rysunku
nr 24.
rys. nr 24 — Dane w Excelu
Możemy wprowadzać dowolne zmiany w tabeli z danymi
wejściowymi, a następnie wystarczy kliknąć prawym przyciskiem myszy na tabelę z
naszego zapytania i z podręcznego menu wybrać polecenie Odśwież, aby dane
zostały zaktualizowane.
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ę wyznaczaniem numeru tygodnia w roku i miesiącu oraz początku i końca tygodnia. W poradach od 341 do 344 omawialiśmy wyznaczanie tych danych w Excelu za pomocą funkcji .
Poznamy możliwości Power Query w tym zakresie. Temat ten
omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.
rys. nr 1 — Przykładowe dane
Mamy tutaj przedstawione dane zawierające zbiór dat i
informację jaki to jest dzień tygodnia. Aby zaczytać dane do Power Query
wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli
Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą.
Klikamy ikonkę kalendarza przy nazwie kolumny Data aby zmienić formatowanie. Z
podręcznego menu wybieramy typ danych – Data (rys. nr 3). Wyświetli nam się
komunikat o Zmianie typu danych, w którym za pomocą przycisku Zamień bieżącą
zatwierdzamy zmianę.
rys. nr 3 — Zmiana typu danych
Otrzymamy dane przedstawione na rysunku nr 4.
rys. nr 4 — Dane w edytorze zapytań
Chcemy dodać nową kolumnę z danymi zawierającymi numer
tygodnia więc korzystamy z poleceń z karty Dodaj kolumnę. Gdybyśmy natomiast
chcieli przekształcić nasze dane musimy skorzystać z analogicznych poleceń na
karcie Przekształć (rys. nr 5).
rys. nr 5 — Przekształcenie danych
Naszym zadaniem jest dodanie nowych kolumn i interesującymi
nas danymi. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt
2 na rys. nr 6) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Tydzień roku (punkt 4).
rys. nr 6 — Dodawanie nowej kolumny Tydzień roku
Otrzymamy dane z nową kolumną zawierającą numer tygodnia w
roku odpowiadającego naszej dacie (rys. nr 7).
rys. nr 7 — Tydzień roku
Istotną informacją tutaj jest, że Power Query działa na
systemie pierwszym funkcji NUM.TYGODNIA Excela, czyli data 1 stycznia każdego
roku to pierwszy dzień pierwszego tygodnia nowego roku. W Power Query możemy
jedynie skorygować od jakiego dnia rozpoczyna się nowy tydzień. W Polsce
standardowo nowy tydzień zaczyna się w poniedziałek i takie właśnie jest domyślne
działanie funkcji Date.WeekOfYear w Power Query. Możemy to zmienić, wystarczy
skopiować nazwę funkcji a następnie nacisnąć przycisk fx obok paska formuły,
aby dodać nowy krok i wkleić w pasek formuły zapis =Date.WeekOfYear. Kiedy
zatwierdzimy taką formułę klawiszem Enter otrzymamy informacje na temat tej
funkcji, między innymi jej parametrów oraz jakie zwraca wartości. Możemy sobie
tutaj w polu firstDayOfWeek wybrać dzień jaki chcemy przyjąć za początek
tygodnia (rys. nr 8).
rys. nr 8 — funkcja Date.WeekOfYear
Usuwamy ten krok z Zastosowanych kroków, ponieważ
wprowadziliśmy go tylko w celu pokazania możliwości zmiany dnia początku
tygodnia. Jeśli chcemy wprowadzić zmianę dotyczącą dnia początku tygodnia
możemy wpisać w formułę dodatkowy parametr zawierający nazwę dnia, jaki chcemy
przyjąć za początek tygodnia. Załóżmy, że chcemy aby tydzień rozpoczynał się we
wtorek, więc w formułę wpiszemy Day.Tuesday (rys. nr 9). Formuła powinna
wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień
roku" each Date.WeekOfYear([Data], Day.Tuesday), Int64.Type)
rys. nr 9 — Formuła funkcji Date.WeekOfYear
Otrzymamy zmienione dane, w których każdy nowy tydzień będzie
rozpoczynał się we wtorek (rys. nr 10).
rys. nr 10 — Zmienione dane
Zamiast Day.Tuesday możemy wpisać wartość 2 w formule.
Zadziała ona prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Tydzień
roku" each Date.WeekOfYear([Data], 2), Int64.Type)
W Power Query dni są numerowane od niedzieli do soboty.
Niedzieli odpowiada wartość 0, poniedziałek to wartość 1 itd., natomiast
sobocie przypisana jest wartość 6.
Wszystkie kolejne funkcje, z których tutaj będziemy korzystać
mają ten dodatkowy argument, w którym możemy przyjąć jaki dzień chcemy
traktować jako początek tygodnia.
W kolejnym przykładzie wyznaczymy sobie numer tygodnia w
miesiącu. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2
na rys. nr 11) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Tydzień miesiąca (punkt 4).
rys. nr 11 — Tydzień miesiąca
Otrzymamy nową kolumnę z numerem tygodnia w miesiącu (rys. nr
12).
rys. nr 12 — Numer tygodnia w miesiącu
Dzień uznawany za początek tygodnia wstawił się domyślnie
(poniedziałek). Jeśli chcemy zmienić dzień początku tygodnia musimy to zrobić
analogicznie jak w poprzednim przykładzie, czyli dopisać ten argument w formułę
funkcji na pasku formuły (rys. nr 13). Zakładamy, że chcemy aby tydzień
rozpoczynał się w piątek, czyli musimy wpisać argument Day.Friday lub wartość
5. Zapis powinien wyglądać następująco:
=Table.AddColumn(#"Wstawiono Tydzień roku",
"Tydzień miesiąca", each Date.WeekOfMonth([Data],5) Int64.Type)
rys. nr 13 — Funkcja Date.WeekOfMonth
Gdybyśmy nie mieli widocznego paska formuły, musimy go
uruchomić w karcie Widok (rys. nr 14).
rys. nr 14 — Uruchomienie paska formuły
Otrzymamy dane przedstawiające numerację rozpoczynającą nowy
tydzień w piątek (rys. nr 15).
rys. nr 15 — Dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek
W kolejnym przykładzie dodamy kolumnę zawierającą Początek
tygodnia. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2
na rys. nr 16) z karty Dodaj kolumnę,
następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas
informację, czyli w naszym przykładzie Początek tygodnia (punkt 4).
rys. nr 16 — Początek tygodnia
Dodamy również kolumnę zawierającą Koniec tygodnia w
analogiczny sposób jak na rysunku nr 16. Otrzymamy wtedy dwie nowe kolumny
przedstawione na rysunku nr 17.
rys. nr 17 — Dane z początkiem i końcem tygodnia
Zostało tu zastosowana numeracja domyślna, czyli tydzień
rozpoczyna się w poniedziałek, a kończy w niedzielę.
Użyliśmy w tych przykładach dwóch funkcji Date.StartOfWeek
oraz Date.EndOfWeek.
Istotną informacją dla działań w Power Query jest to, że data
1 stycznia zawsze rozpoczyna pierwszy tydzień nowego roku. Możemy jedynie
zmieniać dni początku i końca tygodnia. Nie ma możliwości zrobić kontynuacji
numeru tygodnia z poprzedniego roku w nowym roku, tak żeby tydzień zawsze miał
7 dni, jak to było możliwe w Excelu.
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 18).
rys. nr 18 — Zamknij i załaduj do
Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób
wyświetlania danych jako Tabela oraz wybieramy miejsce wstawienia danych –
Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 19). Powyższe
parametry zatwierdzamy przyciskiem Załaduj.
rys. nr 19 — Okno ładowania do
Otrzymamy dane wczytane do Excela przedstawione na rysunku nr
20.
rys. nr 20 — Dane zaczytane do Excela
Możemy sobie jeszcze odpowiednio sformatować dane korzystając
ze skrótu klawiszowego Ctrl+1, aby przy tych datach wyświetlała się nazwa dnia
tygodnia. Otworzy nam się okno formatowania komórek, gdzie wybieramy
formatowanie Niestandardowe w karcie Liczby, następnie w polu Typ musimy
dopisać zapis ddd (rys. nr 21).
rys. 21 — Formatowanie komórek
Otrzymamy sformatowane dane przedstawione na rysunku nr 22.
21 — Sformatowane 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ę wyciąganiem tekstu po
ograniczniku, między ogranicznikami bądź przed ogranicznikiem. W pytaniu od
widzów nr 104 napisaliśmy formułę w Excelu, która wyciągała tekst znajdujący
się pomiędzy przedostatnim a ostatnim ogranicznikiem np. oznaczony na rysunku
nr 1 na czerwono.
rys. nr 1 — Tekst który będziemy wyciągać
Formuły te w Excelu były skomplikowane. W Power Query możemy
zrobić to samo, ale dużo szybciej. Ponadto możemy wyciągnąć tekst znajdujący
się za konkretnym ogranicznikiem, przed lub pomiędzy dwoma konkretnymi
ogranicznikami. Zagadnienie to omówimy na podstawie przykładowych danych
przedstawionych na rysunku nr 2.
rys. nr 2 — Przykładowe dane
W przykładowych danych mamy zestawione różne ścieżki do
plików, z których będziemy chcieli wyciągnąć konkretną informację. Pierwszym
krokiem jest zaczytanie danych do Power Query. Otrzymamy Dane w Edytorze
zapytań przedstawione na rysunku nr 3.
rys. nr 3 — Edytor zapytań
Z zastosowanych kroków usuwamy krok o nazwie Zmieniono typ.
Będziemy dodawać kolumny zawierające wyciągnięte informacje z tekstu z danych
wejściowych. Rozwijamy polecenie wyodrębnij (punkt 2 na rys. nr 4) z karty
Dodaj kolumnę, a następnie wybieramy polecenie Tekst przed ogranicznikiem
(punkt 3 na rys. nr 4).
rys. nr 4 — Tekst przed ogranicznikiem
Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w
polu Ogranicznik musimy wybrać lub wpisać ogranicznik oznaczający koniec
zawartości, którą chcemy wyodrębnić. Wpisujemy / (backslash). Rozwijamy opcje
zaawansowane, gdzie możemy wybrać dodatkowe parametry. Domyślnie Power Query
czyta dane od lewej do prawej strony i znajdzie nam pierwsze wystąpienie
ogranicznika. W opcjach zaawansowanych możemy wybrać ilość ograniczników, które
chcemy pominąć. W polu liczba ograniczników do pominięcia wpisujemy wartość 1,
czyli chcemy pominąć pierwsze wystąpienie ogranicznika (rys. nr 5). Tak
ustawione parametry zatwierdzamy przyciskiem OK.
rys. nr 5 — Okno tekstu przed ogranicznikiem
Otrzymamy dane z nową kolumną o nazwie Tekst przed
ogranicznikiem przedstawione na rysunku nr 6.
rys. nr 6 — dane z kolumną z tekstem przed ogranicznikiem
Ważne jest, że domyślnie rozpatrujemy dane od lewej strony, a
w opcjach zaawansowanych możemy ten parametr zmienić. Opcja ta jest zapisana w
pasku formuły naszej funkcji (Text.BeforeDelimiter), co zostało zaznaczone
kolorem zielonym na rys. nr 7.
rys. nr 7 — Funkcja Text.BeforeDelimiter
Możemy edytować nasze zapytanie klikając na koło zębate obok
nazwy kroku Wstawiony tekst przed ogranicznikiem (rys. nr 8).
rys. nr 8 — Koło zębate do edycji kroku
Ponownie otworzy nam się okno Tekstu przed ogranicznikiem
gdzie możemy wybrać jedną z dwóch opcji rozpatrywania danych: Od początku
danych wejściowych lub Od końca danych wejściowych (rys. nr 9).
rys. nr 9 — Parametry tekstu przed ogranicznikiem
Jeśli ustawimy parametr Od końca danych wyjściowych otrzymamy
dane przedstawione na rysunku nr 10.
rys. nr 10 — Nowa kolumna z tekstem przed ogranicznikiem patrząc od końca
W tym przypadku Power Query będzie przeszukiwał dane od
prawej strony. Ponieważ nie jest to domyślne działanie nasza formuła się
rozbudowała (rys. nr 11).
rys. nr 11 — Rozbudowana formuła funkcji
W kolejnej nowej kolumnie chcemy otrzymać tekst za
ogranicznikiem. W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr
12) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst za
ogranicznikiem (punkt nr 3).
rys. nr 12 — Tekst za ogranicznikiem
Otworzy nam się okno Tekstu za ogranicznikiem, gdzie w polu
Ogranicznik wpisujemy znak (/).Identycznie jak dla pierwszego przypadku, czyli
Tekstu przed ogranicznikiem. Następnie w opcjach zawansowanych, wybieramy czy
chcemy rozpatrywać dane od lewej do prawej strony czy od końca oraz wpisujemy
liczbę ograniczników, które chcemy pominąć. Wpisujemy w naszym przykładzie, że
chcemy pominąć 2 ograniczniki oraz rozpatrywanie danych domyślne, czyli od
lewej do prawej. Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 13).
rys. nr 13 — Okno tekstu za ogranicznikiem
Otrzymamy
dane przedstawione na rysunku nr 14.
rys. nr 14 — Nowa kolumna z tekstem za ogranicznikiem
Ostatnie nasze przekształcenie danych będzie dotyczyć
wyodrębnienia tekstu między konkretnymi ogranicznikami. W tym celu rozwijamy
polecenie Wyodrębnij (punkt 2 na rys. nr 15) z karty Dodaj kolumnę, a następnie
wybieramy polecenie Tekst między ogranicznikami (punkt 3).
rys. nr 15 — Tekst między ogranicznikami
Otworzy nam się okno Tekstu między ogranicznikami, gdzie
wpisujemy ograniczniki w polach Ogranicznik początkowy i końcowy, czyli w
naszym przykładzie w obu polach ten sam znak (/). W opcjach zaawansowanych
możemy jak w innych omawianych dziś przypadkach ustawić czy chcemy przeszukiwać
dane od początku do końca czy odwrotnie. W pytaniu od widzów było zadanie
znalezienia tekstu między przedostatnim a ostatnim ogranicznikiem, więc
ustawiamy rozpatrywanie danych od prawej do lewej strony (od końca). W polu
Liczba ograniczników początkowych do pominięcia wpisujemy 1 ogranicznik,
natomiast w polu Liczba ograniczników końcowych do pominięcia zostawiamy 0. Tak
ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 16).
rys. nr 16 — Okno tekstu między ogranicznikami
Otrzymamy dane przedstawione na rysunku nr 17, czyli tekst
między ostatnim a przedostatnim ogranicznikiem.
rys. nr 17 — Nowa kolumna z tekstem między ogranicznikami
Możemy zagłębić się bardziej w opcje zaawansowane, ale wtedy
łatwiej się zgubić, ponieważ opcja Skanuj w poszukiwaniu ogranicznika końcowego
daje nam dwie możliwości. Po znalezieniu pierwszego ogranicznika w tej opcji
możemy wybrać czy chcemy przeszukiwać dane w lewą stronę od tego ogranicznika
czy w prawo. Pokażemy te opcje na przykładzie zmiany drugiego ogranicznika na
znak @ oraz przy pominięciu jednego znaku @. Ustawione parametry powinny
wyglądać jak na rysunku nr 18.
rys. nr 18 — Zmiany parametrów tekstu między ogranicznikami
Otrzymamy wtedy dane przedstawione na rysunku nr 19. Możemy zauważyć, że w górnych wierszach nie ma znaków @ po znaku /, więc Power Query wyciągnie nam cały tekst do końca. W ostatnim wierszu otrzymamy fragment tekstu między ostatnim znakiem / a drugim znakiem @, zgodnie z ustawionymi parametrami.
rys. nr 19 — zmienione dane
Najlepiej przeczytać dokładnie wszystkie opcje i przetestować
ich działanie.
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 20).
rys. nr 20 — Zamknij i załaduj do
Otrzymamy dane załadowane do Excela. Zadanie to jest dużo
szybsze w Power Query, musimy tylko pamiętać o dokładnym czytaniu parametrów
oraz o tym w którą stronę czytamy dane (od początku do końca czy odwrotnie).
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ę kontynuacją poprzedniego, a
dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym
określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest
wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej
zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu
M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych
przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Pierwszym krokiem jest zaczytanie danych wejściowych do Power
Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
rys. 2 — Z tabeli/ zakresu
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą
z danymi przedstawioną na rysunku nr 3.
rys. 3 — Edytor zapytań
Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono
typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy
prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 4).
rys. 4 — Usuń inne kolumny
Otrzymamy interesujące nas dane przedstawione na rysunku nr
5.
rys.5 — Dane po usunięciu innych kolumn
Do tego momentu kroki w działaniu były takie same jak w
poprzednim poście https://exceliadam.pl/?p=9720
. Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku
formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6).
Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.
rys. 6 — Kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej, gdzie w polu
Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać
następująco:
=Text.Split([Produkt], ", ")
W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7).
rys. 7 — Parametry kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 8.
rys. 8 — Kolumna niestandardowa w postaci list
Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem
List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr
9).
rys. 9 — Dane ukryte pod nazwą List
Kolejnym krokiem jest policzenie elementów każdej z tych
list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę
niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić
zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:
=List.Count(Text.Split([Produkt],
", "))
rys. 10 — zmiany wprowadzone w oknie kolumny niestandardowej
Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy
dane przedstawione na rysunku nr 11.
rys. 11 — zmieniona kolumna niestandardowa
Podsumowując najpierw podzieliliśmy dane na Poszczególne
listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w
każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić
zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn
na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak
na rysunku nr 12.
=List.Transform(#"Usunięto inne kolumny" [Produkt],
each List.Count(Text.Split(_,", ")))
rys. 12 — Formuła funkcji List.Transform
Po zatwierdzeniu formuły otrzymamy dane przedstawione na
rysunku nr 13.
rys. 13 — Wynik funkcji List.Transform
Otrzymaliśmy listę wszystkich elementów na konkretnych
listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną
kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów
tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję
List.Max. Formuła powinna wyglądać jak na rysunku nr 14.
=List.Max(List.Transform(#"Usunięto inne kolumny"
[Produkt], each List.Count(Text.Split(_,", "))))
rys. 14 — Zapis funkcji List.Max
Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik
przedstawiony na rysunku nr 15.
rys. 15 Wynik maksymalny
Kolejny krok pokrywa się z krokiem z wcześniejszego filmu,
czyli Power Query odcinek 36.
Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go
i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy
przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą
skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.
rys. 16 Dane z kroku Usunięto inne kolumny
Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr
17) z karty Narzędzia główne, a następnie wybieramy polecenie Według
ogranicznika (punkt nr 3 na rysunku nr 17).
rys. 17 — Podziel kolumny według ogranicznika
Otworzy nam się okno Dzielenia kolumny według ogranicznika,
gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe
(punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy
nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym
wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i
wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go
zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem
OK.
rys. 18 — parametry dzielenia kolumny
Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy
niepotrzebny krok o nazwie Zmieniono typ.
rys. 19 — Podzielone dane
W
pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn
(podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku
rys.20 zmiana nazw kolumn na odwołanie do poprzedniego kroku w pasku formuły
Klikamy nazwę wcześniejszego kroku — Dodano kolumnę
niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za
pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku,
czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego
kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien
wyglądać jak na rysunku nr 21.
rys. 21 — Zapis formuły
Po zatwierdzeniu formuły otrzymamy dane przedstawione na
rysunku nr 22.
rys. 22 — Dane uzyskane z działania funkcji Table.TransformColumn
Otrzymaliśmy jedenaście kolumn dynamicznych, czyli
zmieniających się pod wpływem zmian w danych wejściowych.
Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć
krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w
pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto
inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po
zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.
rys. 23 zmiana nazw kroków w pasku formuły
Następnie możemy usunąć już zbędny krok Niestandardowe1.
Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat
Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys.
nr 24).
rys. 24 — Usuwanie kroku
Tak przygotowane dane możemy załadować do Excela korzystając
z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).
rys. 25 — Zamknij i załaduj do
Otworzy nam się okno Importowania danych, gdzie wybieramy
sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia,
czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone
parametry zatwierdzamy przyciskiem OK (rys. nr 26)
rys. 26 — Okno importowania danych
Otrzymamy dane zaimportowane do Excela przedstawione na
rysunku nr 27.
rys. 27 — Dane zaimportowane do Excela
Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi,
aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z
Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym
przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z
podręcznego menu.
Właśnie dodałem mó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 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.
