NIP z KRS w Excelu przez Power Query

Jednym z ciekawszych zastosowań, jakie znalazłem dla Power Query, jest pobieranie danych z Internetu za pomocą połączeń z API. Dla przykładu zaprezentuję metodę pozwalającą na szybkie pobranie danych z Krajowego Rejestru Sądowego. Informację będą dotyczyć organizacji zarejestrowanych w KRS i zaczytywane będą wyłącznie na podstawie ich NIPu wpisanego do arkusza Excel. W sieci można znaleźć wiele podobnych rozwiązań – w większości opartych na VBA, niestety głównie płatnych. Prezentowana przeze mnie metoda pobierania danych NIP z KRS w Excelu przez Power Query będzie przystępna dla każdego i całkowicie darmowa 🙂

Tworzymy arkusz Excel

Na początku zakładamy skoroszyt Excel, do którego będziemy pobierać dane z KRS. API, które nas interesuje zostało udostępnione na stronie: https://mojepanstwo.pl/api/krs
Do arkusza będziemy zaczytywać dane wg NIP (strona oferuje również inne filtry). Na potrzeby utworzenia połączenia interesuje nas poniższy fragment:

W naszym arkuszu wpisujemy nr NIP interesujących nas firm. Możemy zapisać jeden numer lub więcej. Wpisane dane zaczytujemy do Power Query wybierając opcję „Z tabeli/zakresu”.

Domyślnie numery NIP zaczytają się do zapytania jako liczby całkowite. W kolejnych krokach będziemy te numeru łączyć z linkiem API, który jest tekstem. Aby Power Query poprawnie przeprowadziło taka operację obie części musza być przechowywane pod postacią tekstową. Dlatego zmieniamy domyślny typ danych kolumny NrNIP na tekst.

Dodanie kolumny niestandardowej łączącej z API KRS

W następnym kroku zapytania dodajemy kolumnę niestandardową. Jako formułę wklejamy poniższe:
=Table.AddColumn(#"Zmieniono typ", "Kolumna", each Json.Document(Web.Contents("https://api-v3.mojepanstwo.pl/dane/krs_podmioty.json?conditions[krs_podmioty.nip]="&[NrNIP])))
Dzięki temu zabiegowi numer przechowywany w tabeli w kolumnie NrNIP będzie łączył się z fragmentem linku do KRS z API. W efekcie tego kroku powstanie nowa kolumna przechowująca obiekty typu „Table”.

Klikając jednokrotnie na dowolny z nich zmienimy obiekt „Table” na „Record”. Następnie rozwijamy zawartość tych obiektów klikając dwie przeciwne strzałki w nagłówku. Domyślnie zaznaczone będą wszystkie nowe kolumny, nam potrzeba będzie tylko ta opisana jako „Dataobject”.

Nowa kolumna ponownie będzie zawierała obiekty typu „List”, rozwijamy je klikając w dwie przeciwne strzałki i wybierając „Rozwiń do nowych wierszy”.

Ponownie otrzymujemy obiekty typu „Record”. Znów rozwijamy ich zawartość strzałkami w nagłówku.

Przed nami jeszcze jedno, ostatnie rozwinięcie zawartości kolumny. Właśnie dokopaliśmy się do danych przechowywanych w Krajowym Rejestrze Sądowniczym. Do wyboru mamy teraz ponad 80 kolumn. W zależności od rodzaju spółki część z nich może być pusta. Tym razem pozostawiamy zaznaczone tylko te kolumny, których dane są nam potrzebne. Opcjonalnie odznaczamy checkbox „Użyj oryginalnej nazwy jako prefiksu” aby nadmiernie nie wydłużać nazw w nagłówkach kolumn.

Ładowanie danych do arkusza Excel

Zapytanie pobierające dane NIP z KRS w Excelu przez Power Query na podstawie połączenia z API jest zasadniczo gotowe. Pozostaje załadować je do arkusza Excel. Ja zaczytałem swoje tuż obok tabeli źródłowej ze sprawdzanymi numerami.
Chcąc sprawdzić inne numery wystarczy podkleić je w kolejnej wolnej komórce pod tabelą źródłową lub wstawić je w miejsce poprzednich. Następnie odświeżamy naszą tabelę wynikową i po kilku sekundach widzimy nowe dane zaczytane prosto z Krajowego Rejestru Sądowego 🙂

Kliknij aby zobaczyć inne wpisy o Power Query.

Cała treść wygenerowanego zapytania w języku M:

let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"NrNIP", type text}}),
#"Dodano kolumnę niestandardową" = Table.AddColumn(#"Zmieniono typ", "Niestandardowe", each Table.AddColumn(#"Zmieniono typ", "Kolumna", each Json.Document(Web.Contents("https://api-v3.mojepanstwo.pl/dane/krs_podmioty.json?conditions[krs_podmioty.nip]="&[NrNIP])))),
Niestandardowe = #"Dodano kolumnę niestandardową"{1}[Niestandardowe],
#"Rozwinięty element Kolumna" = Table.ExpandRecordColumn(Niestandardowe, "Kolumna", {"Dataobject"}, {"Kolumna.Dataobject"}),
#"Rozwinięty element Kolumna.Dataobject" = Table.ExpandListColumn(#"Rozwinięty element Kolumna", "Kolumna.Dataobject"),
#"Rozwinięty element Kolumna.Dataobject1" = Table.ExpandRecordColumn(#"Rozwinięty element Kolumna.Dataobject", "Kolumna.Dataobject", {"data"}, {"Kolumna.Dataobject.data"}),
#"Rozwinięty element Kolumna.Dataobject.data" = Table.ExpandRecordColumn(#"Rozwinięty element Kolumna.Dataobject1", "Kolumna.Dataobject.data", {"krs_podmioty.regon", "krs_podmioty.siedziba", "krs_podmioty.wartosc_kapital_zakladowy", "krs_podmioty.nazwa", "krs_podmioty.adres_kod_pocztowy", "krs_podmioty.forma_prawna_str", "krs_podmioty.adres"}, {"krs_podmioty.regon", "krs_podmioty.siedziba", "krs_podmioty.wartosc_kapital_zakladowy", "krs_podmioty.nazwa", "krs_podmioty.adres_kod_pocztowy", "krs_podmioty.forma_prawna_str", "krs_podmioty.adres"})
in
#"Rozwinięty element Kolumna.Dataobject.data"

