cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
palo173 Regular Visitor
Regular Visitor

Odata feed URL basic authentication

Hi,

 

I created OData.Feed and manualy insert my username and password into basic authentication. It works fine, but there is my credentials all the time in the same excel workbook.

My idea is ...  when somebody refresh Odata connection, he has to insert his own credentials. For example username and password will be in URL for data feed and it will be a variable. Something like example below for variables for date_from and date_to in URL.

 

Is it possible add here some authentication? or any idea?

 

let
    Source1 = Excel.CurrentWorkbook(){[Name="date_from"]}[Content],
    from = Source1{0}[date_from],
    Source2 = Excel.CurrentWorkbook(){[Name="date_to"]}[Content],
    to = Source2{0}[date_to],
    Source3 = Excel.CurrentWorkbook(){[Name="profile"]}[Content],
    pr = Source3{0}[profile],

    Zdroj = OData.Feed("http://x2-tst/tss/ProfileData(path='" & pr & "')?contextFrom=" & from & "&contextTo=" & to, null, [Implementation="2.0"])
IN

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
palo173 Regular Visitor
Regular Visitor

Re: Odata feed URL basic authentication

It is my final solution:

 

...

    Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content],
    options = Source4{0}[encode],

    Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]),

...

variable "options" is store in cell in excel sheet.

Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.

 

It works perfectly.

 

This is function for excel for encoding string from username: password to base64 encoded string. May be useful.

Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)
  Dim objXML
  Dim objNode
  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text
  Set objNode = Nothing
  Set objXML = Nothing
End Function

 

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Odata feed URL basic authentication

hi, @palo173

After my research, I'm afraid it is difficult to achieve in Power BI for now.

And if you could try to Row-level security (RLS) with Power BI Desktop.

 

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
palo173 Regular Visitor
Regular Visitor

Re: Odata feed URL basic authentication

Thank you @v-lili6-msft , but do you think, is it possible to do it in Excel Power Query, becasue I work with Excel.

I thought something like this:

[Headers =[#"Authorization"="Basic username: pasword"]]

but it doesn't work for me and I don't know why.

 

Palo

palo173 Regular Visitor
Regular Visitor

Re: Odata feed URL basic authentication

It is my final solution:

 

...

    Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content],
    options = Source4{0}[encode],

    Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]),

...

variable "options" is store in cell in excel sheet.

Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.

 

It works perfectly.

 

This is function for excel for encoding string from username: password to base64 encoded string. May be useful.

Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)
  Dim objXML
  Dim objNode
  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text
  Set objNode = Nothing
  Set objXML = Nothing
End Function

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 234 members 2,818 guests
Please welcome our newest community members: