One of the best things about Power Pivot and Power Query is that once you’ve learned how to use them in Excel, you can easily transfer that knowledge to Power BI:
But not only do your skills transfer over — sometimes the actual work does too. For example, did you know you can import a data model that was created in Excel Power Pivot into Power BI? Here’s how.
Why move from Excel to Power BI?
If both Power BI and Excel contain the same “power”-ful tools, you might be asking, then why ever make the move? To answer the question, let’s take a look at the history of Excel.
Power BI as the third piece of “modern Excel”
You may have heard the term “Modern Excel” to describe a suite of impressive data analytics features rolled out starting in Excel 2010. Among the most famous are Power Query for data cleaning and Power Pivot for data modeling.
But there’s a lost sibling in this story: Power View. Initially built for Excel, Power View was conceived as a tool for building interactive dashboards and reports. Over time, the idea of Power View was merged into Power BI entirely. In fact, newer version of Excel do not even include Power View.
That means Power BI boasts many more features for building interactive dashboards than Excel. At the same time, it’s a less familiar tool that may be difficult for analysts to build and share with. That’s why it’s not a bad idea to start building your data model in Excel — if you hit a point of dashboard sophistication where Power BI is just a better choice, you can always transfer it there. Let’s see how that’s done now.
Making the move
Your exercise files contain a workbook called superstore_data_model.xlsx. This is a simple data model consisting of three tables, with a couple of useful measures and calculated columns created as well. With this file closed in Excel, open Power BI Desktop and head to Import > Power Query, Power Pivot, Power View:
From here, you can navigate to and select superstore_data_model.xlsx.
Loading to Power BI
Upon importing your model to Power BI, you’ll get a warning sign that Power BI will do its best to import the data. Click Start:
First, you have the option to either make a copy of the Excel data, or keep a live connection. While the ability to change the Excel workbook is more convenient, it comes at the expense of slower performance in Power BI. For the sake of simplicity, I will make a copy of the data for the exercise file, rather than keep the connection.
You should now see a message that Power BI successfully imported your workbook along with its queries, data model tables, and any KPIs or measures created:
While the previous screen doesn’t state it, our data model’s calculated columns were also successfully imported. We can check that in Data View:
Finally, head to Model View to confirm the relations between tables were properly defined:
Have you imported a data model from Excel to Power BI? Where do you prefer building data models and why? Let me know in the comments.
Leave a Reply