Oto drugi artykuł o pobieraniu danych z sieci Web z użyciem Power Query, które służyły do przygotowania infografiki „Polska najwyżej w historii w rankingu FIFA”. Po pobraniu danych z 3 stron Wikipedii przyszedł czas na pobranie danych z prawie 300 stron internetowych o takim samym układzie. Wykonanie tego na piechotę dla każdej ze stron, nawet za pomocą Power Query, byłoby czasochłonne. W takich przypadkach z pomocą przychodzą funkcje w Power Query, która pozwalają raz napisane zapytanie zastosować do wielu różnych elementów.

Analogiczny przykład dla użytkownika programu Excel wyglądałby następująco: mam plik w Excelu z wieloma zakładkami, a w każdej zakładce nieuporządkowane dane, które trudno będzie wczytać na raz. Jak wybrnąć z tej sytuacji? Nagrać zapytanie dla pierwszego arkusza, przekształcić na funkcję a następnie zastosować do pozostałych arkuszy (ich listę też można wczytać do Power Query).

Budowa pierwszego zapytania Power Query

Zaczynamy od przygotowania pierwszego zapytania. Nagrywam w najprostszy możliwy sposób ciąg poleceń, który pozwoli pobrać dane z sieci Web. Ich obróbką mogę zająć się teraz lub jak będę miał kompletną listę danych. W przypadku danych ze strony FIFA, zapytanie było dość proste „do wyklikania” za pomocą interfejsu pobierania z sieci Web, opisanego w części 1:

Wychwycenie struktury strony internetowej

Podana tu technika ma zastosowanie tylko w sytuacji, gdy pomiędzy pojedynczymi stronami istnieje różnica w adresach stron, którą możemy w jakiś sposób zdefiniować. W przypadku strony FIFA zmienia się numer rankingu w odnośniku.

Utworzenie funkcji w Power Query

Wiedząc, gdzie następują zmiany w adresie, możemy przystąpić do utworzenia funkcji. Choć na pierwszy rzut oka może to wyglądać na skomplikowaną operację (Edytor zaawansowany, pisanie kodu w M), wcale takie nie jest. Na karcie Narzędzia główne znajdujemy Edytor zaawansowany i deklarujemy zmienną tekstową przed słowem let:

(ID as text) =>

Następnie umieszczamy ją zamiast numeru strony.

Dlaczego zmienna tekstowa, jeśli mamy do czynienia z liczbami?

Cała treść hiperłącza jest tekstem, a zmienna jest dodawana za pomocą formuły łączenia tekstów: „&”.

Zapytanie zmieni postać w oknie zapytań:

Wpisując jakiś parametr (np. numer strony), możemy przetestować, czy zapytanie działa. W ten sposób wygenerujemy nowe zapytanie.

Budowa tabeli ze zmiennymi

Znając różnice w adresach, możemy zbudować tabelę ze zmiennymi. Jest na to kilka sposobów, ale dla użytkowników programu Excel najprostszym z nich jest utworzenie nowej tabeli w arkuszu i wczytanie jej jako zapytanie.

Upewniwszy się, że kolumna ma format tekstowy, możemy utworzyć nową kolumnę, która będzie pochodzić z wywołania naszej funkcji. Na karcie Dodaj kolumnę wybieramy Wywołaj funkcję niestandardową, a następnie wybieramy funkcję (moja nazywała się Table 0) oraz wskazujemy kolumnę, w której mamy argumenty.

Jeżeli wszystko poszło poprawnie, otrzymamy nową kolumnę o typie Table, którą możemy rozwinąć, uzyskując w efekcie dane dla wszystkich argumentów. W przypadku danych z FIFA, są to dane pochodzące z prawie 300 stron.

Pobierz plik Excel

Tu możesz pobrać plik Excel, aby podejrzeć całe zapytanie Power Query od środka.