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 is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.