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"



1 gwiazdka2 gwiazdki3 gwiazdki4 gwiazdki5 gwiazdek (5 głosów, średnia: 4,80 z 5)
Loading...


Powiązane

  1. Wygląda na to, że ten darmowy API którym się łączyłem i który był udostępniony na stronie https://mojepanstwo.pl/ został przeniesiony na rejestr.io i już niestety nie jest darmowy…
    W wolniejszej chwili rozejrzę się za innym darmowym źródłem. Dam znać jak uda mi się coś znaleźć. Jeśli nie to niestety zostanie tylko płatna wersja lub utworzenie własnej bazy z danymi z NIP.

    Aktualizacja 2020-04-26
    API zostało odblokowane i ponownie działa.
    Zmienił się nieco układ kolumn w źródle, ale zapytanie z wpisu powinno działać póki co zgodnie z zamieszczonym opisem.

    Aktualizacja 2020-12-23
    Do wpisu dodałem pełny kod zapytania pozwalającego na pobranie danych z rejestr.io z wykorzystaniem kodu autoryzacyjnego.

    1. Maciej

      Witam, Posiadam klucz api ale chyba wygląda na to że bez vba się nie obejdzie.

      1. Maciej, jeśli możesz to podeślij proszę namiar na to API na excel@jakubkrupa.pl i zobaczymy co da się zrobić 🙂

    2. Lucjan

      Witam, czy udało się Panu pobrać dane do Exekl’a z rejestru.io dysponując kluczem autoryzacyjnym. Aktualnie próba pobrania danych kończy się pytaniem o autoryzację, która i tak nie działa, zaś wybranie opcji jako anonim nie kończy się wcale.

      1. Dzień dobry,
        Nie, niestety nie miałem okazji korzystać tutaj z klucza autoryzacyjnego.

    1. Dzięki za link 🙂 Zerknę na to w weekend.

      1. Tomek

        Mam nadzieję ze uda się to wykorzystać. 😉

      2. basmen

        Z nieukrywaną ciekawością zapytam: czy udało się połączyć z tym drugim API?

        1. Z tym drugim APi nie udało mi się połączyć. Spróbuj ponownie metody z mojego wpisu. Póki co powinna znów działać poprawnie,

      3. Paweł

        Jakub udało się znaleźć rozwiązanie problemu?

        1. Paweł,
          Przyznam szczerze, że próbowałem kilkukrotnie wracać do tego tematu, ale bez zadowalającego efektu.
          Dobra wiadomość jest taka, że na ten moment rozwiązanie z wpisu powinno działać zgodnie z założeniami.

  2. Rafał

    Może się komuś przyda jak wywołać API z autoryzacją w headerze np do rejestr.io

    let
    Źródło = Json.Document(Web.Contents(„https://rejestr.io/api/v1/krs?name=xxxxxxxx”,
    [
    Headers = [#”Content-Type”=”application/json”, #”Authorization”=”xxxxxxxxxxxx”]
    ] )),
    #”Przekonwertowane na tabelę” = Record.ToTable(Źródło)

    in
    #”Przekonwertowane na tabelę”

    1. Dzięki za kod. U mnie zadziałało poprawnie.
      W zapytaniu z wpisu należy przede wszystkim podmienić kod w trzecim kroku na coś takiego:
      #"Dodano kolumnę niestandardową" = 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"]])))),

      Zaktualizowałem wpis o pełny kod zapytania z opcją z kodem autoryzacyjnym.

  3. seba

    Czy istnieje możliwość pobrania pliku.
    Ponieważ jak wpisuje kod M to mi nie działa rozwijanie pokazuje mi sie nip i kolumna. A pozniej wyskakują ERRRORY

    1. Odezwij się do mnie na maila 😉

  4. seba

    dziękuje ci bardzo Robisz kawał dobrej roboty

  5. Emilia

    Czy komuś udało się pozyskać inne dane niż te, które autor przedstawia w rozwiązaniu powyżej?
    Chodzi mi o dane takie jak: reprezentacja, pełnione funkcje, prokurenci, itd.

    1. Sam nie wyciągałem innych danych, ale informacje z instrukcją jak tego dokonać powinny być zawarte w dokumentacji API: https://rejestr.io/api-krs#section/Pola-KRS-udostepniane-przez-API

  6. Error

    Dzień dobry,
    Dlaczego pojawia się komunikat o treści:
    „Expression.Error: Nie można rozpoznać nazwy „Zmieniono typ”. Sprawdź, czy została wpisana poprawnie.”

    Do nowej kolumny niestandardowej wklejam: 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”]]))))” i otrzymuję powyższy komunikat o błędzie 🙁

    1. Dzień dobry,
      Czy używasz kodu autoryzacyjnego i wklejasz go w odpowiednie miejsce? Jeśli tak to sprawdź jeszcze czy krok wcześnie jest nazwany „Zmieniono Typ”

  7. Adam

    Witam,

    proszę rozwinąć skrót myślowy dotyczący zamiany z Table na Record.
    W żaden sposób nie mogę odnaleźć tego kroku. Jak wygląda przejście z Table na wspomniany Record?
    „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”

    1. Dzień dobry,
      Wystarczy kliknąć w dowolny wiersz w drugiej kolumnie i nazwa „Table” powinna zmienić się na „Record”, które dalej rozwijamy strzałkami z nagłówka kolumny.

  8. Mateusz

    Bardzo fajny opis, przydatna funkcjonalność! Dzięki!
    Próbuję to właśnie zrobić ale mi nie wychodzi i nie wiem dlaczego.
    Raz, że widzę na stronie rejestr.io że w dokumentacji API opisy zapytań są w innym układzie czyli: https://rejestr.io/api/v2/org. W poście jest v1. Czy to moze mieć znaczenie?
    Poza tym mam jakieś komunikaty z Power Query o Formula.Firewall a jak skróciłem zapytanie tylko do linii „Web.Contents” to mam komunikat „Expression.Error Dostęp do tego zasobu jest zabroniony”. Nie wiem jak sobie z tym poradzić. Googlam to wychodzi, że trzeba poprawić edycję uprawnień. Wszędzie zmieniam na „Organizacyjne” ale nic to nie daje. Czy możecie proszę z tym jakoś pomóc?

    1. To API się zmienia co jakiś czas więc jeśli tak piszą w dokumentacji to spróbuj podmienić.
      Co do Formula.Firewall to spróbuj zmienić ustawienie ignorowania poziomów prywatności w ustawieniach tego źródła danych (albo w opcjach zapytania Power Query)
      Plik > Opcje i ustawienia > Ustawienia źródła danych > Edytuj uprawnienia > Poziom prywatności > Brak

  9. Piotr

    Witam,
    a czy jest możliwość pobrania danych z https://api.stat.gov.pl/Home/RegonApi
    ?

    1. Jeśli jest przygotowane API to powinno się dać. Ja akurat nie pobierałem danych z tego źródła.

  10. Alek

    Cześć,
    Czy ktoś wykorzystywał usługę oficjalną KRS, pod adresem:
    https://prs.ms.gov.pl/krs/openApi

    Dane pobierają się okej, ale imiona i nazwiska osób z KRS są wykropkowane – co zabija całą funkcjonalność w moim przypadku…

  11. Mariusz

    Dzień Dobry,
    czy może ktoś się podzielić plikiem excel, gdzie stworzone jest już zapytanie pod ściąganie danych:
    https://prs.ms.gov.pl/krs/openApi

    chciałbym po NIP filtrować numery PKD

  12. Andrzej

    Cześć,
    mam potrzebę gdy po nazwie (albo jej fragmencie – bo może być wpisana nieprawidłowo) chciałbym uzyskać dane adresowe i nr NIP np.
    Czy to jest możliwe – jak należy zmodyfikować zapytanie?

    1. Cześć, nie przerabiałem takiego przypadku ale biorąc pod uwagę możliwość zwrócenia bardzo wielu wyników dla wskazanego fragmentu nazwy to takie zapytanie mogłoby działać mało efektywnie (długi czas odpytywania źródła i zwracania wyników).

Komentarze