JSON z API: kolumny z listami i rekordami - jak je rozwinąć w Power Query

Power Query w Excelu, Power BI i Fabric

W skrócie

Po wczytaniu JSON z API Power Query pokazuje zamiast danych napisy List i Record. Kolumny z listami i rekordami trzeba rozwinąć, a każdy rodzaj wymaga innego polecenia.
JSON zagnieżdża dane: obiekt staje się w Power Query rekordem, a tablica listą. Lista rekordów to przyszłe wiersze, rekord to kolumny jednego wiersza, a lista w środku wiersza, na przykład pozycje zamówienia, oznacza kilka wierszy na jeden rekord nadrzędny.
Listę zamień na tabelę przyciskiem Do tabeli, rekordy rozwiń do kolumn ikoną rozwijania, a listy w kolumnie rozwiń opcją Rozwiń do nowych wierszy. Wyodrębnij wartości stosuj tylko do list prostych wartości.

Jak rozwinąć listę rekordów z JSON w Power Query, to pytanie wraca przy każdym API i każdym eksporcie zapisanym jako JSON. Pokazujemy, co oznaczają napisy List i Record, w jakiej kolejności rozwijać kolumny i jak rozpoznać błąd, gdy zamiast JSON przyjdzie coś innego. Przykłady opieramy na API kursów walut NBP z kursu i na pliku zamówień internetowych Nordvelli z materiałów do ćwiczeń, a operacje na danych sprawdziliśmy w polskim Excelu.

Jak to wygląda w praktyce

Po połączeniu z adresem API albo z plikiem JSON podgląd nie pokazuje tabeli, tylko wartości List i Record. W API NBP odpowiedź to lista z jednym rekordem o polach table, no, effectiveDate i rates, a dopiero w polu rates siedzi lista kursów. W pliku zamówień każde zamówienie ma rekord klient (typ, miasto), rekord dostawa (metoda, koszt) i listę pozycje, w której każda pozycja to rekord z polami sku, ilosc i cena_netto.

Po drodze możesz trafić na kolumnę, w której po rozwinięciu nadal stoi List, na liczbę wierszy, która po rozwinięciu nagle rośnie, i na błąd przy próbie zamiany listy rekordów na tekst: Expression.Error: Nie możemy przekonwertować wartości typu Record na typ Text. (ang. We cannot convert a value of type Record to type Text.). Gdy serwer zamiast JSON zwróci stronę HTML, na przykład z informacją o braku dostępu, zobaczysz DataFormat.Error: W danych wejściowych JSON znaleźliśmy nieoczekiwany znak. (ang. We found an unexpected character in the JSON input.).

Dlaczego tak się dzieje

Funkcja Json.Document tłumaczy JSON na wartości języka M: obiekt w nawiasach klamrowych staje się rekordem, tablica w nawiasach kwadratowych listą. Liczby przychodzą jako liczby, a daty jako tekst, bo JSON nie ma osobnego typu daty: w naszym teście pole utworzono było tekstem, a koszt dostawy liczbą. Rekord to zestaw nazwanych pól, czyli kolumny jednego wiersza. Lista to ciąg wartości, czyli przyszłe wiersze.

Stąd trzy różne polecenia. Listę zamieniasz w tabelę jednokolumnową poleceniem Do tabeli (funkcja Table.FromList), w której każdy wiersz zawiera rekord. Kolumnę rekordów rozwijasz do kolumn (Table.ExpandRecordColumn). Kolumnę list rozwijasz do nowych wierszy (Table.ExpandListColumn), a ta funkcja według swojego opisu tworzy kopię wiersza dla każdej wartości z listy. Dlatego zamówienie z trzema pozycjami zajmuje po rozwinięciu trzy wiersze, a jego pozostałe dane powtarzają się w każdym z nich.

Jak to rozwiązać krok po kroku

