API calls kun je prima doen in Power Query.
Er zijn wel enkele zaken waar je rekening mee moet houden:
- Authenticatie
- Omgaan met pagination
Om het je makkelijker te maken heb ik een script gemaakt wat hier rekening mee houdt. Niet iedere API is hetzelfde, maar wellicht kan dit script je helpen.
Dit script houdt mede rekening met:
- Schaalbaarheid: of het nu gaat om 10 pagina’s of 100, het script schaalt mee.
- Zowel basis authenticatie als authenticatie met een API key worden meegenomen.
Zie hieronder voor het script in tekst format zodat je het kunt kopiëren.
let
// Authentication set to anonymous in “Data source settings”
// Variables
// api_key = “your_api_key_here”, // Variable for when you are using an API key instead of basic authentication
username = “”, // Username used in authorization of your API call.
password = “”, // Password used in authorization of your API call.
categorie = “sales-invoices/?include=lineItems”, // The category for which you would like to get data.
organisatie = “MyOrganisation”, // Variable to be used in.
auth = “Basic ” & Binary.ToText(Text.ToBinary(username & “:” & password), BinaryEncoding.Base64), // Variable to be used to authenticate you in an API call.
headers = [ #”Authorization” = auth], // Setting the headers to be used in the API call.
// , [#”Authorization” = auth, #”APIKey” = api_key] // Authorization when you’re using an API key instead of basic authentication.
aantalitems = “100”, // Amount of items per page; in some API calls you can specify the amount of items per page. That is what this variable is used for.
url = “https://app.someurl.com/api/v2/” & categorie, // The concatenation of some API url and the category for which you would you like to get data (in this case, sales invoices).
// Query
Source = Json.Document(
Web.Contents( //Doing an API call…
url, // Using the url as defined above…
[Headers = headers, Query = [size = aantalitems]] // With the headers and size defined as defined above.
)
),
totalPages = Source[totalPages], // Getting the total amount of pages, because I do not just want the results stored on page 0, I want the the results of all pages.
Custom1 = List.Numbers(0, totalPages, 1), // Creating a list from 0 until the total amount of pages. For example if the total amount of pages is 2, then the list will be {0, 1, 2}.
#”Converted to Table” = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Converting the list to a table.
#”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”, {{“Column1”, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each Json.Document(
Web.Contents(
url,
[Headers = headers, Query = [page = [Column1], size = aantalitems]] // Doing an API call for every page number (every record in the table containg a row for every pagenumber) using the same url.
)
))
in
#”Added Custom”