Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PowerBI API call with loop

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/20210401/code/8716948310004

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

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

1 Reply

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    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