Power BI Issue Fix: Import from Excel Workbook Contents; Password Protection Failure

One of the great features of Power BI Desktop is the ability to import your entire Power Pivot Excel model into it. This is an awesome feature, because you can import the entire model including tables, relationships, calculations, and hierarchies into the Power BI. This is a great migration feature from Excel to Power BI. However, this feature ocassionally doesn’t work as expected. One of these situations is when your Excel file is password protected. In this post I’ll show you what happens when your Excel file is password protected, what error do you receive in Power BI, and how to fix this issue and continue successfully with importing your model into Power BI.

Import Excel Workbook Content

Getting data from an excel file is totally different from this option that we are talking about now. When you get data from an excel file, you only care about the data stored in the Excel file. However, sometimes you have already an Excel file with Power Pivot model in it, this Excel file might have been through development process and maintained by a business user from finance team for about 2 years already. It would take really long time to re-create such model in Power BI from scratch. Instead, you would like to migrate it to Power BI. The process of migration from an Excel Power Pivot model to Power BI exists fortunately, and this process is called Import Excel Workbook Content. You can find this option in Power BI Desktop, in File Menu.

This process usually is smooth and straigtforward, however, sometimes you get errors through this process. Below is an example;

Error: ‘*.xlsx’ is corrupt or not a valid Excel file

Sometimes the errors through the process is not much clear. for example, you may receive error below, and this is happening when your Excel file is actually working pretty fine in Excel.

One of the reasons for this error is actually; Password Protection of the Excel File

Remove the Password Protection from the excel file

I have seen that one of the reasons for this error is that the Excel file is password protected. If your file has the same situation, then you can remove the password through this process:

Open the Excel file in Microsoft Excel, then go to File Menu,

Then click on Protect Workbook and select Encrypt with Password

In the new Password window, remove the password

click ok. Password is now removed, save the file

Import into Power BI

After removing the password, you can try to import it again, this time migration would complete successfully.

Note: there might be other reasons that also fails the migration. Password protection is not the only reason for failure, but it is one of them.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply