Power BI Tips: Excel vs. Power BI – when to use which?
18 January 2018
We mentioned that there were several key things that Power BI can do. Let’s see how they compare to the tools that we have in Excel.
- Collect data from a range of sources / clean and transform data: the technology that Power BI uses is pretty much exactly the same as that available in Excel. In Excel 2010 and Excel 2013, you can download the Power Query add-in from the Microsoft website, or in Excel 2016, the Get and Transform feature is built into the Data tab in the ribbon. At the moment, the technology in Power BI is a bit more advanced – any new features are generally introduced in Power BI before they are brought back into Excel. Because Power BI was designed with this task in mind, it is also a bit more efficient (read: faster) at getting and transforming your datasets.
Get and Transform in Excel 2016
Get Data in Power BI
- Link multiple data sources together: again, the technology being used in Power BI is the same Power Pivot technology that’s been around since Excel 2010. The DAX language used to create calculated columns and measures (or calculated fields, if you’re using Excel 2013) is essentially the same in both Excel and Power BI, with only minimal differences between them.
- Creating charts with a point-and-click user interface: There have been many upgrades to the process of creating charts in Excel; from the new charts available in 2016 to the Quick Analysis pop-up that helps you to choose appropriate charts for your data type. However, it generally seems easier to build charts in Power BI, with a greater variety of charts available and an easy PivotChart type interface to click and drag fields into the relevant chart parameters. On the flip side though, Excel provides greater flexibility to customise charts, with more formatting options, along with the ability to link titles and labels back to values in the dataset.
Insert Chart in Excel 2016
Charts and visualisations in Power BI
- Sharing reports and dashboards: this is where Power BI and Excel differ quite greatly. Traditionally, in Excel, someone will build a report or dashboard, and they would need to email or otherwise send the file to a colleague. With Power BI, the grand idea is that you will upload your dataset to the Power BI Service (we’ll go into more detail on what that is exactly in subsequent weeks) which is essentially a cloud-based server, and colleagues can log in to see your reports and dashboards, updated automatically with the most recent data.
This is a summary of some of the key similarities and differences between the tools available Power BI and those in Excel. Next week, we’ll highlight cases and situations where you should use one or the other, and why it’s a case of horses for courses. See you next week!