Połącz się ze źródłem. Dla API wybierz na karcie Dane polecenie Pobierz dane, Z innych źródeł, Z sieci Web i wklej adres, na przykład https://api.nbp.pl/api/exchangerates/tables/A?format=json, a dla pliku Z pliku, Z formatu JSON. Przy publicznym API zostaw w oknie poświadczeń dostęp Anonimowy.
Przejdź do listy z właściwymi danymi: klikaj wartości Record i List, aż zobaczysz listę rekordów, z których każdy opisuje jeden wiersz. W API NBP to lista w polu rates, w pliku zamówień lista w polu zamowienia. Każde kliknięcie dodaje krok nawigacji.
Na karcie Przekształć, w grupie Narzędzia do obsługi list, kliknij Do tabeli i zatwierdź okno bez zmian. Powstanie tabela z jedną kolumną Column1, w której każdy wiersz zawiera rekord: w naszym teście trzy wiersze dla trzech zamówień.
Kliknij ikonę rozwijania w nagłówku kolumny Column1, zaznacz potrzebne pola, odznacz Użyj oryginalnej nazwy kolumny jako prefiksu i kliknij OK. Gdy pod listą pól widzisz napis Lista może być niekompletna, kliknij najpierw Załaduj więcej, bo Power Query zbadał tylko część rekordów i mógł pominąć rzadziej występujące pola. Pole, którego w danym rekordzie brakuje, dostanie w tym wierszu wartość null.
Kolumny, w których nadal stoi Record, na przykład klient i dostawa, rozwiń tak samo. Gdy w różnych rekordach powtarzają się te same nazwy pól, zostaw zaznaczony prefiks: kolumny dostaną nazwy w rodzaju klient.typ i klient.miasto.
Kolumnę z listą, na przykład pozycje, rozwiń ikoną rozwijania i wybierz Rozwiń do nowych wierszy. Każda pozycja dostanie własny wiersz: w naszym teście trzy zamówienia dały sześć wierszy. Pozycje są rekordami, więc rozwiń kolumnę jeszcze raz, tym razem do kolumn sku, ilosc i cena_netto. Zamówienie z pustą listą albo bez listy zostaje jednym wierszem z wartością null.
Opcji Wyodrębnij wartości używaj tylko dla list prostych wartości, na przykład kodów produktów. Łączy je w jeden tekst z wybranym ogranicznikiem: w naszym teście lista NOR-1025 i KEL-1028 dała tekst NOR-1025,KEL-1028. Na liście rekordów ta sama operacja kończy się błędem konwersji typu Record na typ Text.
Na koniec ustaw typy danych. Liczby z JSON są już liczbami, ale daty, na przykład utworzono, przychodzą jako tekst i wymagają zmiany typu.

Okno rozwijania kolumny rekordów w Power Query: pola currency, code i mid, pole Użyj oryginalnej nazwy kolumny jako prefiksu i napis Lista może być niekompletna — Rozwijanie kolumny rekordów z API NBP. Napis Lista może być niekompletna oznacza, że Power Query zbadał tylko część wierszy, a Załaduj więcej przejrzy wszystkie. Prefiks jest tu jeszcze zaznaczony, w kursie odznaczamy go przed kliknięciem OK.

Jak sprawdzić, że zadziałało

Porównaj liczbę wierszy z danymi źródłowymi. Po zamianie listy zamówień na tabelę liczba wierszy ma się równać liczbie zamówień, a po rozwinięciu pozycji do nowych wierszy liczbie wszystkich pozycji. Dodaj kolumnę niestandardową z iloczynem ilości i ceny netto, zsumuj ją i porównaj z wartością w systemie źródłowym: w naszym teście trzy zamówienia dały 20566,97. Sprawdź też, czy w żadnej kolumnie nie zostały napisy List ani Record.

Pamiętaj, że po rozwinięciu pozycji koszt dostawy powtarza się w każdym wierszu zamówienia, więc suma tej kolumny po rozwinięciu policzy go wielokrotnie. Koszty na poziomie zamówienia sumuj przed rozwinięciem albo w osobnym zapytaniu. Gdy odświeżenie zgłasza nieoczekiwany znak w danych JSON, otwórz adres w przeglądarce i sprawdź, co zwraca serwer.

Poczytaj więcej
Lekcja 8 bezpłatnego kursu Power Query: Źródła danych: CSV, JSON, API, SharePoint i bazy danych
Cały bezpłatny kurs Power Query w Excelu, Power BI i Fabric z Copilotem

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Baner szkolenia Microsoft Excel - Power Query w JSystems z edytorem Power Query na ekranie laptopa

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Czym różni się rozwinięcie rekordu od rozwinięcia listy?

Rozwinięcie rekordu dokłada kolumny i nie zmienia liczby wierszy, bo każdy rekord to jeden wiersz z kilkoma polami. Rozwinięcie listy do nowych wierszy tworzy kopię wiersza dla każdego elementu listy, więc liczba wierszy rośnie. Zamówienie z trzema pozycjami zajmie po nim trzy wiersze.

Dlaczego po rozwinięciu brakuje części pól z JSON?

Okno rozwijania pokazuje pola znalezione w zbadanej części rekordów i sygnalizuje to napisem Lista może być niekompletna. Kliknij Załaduj więcej, żeby Power Query przejrzał wszystkie rekordy. Pole, którego w danym rekordzie nie ma, dostaje w tym wierszu wartość null.

Co oznacza błąd nieoczekiwanego znaku w danych wejściowych JSON?

Power Query dostał odpowiedź, która nie jest poprawnym dokumentem JSON, na przykład stronę HTML z komunikatem serwera. W naszym teście ten błąd dał tekst HTML podany funkcji Json.Document. Otwórz adres w przeglądarce, sprawdź, co zwraca serwer, i upewnij się, że poświadczenia są aktualne.

Czy Wyodrębnij wartości zastąpi rozwijanie listy pozycji?

Tylko wtedy, gdy lista zawiera proste wartości, na przykład kody produktów, które chcesz zobaczyć w jednej komórce. Lista rekordów, taka jak pozycje zamówienia z ilością i ceną, kończy się przy tym błędem konwersji typu Record na typ Text. Takie listy rozwijaj do nowych wierszy.