PowerBI API call with loop

[Anonymous]

Hi all,

i'm trying to accomplish to get all data from 6 different URL's into one table.
The URL's need to be generated based on a list. So far i fail to understand all the examples on the internet, so i'm hoping someone can tell me what i'm doing wrong.

I get data from the Web using this URL:
https://www.ealyze.nl/api/ean/list/format/csv/key/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
So far so good, data comes in i see the different EAN numbers. Now to get detailed info on these EAN numbers, i need to call this URL:
https://www.ealyze.nl/api/ean/innerdayvalues/format/csv/key/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/start/...

So i did the following steps

• Converted the EAN column to a List

• Created a parameter called pEAN

• Created a function called CallAPI

• Invoked the function to the table containing the EAN number

And then i only see a new column named CallAPI with rows Binary 😞
How can i convert the binary to columns?

This is the function i use

 let
     CallAPI= (EAN as text, optional Timeout as number) as any => 
    
     let
         WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 
         WebResponse = Web.Contents("https://www.ealyze.nl/api/ean/innerdayvalues/format/csv/key/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/start/20210401/code/" 
         & EAN,  [Timeout = WebTimeout])
     in
         WebResponse
 in
     CallAPI

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Sorry for editing the original reply, after deep research, I have some new solution for your issue. You need to use text. from binary to parse content return by web.content. So you need to change your function to 

 let
     CallAPI= (EAN as text, optional Timeout as number) as any => 
    
     let
         WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 
         WebResponse = Text.FromBinary(Web.Contents("https://www.ealyze.nl/api/ean/innerdayvalues/format/csv/key/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/start/20210401/code/" 
         & EAN,  [Timeout = WebTimeout]))
     in
         WebResponse
 in
     CallAPI

Please try again.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai