Forum Discussion

arhadis's avatar
arhadis
Frequent Visitor
5 years ago
Solved

Connect to REST API -with optional parameters and basic authentication

Hi everybody    I'm completely new to Power Bi but have been asked to look into how we can conncet a Rest API to Power Bi desktop that follows this format. https://integration.website.com/year/Tra...
Need Help
Show and Tell
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    arhadis 

    No worries Anders.

    And my apologies, I appear to have put a closing square bracket in the wrong place in the code I gave you.  I didn't close the Headers record at the correct place and made the Query part of the Headers, hence the error.

    This is the correct code, I've tested it on my PC.  I've also corrected the code above.

     

    let
    URL = "THE_URL",
    Credentials = "USERNAME:PASSWORD",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Credentials), BinaryEncoding.Base64),
    Options = [
            
                Headers =[#"Authorization"=EncodedCredentials, #"Accept-Encoding"="gzip, deflate", #"Content-Type"="application/json"],
    
                Query=[

                count=Number.ToText(count),

                fromDate= Date.ToText(fromDate, "yyyy-mm-dd"),

                toDate= Date.ToText(toDate, "yyyy-mm-dd"),

                offset = if off <> null then off else "",

                brands=  if brands <> null then brands else "",

                transactionTypes=  if Transtypes <> null then Transtypes else "",

                fields=  if fields <> null then fields else "",

                styleID=  if stID <> null then stID else ""

            
            ]
            ],  

    Result = Json.Document(Web.Contents(URL, Options))

in
    Result

     

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi arhadis 

You can specify Basic auth by including your encoded credentials in the Headers.  Fill in the URL and your username and password.  You will also need to clear the permissions in the data Sources for the site you are trying to access, then when asked again by PBI, choose Anonymous access.

 

URL = "THE_URL",
    Credentials = "USERNAME:PASSWORD",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Credentials), BinaryEncoding.Base64),
    Options = [Headers =[#"Authorization"=EncodedCredentials, #"Accept-Encoding"="gzip, deflate", #"Content-Type"="application/json"],  Query=[

                count=Number.ToText(count),

                fromDate= Date.ToText(fromDate, "yyyy-mm-dd"),

                toDate= Date.ToText(toDate, "yyyy-mm-dd"),

                offset = if off <> null then off else "",

                brands=  if brands <> null then brands else "",

                transactionTypes=  if Transtypes <> null then Transtypes else "",

                fields=  if fields <> null then fields else "",

                styleID=  if stID <> null then stID else ""

            ]
            ],  
    Result = Json.Document(Web.Contents(URL, Options)),

 

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

arhadis's avatar
arhadis
Frequent Visitor
5 years ago

Hi again Phil - sorry about the late reply and thank you for the suggestions above.

I've changed the code accordingly and now I no longer get authentication errors 🙂 

Instead a new error hits me: 

It is not supposed to be easy, I guess. It looks like the Query-part is treated like a record.

 

No matter what, I'll mark your answers as solution.

Thanks for all the patience 

 

Best regards

Anders

 

 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    arhadis 

    No worries Anders.

    And my apologies, I appear to have put a closing square bracket in the wrong place in the code I gave you.  I didn't close the Headers record at the correct place and made the Query part of the Headers, hence the error.

    This is the correct code, I've tested it on my PC.  I've also corrected the code above.

     

    let
    URL = "THE_URL",
    Credentials = "USERNAME:PASSWORD",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Credentials), BinaryEncoding.Base64),
    Options = [
            
                Headers =[#"Authorization"=EncodedCredentials, #"Accept-Encoding"="gzip, deflate", #"Content-Type"="application/json"],
    
                Query=[

                count=Number.ToText(count),

                fromDate= Date.ToText(fromDate, "yyyy-mm-dd"),

                toDate= Date.ToText(toDate, "yyyy-mm-dd"),

                offset = if off <> null then off else "",

                brands=  if brands <> null then brands else "",

                transactionTypes=  if Transtypes <> null then Transtypes else "",

                fields=  if fields <> null then fields else "",

                styleID=  if stID <> null then stID else ""

            
            ]
            ],  

    Result = Json.Document(Web.Contents(URL, Options))

in
    Result

     

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • arhadis's avatar
      arhadis
      Frequent Visitor
      5 years ago

      Hi Phil 

       

      Thanks for quick reply. I actually wondered why the Query was in the Header and changed it before I wrote - but it did'nt work.

      But doing that, I must have made a typo somewhere, because now it goes through, no problems.

       

      So it perfect - thank you for all the help 🙂

       

      Best regards 

      Anders