Odata feed URL basic authentication

[Anonymous]

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

[Anonymous · Accepted solution]

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

[v-lili6-msft]

hi, @Anonymous

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

[Anonymous]

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