user for remote excel sheet

[vetty]

Hi All,

I'm using Power BI desktop and I need to get an excel file from a remote folder using:

Excel.Workbook(File.Contents("\\path_to_remote\excel.xlsx"), null, true)

But I must use a specific username and password to access that file.

How can I pass credentials in the power query statement?

Thank you so much for your attention and participation.

[devesh_gupta · Accepted solution]

@vetty In Power Query within Power BI Desktop, you can pass credentials to access a remote file using the Web.Contents function to fetch the file. You can include the username and password as part of the request headers. Here's how you can do it:

let
    // Define the URL of the remote file
    url = "\\path_to_remote\excel.xlsx",

    // Define your username and password
    username = "YourUsername",
    password = "YourPassword",

    // Encode the credentials in base64
    encodedCredentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),

    // Create a custom header with the Authorization token
    headers = [#"Authorization" = "Basic " & encodedCredentials],

    // Fetch the file using Web.Contents with the custom headers
    source = Excel.Workbook(
        Web.Contents(url, [Headers=headers]), 
        null, 
        true
    )
in
    source

Replace "YourUsername" and "YourPassword" with your actual credentials.

If you find this insightful, please provide a Kudo and accept this as a solution.

[vetty]

Great Solution, Thanks!