Improving performance on multiple API calls

[RenierWessels]

Good day all

I have a power query model that does the following:

1. Get token from API

2. Get ID of outcome (uses token)

3. Get outcome table

I do this over 30 data sources resulting in 69 queries. 

A call to the API is usually quick, but this Power Query model is killing my RAM usage as well as my Excel. It keeps on crashing, but I NEED this to work. 

Any ideas on how to improve on my process?

Call to get_token:

let
	url = "https://my-url/",
	body = "{""username"": """&username&""", ""api_key"": """&api_key&"""}",
	Source = Json.Document(
	Web.Contents(
		url,
		[
		Headers = [#"Content-Type"="application/json"],
		Content = Text.ToBinary(body)
		]
		)
) in Source

Call to get_id:

let
    input_id = "123",
    token = get_token[token],
    url = "https://my_url/",
    Source = Json.Document(Web.Contents(url, [Headers = [#"Authorization"="JWT "&token]])),
    results = Source[results],
    results1 = results{0}
in
    results1

Call to get outcome table:

let
    token = get_token[token],
    outcome = get_id[id],
    url = "https://my_url/,
    Source = Json.Document(Web.Contents(url, [Headers = [#"Authorization"="JWT "&token]])),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
                          Record.FieldNames(#"Converted to Table"{0}[Column1]),
                          Record.FieldNames(#"Converted to Table"{0}[Column1])),
in
    #"Expanded Column1"

Thanks in advance,

R

[PhilipTreacy]

Hi @RenierWessels 

I can't tell you exactly how to check as I don't know the structure of the token or how the API works.  Is the token returned as JSON?  Text?  A record?

Usually the token contains other data (not just the token id) that tells you (for example) when it expires.  Check that against the current time before you make an API call. If it's not expired then make the data request.  If it is expired, get another token.

Regards

Phil

[RenierWessels]

Thanks Phil

This JSON is all I get:

{"token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzUxMiJ9.eyJvcmlnX2lhdCI6MTYzNjQzODkxMywiZXhwIjoxNjM2NDQyNTEzLCJ1c2VyX2lkIjoxNDUxLCJlbWFpbCI6InJlbmllci53ZXNzZWxzQGJpZHZlc3RhbGljZS5jb20iLCJ1c2VybmFtZSI6InJlbmllci53ZXNzZWxzQGJpZHZlc3RhbGljZS5jb20iLCJhdXRoX3R5cGUiOiJhcGlfa2V5In0.aLzi9BcMiCq64pwk7lJybaTB9VI8Cs8FLb3MZZo9FhMECLzz5UigtZNHYpkMQAMdV2ntePlT-VRBWpd-vQTYEPzalW3JUqPbkSl4sfpQdwhkwuj-DK0pOvvalxPuOrYPBZIKTKFSdNpIVkwco3leNRkmytokenMMDJcQ0lehnO5T-j06ueZSFq0GDRiEGkxhFOVE-SZgnt1zbhZthD-wAZuw5kwf6uJXrvimmkpgky3BGethX7axgv0dBPVXYPVuGfupL-VN4ShgU6TKT14rwZLxFIo5xMsQxWqV76GZPTSi42EEin74ZOwMnY3Qfa-wxQas8geNGx-mor5p_gxRfR5ykPrOGAANOtpjiTfJVMGTLwhuvdYEOd1IAemktA9mh7g4EkuaG1dPwSi785095ods-24PZTFpZ9VIeC4maJrtkg_qhGwtTRbtQ5jLKbangbsxGYOzk9PKgqfYcOcrFk0_eO03dqZBITPKlpTee7BGwIcQ0HZsxsQHqP7VPzza1DjMWU6T4BeE2-fReMCKk9gsIuTi6iBYPFQ3Qkh3GOiunMAcmXDNmBt22FPlHYk5UNhiy5mdvM98xlleT8PSDlzy9UoLnyg-X_DBCGJQcmMD96fGJ4jTNTf74Uj0pv3wCS6LDTeJBf1VUW_r4D1KHtOAeWlscSjmUtmiliKg"}

[PhilipTreacy]

Hi @RenierWessels 

Do you need to get a token before each API request for data?  Usually the token remains valid for a period of time. 

Try checking if the token is still valid, and if it is don't request another, just make the data request.

Regards

Phil

[RenierWessels]

Hi Phil

Thanks for taking the time to assist.

The token is valid for an hour, but I don't know how to check if it's still valid with PowerQuery. The token is a necessary parameter for each call. 

How can I:

• Check for validity?
• If not valid, check for another?
• If valid, just request the data?

Hope that makes sense.

Thank you,

R