Policzymy wystąpienia wszystkich liter polskiego alfabetu w każdym wyrazie. Wynik przedstawimy w postaci złożonej tabeli. Kolumnami będą litery alfabetu, a wartościami liczby wystąpień każdego znaku.
Dane źródłowe
Potrzebne będą dwie tabele:
tabela – baza miast
tabela – alfabet polski
Zamiana wszystkich liter na małe
Na samym początku należy zamienić wszystkie litery na małe. Power Query jest key sensitve i dla niego litery „A” i „a” nie są sobie równe. Należy ujednolicić format zapisu, aby poprawnie zliczyć znaki.
Dane w zapytaniu „alfabet polski” mają już odpowiedni format tekstu. Przejdź do zapytania „zliczanie liter”.
- Zamień wszystkie litery na małe w kolumnie [miasto]
- Zaznacz kolumnę [miasto]
- Zakładka „Przekształć„
- Polecenie „Format„
- Opcja „Małe litery„
Wydzielenie każdej litery
Stworzymy nową kolumną o strukturze List, w której będą wyodrębnione wszystkie znaki do osobnych komórek.
- Dodaj kolumnę niestandardową i nazwij ją [lista znaków]
- Zakładka „Dodaj kolumnę„
- Opcja „Kolumna niestandardowa„
- Nazwij kolumnę „lista znaków„
W pasku formuły wpisz funkcję:
Text.ToList([miasto])Text.ToList([wskaż kolumnę z danymi])Funkcja Text.ToList() rozdziela każdy znak. Zostaje w ten sposób utworzona nowa kolumna [lista znaków] z danymi typu List wewnątrz której są odseparowane wszystkie znaki (litera, spacja, liczba).
Dodawanie indeksu
W ten sposób nadamy unikalny numer każdemu rekordowi. W późniejszym etapie będziemy wyodrębniać każdy znak z kolumny [lista znaków] do osobnego wiersza. Dzięki numeracji będziemy wiedzieć jaka grupa liter należy do słowa np. wszystkie litery pod numerem 1 będą należeć do „Jelenia Góra”
- Dodaj kolumnę indeks
- Zakładka „Dodaj kolumnę„
- Opcja „Kolumna Indeksu„
- Polecenie „Od 1„
Rozwijanie danych
Po rozwinięciu danych z kolumny [lista znaków] otrzymamy w każdym wierszu odseparowany znak każdego wyrazu.
- Rozwiń dane typu List w kolumnie [lista znaków]
- Kliknij w ikonę strzałek, która znajduję się obok nazwy kolumny [lista znaków]
- Z rozwijanej listy wybierz opcję „Rozwiń do nowych wierszy„
Dane prezentują się następująco:
Każdy znak został odseparowany. Po odpowiednim odfiltrowaniu znaków rozdzielających wyrazy w kolumnie [lista znaków] będzie można policzyć liczbę wystąpień liter w każdym słowie.
- Pod numerem 1 w kolumnie [indeks] mamy wszystkie znaki dla miasta „Jelenia Góra”
- Pod numerem 2 w kolumnie [indeks] mamy wszystkie znaki dla miasta „Legnica”
Odfiltrowywanie zbędnych znaków
Aby policzyć tylko litery należy wyfiltrować zbędne znaki np. spację
- Odfiltruj niepotrzebne znaki z kolumny [lista znaków]
- Kliknij w strzałkę znajdującą się obok nazwy kolumny [lista znaków]
- Odznacz znaki, które nie występują w polskim alfabecie
Grupowanie danych – zliczanie liter
Poprzez grupowanie nadamy nowy kształt danym, dzięki czemu zliczymy znaki dla każdego wyrazu
- Pogrupuj dane
- Zakładka „Przekształć„
- Opcja „Grupowanie według„
Ustaw kryteria tak jak poniżej:
Grupowanie według kolumn [indeks] i [lista znaków] nada odpowiedni kształt danym i wykona poniższe operacje
- Kolumna [liczność] z operacją „Zlicz wiersze” – zliczy wystąpienia każdego znaku.
- Kolumna [w] z operacją „Wszystkie wiersze” – zwróci minitabelki z wszystkimi danymi.
Zaznaczanie pierwszego wiersza
Po samym numerze [indeks] nie jesteśmy w stanie stwierdzić dla jakiego miasta są zliczane litery. Ta informacja zawarta jest w minitabelkach w kolumnie [w].
- Dodaj „kolumnę niestandardową„
- Zakładka „Dodaj kolumnę„
- Opcja „Kolumna niestandardowa„
- Nazwij kolumnę „miasto„
W pasku formuły wpisz funkcję:
Table.FirstN([w], 1)[miasto]Funkcja Table.FirstN() zaznacza w każdej minitabelce z kolumny [w] pierwszy wiersz. Następnie zostaje wyodrębniania kolumna [miasto] w formacie typu List. W skrócie funkcja Table.FirstN() mówi: Zaznacz pierwszy wiersz i pozostaw tylko dane z kolumny [miasto]
Dane prezentują się następująco:
Wyodrębnianie wartości
Nazwa miasta jest zagnieżdżona w kolumnie [miasto]. Rozwiniemy dane z tej kolumny, aby pozyskać nazwy miast
- Kliknij w ikonę strzałek obok nazwy kolumny [miasto]
- następnie wybierz polecenie „Wyodrębnij wartości..”
- Następnie wyskoczy okienko z ustawieniami. Ustaw parametry tak jak na obrazku poniżej
W polu wyboru ogranicznika łączącego elementy na listach pozostawiamy opcję „brak”, ponieważ każda komórka z danymi typu List w kolumnie [miasto] zawiera tylko jeden element.
Edytor Power Query prezentuje się następująco:
Dodawanie do tabeli brakujących liter alfabetu
Musimy teraz dodać te litery, które nie występuje w nazwach miast. Wykorzystamy do tego funkcję „scalanie zapytań”.
- Scal zapytania: „zliczanie liter” z zapytaniem „alfabet„
- Zakładka „Narzędzia główne„
- Opcja „Scal zapytania„
Ustaw kryteria tak jak na obrazku poniżej:
W pierwszej tabeli zaznaczamy kolumnę z polskimi znakami – „lista znaków”, a w drugiej kolumnę [alfabet]. Po wyborze sprzężenia „Prawe zewnętrzne” litery, które nie zostaną znalezione w zapytaniu „zliczanie liter” zostaną dodane na końcu tej tabeli. Na poniższym zrzucie zaznaczyłem mintabelkę zawierającą brakujące litery alfabetu.
- Rozwiń dane z kolumny [alfabet]
- Kliknij w ikonę strzałek, która znajduję się obok nazwy kolumny [alfabet]
- Z rozwijanej listy wybierz opcję „Rozwiń do nowych wierszy„
Dane prezentują się następująco:
Przestawianie kolumn
Obecna struktura tabeli jest mało przyjazna dla oka. Zmienimy jej schemat w taki sposób:
- nazwami kolumn będą litery alfabetu w prawidłowej kolejności
- w wierszach będą nazwy miast
- wartościami liczba wystąpień każdej litery
- Posortuj dane od A do Z w kolumnie [alfabet.1]
- Usuń kolumny: [w] i [lista znaków]
- Przestaw kolumny:
- Zaznacz kolumnę [alfabet.1]
- Zakładka „Przekształć„
- Polecenie „Kolumna przestawna„
Ustaw kryteria tak jak na obrazku poniżej:
Polecenie „kolumna przestawna” tworzy nowe nazwy kolumny z zaznaczonej kolumny [alfabet.1], a jako wartości przypisuje dane z kolumny [liczność].
- pole kolumna wartość – z listy dostępnych kolumn wybieramy kolumnę [Liczność]
- w polu agreguj funkcję wartości – wybieramy opcję „Nie agreguj”, bo nie chcemy, aby w nowo powstałych kolumnach zostały wykonane jakiekolwiek operacje arytmetyczne na wartościach z kolumny [Liczność]
Dane prezentują się następująco:
Litery diakrytyczne przez Power Query nie są poprawnie sortowane i dlatego nazwy kolumn z takimi znakami są jako ostatnie. Teraz wystarczy odfiltrować niepotrzebne puste wartości w kolumnie [Indeks].