Excel password protected files queries into Excel data model

[DAXRichArd]

Hello,

Software: Excel - Office Professional Plus 2016

Issued: Password protected files

Solution requested: Query password protected files into Excel

Files: I used to password on the files. The files successfully open and the data appears good.

Hello,

I want to import (query) password protected excel workbooks. Is there a way to do that? I tried to query the files in a normal standard way and I receive the following message:

Unable to connect

We encountered an error while trying to connect.

Details: "File contains corrupted data."

Retry Edit Cancel

I choose edit and get a file path editor window. I do not have a place to enter a password, but I do have places to add several file paths. I put the password into one of the file paths. That does not work.

From the Excel Data tab I'll choose create a blank query. This forces the query editor window to open. I don't see anything in the query editor tabs to help me. I've hacked and hacked at it but can't find anything nor do my trial and error attempts work either.

Does anyone have a solution?

Thanks in advance for your help.

@DAXRichArd 

[ChemEnger]

There is another (bad) workaround, which worked for me at least - you can it seems import the data into another Excel sheet via Power Query as long as the source data file is open; from there you can import into Power BI.  The (admitedly massive) downside is that the intermediate Excel file won't refresh with the source file closed!

(I have voted on the Idea!)

[v-juanli-msft]

Hi @DAXRichArd 

It seems impossible to connect to protected excel files with Power BI directly.

There is an idea you could vote up

You can find some workarounds below:

https://community.powerbi.com/t5/Desktop/Power-BI-Desktop-with-Password-Encrypted-Excel-File/td-p/43...

https://www.linkedin.com/pulse/adding-password-box-power-bi-report-wyn-hopkins

Best Regards

Maggie

[DAXRichArd]

The work around I have is to remove the password from the file. My hope is to have a solution where the password can remain and I can successfully import password protected files.