Connect to REST API -with optional parameters and basic authentication

[arhadis]

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/Transactions/?count={COUNT}&offset={OFFSET}&brands={BRANDS}&fro...

It is only the count, toDate and fromDate that are mandatory and hence you can call the service successfully without the specifying make, transactionsTypes etc.

I've tryed this:

let Source1 = (count, off, brands, fromDate, toDate,  Transtypes, fields, stID) =>

let

    Source = Json.Document(

        Web.Contents("https://integration.website.com/year",

        [

            RelativePath="Transactions",

            [Headers=[#"Accept-Encoding"="gzip, deflate", #"Content-Type"="application/json"]],                   

            Query=[

                count=Number.Totext(count),

                offset=off,

                brands=brands,

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

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

                transactionTypes= Transtypes,

                fields= fields,

                styleID=  Number.ToText(stID)

            ]

      ]

        )

       )

in

    Source

in Source1

But it won't work when leaving the optional parameters blank (error: cannot convert NULL to text).

Another problem is the authentication - if I fill out all the parameters and avoid conversion errors then it cannot connect to the webservice - it won't accept my credentials even though they work perfectly when calling the service from Postman or if I just connect to Power Bi web with the entire string (e.g.: 

https://integration.website.com/year/Transactions/?count=100&offset=1&brands=brand1&fromDate=

2017-01-01&toDate=2020-10-10&transactionTypes=1,2,3,4&fields=field1, field2, field3, field4&styleID=1232535&storeID=55)

The service requires Basic Authentication.

Hope this makes sense - and any suggestions are much, much appreciated

Best regards

Anders

[PhilipTreacy]

Hi @arhadis 

When some parameters are left empty an implicit conversion to text is still happening in the Query section e.g. offset=off and if off is null it can't be converted to text.

Change the code to this

            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 ""

            ]

As for the authentication problem,how are you performing the auth?  Can you please supply the M code for that process.

Regards

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]

Hi Phil

Thank you so much for your reply; I've changed my code and now the conversion error no longer seem to be an issue 🙂

Regarding the authentication, I just use the Power Bi connection wizard (in lack of better words):

I've read that it is possible to specify authentication method in the header in power bi-web, but I'm using the desktop version and here the option is missing. I would'nt know how to write the m-code that could solve this.

If you have any suggestions they are all very appreciated.

Best regards 

Anders

[PhilipTreacy]

Hi @arhadis 

When I go to https://integration.bilstatistik.dk/2018 in my browser it prompts me to login.  This is not the same process as providing authentication in PBI or PQ.

What does the API documentation for bilstatistik say about how to perform authentication with their site?  Can you share this info, I can't find any info or links on their website about their API.

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]

H again Phil

And thanks again for your feedback and time

The thing is that the documentation actually says that you should use Basic Authentication - and nothing more (well, it also specifies the headers: Accept-Encoding: gzip, deflate and Content-Type: application/json).

I think it is weird that it works when I hardcode all the mandatory parameters (and the additional optional filters) whilst the more 'dynamic' code using Relative Path and Query does not. It seems not an option to add the username and password in the header of the request, so I'm at loss. 

I'll see if I can acquire some more detailed documentation from our developers today.

Thanks again

Best regards 

Anders

[PhilipTreacy]

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]

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 · Accepted solution]

@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]

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