Bezpłatny kurs Power Query: źródła danych - CSV, JSON, API, SharePoint i bazy danych

W skróciePower Query czyta dane z plików, stron WWW, API, SharePointa i baz danych tym samym schematem: łącznik, poświadczenia, nawigator. Importujemy plik CSV, pobieramy tabelę kursów z API NBP w formacie JSON, rozwijamy zamówienia online i omawiamy pozostałe najczęstsze źródła.

Źródła danych: CSV, JSON, API, SharePoint i bazy danych

Power Query w Excelu ma kilkadziesiąt łączników, a Power BI Desktop jeszcze więcej. Mechanika jest wszędzie ta sama: wybierasz łącznik, podajesz lokalizację, poświadczenia, potem w nawigatorze wskazujesz tabelę. Zobaczmy kilka źródeł, które pojawiają się najczęściej.

Pliki CSV i tekstowe

Pojedynczy plik importujesz przez Pobierz dane > Z pliku > Z pliku tekstowego/CSV. Okno importu, które widziałeś w opisie danych do ćwiczeń, ma te same trzy ustawienia co łączenie plików: Pochodzenie pliku (kodowanie), Ogranicznik i Wykrywanie typu danych. Pliki o stałej szerokości kolumn (bez ogranicznika) obsłużysz, wybierając w polu ogranicznika Stała szerokość i podając pozycje podziału.

API w formacie JSON: kursy walut NBP

Interfejsy API zwracają zwykle dane w formacie JSON. Pokażemy to na publicznym API Narodowego Banku Polskiego, które nie wymaga klucza.

1. Nowe źródło > Inne źródła > Internet (w Excelu: Pobierz dane > Z innych źródeł > Z sieci Web). Wklej adres:

https://api.nbp.pl/api/exchangerates/tables/A?format=json

2. Przy pierwszym połączeniu z adresem Power Query zapyta o sposób uwierzytelnienia. API NBP jest publiczne, więc zostaw Anonimowy. Lista po prawej określa, czy ustawienie dotyczy całej domeny, czy tylko tego adresu.

3. Power Query rozpozna JSON i pokaże listę z jednym rekordem. Kliknij Record, żeby wejść do środka. Rekord ma pola table, no (numer tabeli kursów), effectiveDate i rates, czyli listę kursów.

4. Lista kursów to 32 rekordy. Na karcie Przekształć (w grupie Narzędzia do obsługi list) kliknij Do tabeli i zatwierdź okno bez zmian.

5. Kliknij ikonę rozwijania w nagłówku kolumny, zostaw zaznaczone pola currency, code i mid, odznacz prefiks i kliknij OK.

Jeśli raport z takim zapytaniem trafi do usługi Power BI, warto od razu zapisać adres w częściach. Usługa Power BI potrafi odświeżać tylko źródła, których adres bazowy jest stały, a zmienne fragmenty (ścieżkę i parametry) przekazujesz w opcjach RelativePath i Query:

= Json.Document(Web.Contents("https://api.nbp.pl/api/",
    [RelativePath = "exchangerates/tables/A", Query = [format = "json"]]))

Wiele API zwraca dane stronami, na przykład po sto rekordów. Wtedy pobierasz kolejne strony funkcją List.Generate, dopóki strona nie przyjdzie pusta. Poniższy wzorzec działa dla typowego API z parametrem page i listą rekordów w polu items (adres jest przykładowy, nazwy parametrów sprawdź w dokumentacji swojego API):

let
    Strona = (nr as number) => Json.Document(Web.Contents("https://api.przyklad.pl/",
        [RelativePath = "zamowienia", Query = [page = Text.From(nr)]])),
    Strony = List.Generate(
        () => [nr = 1, dane = Strona(1)],
        each List.Count([dane][items]) > 0,
        each [nr = [nr] + 1, dane = Strona([nr] + 1)],
        each [dane][items]),
    Wszystko = List.Combine(Strony)
in
    Table.FromRecords(Wszystko)

Ten sam schemat (lista, rekord, do tabeli, rozwiń) obsłuży każdy plik JSON, także z dysku (Z pliku > Z formatu JSON). W materiałach do kursu jest plik z zamówieniami online, w którym każde zamówienie ma listę pozycji. Taką zagnieżdżoną listę rozwijasz opcją Rozwiń do nowych wierszy, wtedy każde zamówienie zajmie tyle wierszy, ile ma pozycji.

Strony WWW, PDF, SharePoint i bazy danych

• Z sieci Web na zwykłej stronie HTML pokaże w nawigatorze wszystkie tabele znalezione na stronie oraz tabele sugerowane, które Power Query sam wyodrębnił z układu strony. Zanim zaczniesz regularnie pobierać dane z cudzej strony, sprawdź jej regulamin: część serwisów zabrania automatycznego pobierania treści.
• Pliki Parquet, czyli kolumnowy format z hurtowni danych i Lakehouse, w Power BI Desktop i w Fabric czyta osobny łącznik Parquet, a usługi online (Exchange, Dynamics 365, Salesforce, źródła OData) mają własne łączniki z logowaniem kontem tej usługi.
• Z pliku PDF wyciąga tabele z dokumentów PDF, na przykład z cenników czy wyciągów. Każda tabela i każda strona to osobna pozycja w nawigatorze.
• Z folderu programu SharePoint działa jak łączenie folderu lokalnego, ale dla bibliotek dokumentów SharePointa i OneDrive dla firm. Podajesz adres witryny, nie ścieżkę folderu, a potem filtrujesz pliki po kolumnie Folder Path. Pokazujemy to krok po kroku w lekcji o Fabric, bo tam folder lokalny nie wchodzi w grę.
• Z bazy danych łączy się z SQL Server, Oracle, PostgreSQL, MySQL i innymi. Przy bazach dochodzi zjawisko, którego pliki nie mają: składanie zapytań. Omawiamy je na przykładzie SQL Server w lekcji o Power BI.

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

✕