Statistikaameti andmebaas: Andmed Excelisse Power Query abil

Sissejuhatus

Selleks, et saada Statistikaameti andmebaasist andmetabel Excelisse, on tegelikult mitu võimalust – kas eksportida andmed veebilehel pakutavasse vormingusse või küsida andmeid hoopis Power Query päringuna. Viimane annab lisavõimaluse päringu andmete automaatseks värskendamiseks või päringu korduvkasutamiseks teiste andmetabelite puhul.

Päringu ettevalmistamine

Koosta Statistikaameti andmebaasis endale vajalike tunnustega päring (nii et näed juba küsitud andmeid), seejärel leia lehe lõpust sektsioon API päring tabeli kohta.

Loo (ja salvesta) endale nüüd üks Exceli fail, kuhu saad kopeerida järgmised andmed:

• päringu link (URL) ning
• JSON päring

TEXTJOIN funktsioon aitab päringu sisu ühendada kokku ühte lahtrisse.

Piirkonnale B1:B2 võib anda ka nime (Formulas ➯ Define Name), näiteks Query.

Päringu loomine Power Query abil

Järgnevalt vali piirkond B1:B2 ning vali Exceli Data-menüüst nupp From Table/Range.

Edasi vali Power Query redaktoris Home ➯ Advanced Editor kopeeri sinna järgneval lehel olev kood ning vajadusel korrigeeri real 3 olev piirkonna nimi („Query“) vastavaks oma failis kasutatud nimele.

let
    /* Päringu allikaks on nimeline piirkond Query  */
    Source = Excel.CurrentWorkbook(){[Name="Query"]}[Content],

    /* Asendame väljundi tüübi "json-stat2" tüübiga "csv"  */
    #"Replaced Value" = Table.ReplaceValue(#"Source","json-stat2","csv",Replacer.ReplaceText,{"Column1"}),

    /* Võtame päringu URL-i ja JSON-lause vastavalt ridadelt 0 ja 1, et neid kasutada järgnevas lauses  */
    Column1_url = #"Replaced Value"{0}[Column1],
    Column1_query = #"Replaced Value"{1}[Column1],

    /* Päringu lõpptulemuse lugemine Statistikaameti andmebaasist veebipäringu abil  */
    Query = Csv.Document(Web.Contents(Column1_url, [Headers = [#"Content-Type"="application/json"], 
        Content = Text.ToBinary(Column1_query) ]))

in
    Query

Päringu tulemus peaks nüüd olema midagi niisugust ning seda saad edasi töödelda juba vastavalt oma vajadusele Power Query keskkonnas.

Ühe soovitusena võib sellest päringust teha nüüd Reference-tüüpi koopia, mida siis juba edasi töödelda (määrata esimene rida pealkirjaks ning korrastada andmetüübid arvulistes veergudes).

Arvuliste tunnuste korrastamisel võib olla abi lokaadi muutmisest (Using Locale…) ning vigade asendamisest tühja väärtusega (Replace Errors ➯ null).

Kuidas see kõik täpsemalt käib, sellest räägime koolitusel!