Pobieranie danych ze strony rejestr.io z kodem autoryzacyjnym

Poniżej pełny kod zapytania dla opcji z użyciem kodu autoryzacyjnego ze strony rejestr.io. Kod wklejamy w 3cim kroku zapytania, dokładnie w miejsce : Authorization="tutaj wklejacie-Wasz-kod-autoryzacyjny"

let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"NrNIP", type text}}),
#"Dodano kolumnę niestandardową" = Table.AddColumn(#"Zmieniono typ", "Niestandardowe", each Table.AddColumn(#"Zmieniono typ", "Niestandardowe", each Table.AddColumn(#"Zmieniono typ", "Kolumna", each Json.Document(Web.Contents("https://rejestr.io/api/v1/krs?nip="&[NrNIP], [Headers=[#"Content-Type"="application/json", Authorization="tutaj wklejacie-Wasz-kod-autoryzacyjny"]]))))),
Niestandardowe = #"Dodano kolumnę niestandardową"{0}[Niestandardowe],
Niestandardowe1 = Niestandardowe{0}[Niestandardowe],
#"Rozwinięty element Kolumna" = Table.ExpandRecordColumn(Niestandardowe1, "Kolumna", {"items"}, {"Kolumna.items"}),
#"Rozwinięty element Kolumna.items" = Table.ExpandListColumn(#"Rozwinięty element Kolumna", "Kolumna.items"),
#"Rozwinięty element Kolumna.items1" = Table.ExpandRecordColumn(#"Rozwinięty element Kolumna.items", "Kolumna.items", {"address", "business_insert_date", "current_relations_count", "data_fetched_at", "duns", "first_entry_date", "historical_relations_count", "id", "industries", "is_opp", "is_removed", "krs", "last_entry_date", "last_entry_no", "last_state_entry_date", "last_state_entry_no", "legal_form", "name", "name_short", "nip", "regon", "type", "w_likwidacji", "w_upadlosci", "w_zawieszeniu", "ceo"}, {"address", "business_insert_date", "current_relations_count", "data_fetched_at", "duns", "first_entry_date", "historical_relations_count", "id", "industries", "is_opp", "is_removed", "krs", "last_entry_date", "last_entry_no", "last_state_entry_date", "last_state_entry_no", "legal_form", "name", "name_short", "nip", "regon", "type", "w_likwidacji", "w_upadlosci", "w_zawieszeniu", "ceo"}),
#"Rozwinięty element address" = Table.ExpandRecordColumn(#"Rozwinięty element Kolumna.items1", "address", {"city", "code", "country", "house_no", "post_office", "street"}, {"address.city", "address.code", "address.country", "address.house_no", "address.post_office", "address.street"}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Rozwinięty element address", each ([is_removed] = false))
in
#"Przefiltrowano wiersze"