Here is the video of my session in the Paris Power Platform event – Power BI for Excel users. How Power BI can benefit every user of Excel – without even installing Power BI! Power BI can also be a publisher of raw data for Excel. Analyzing data in Power BI and Power Pivot can happen right within Excel – without any extra cost.
Contents
The problem
Usually, Power BI is used as a separate tool, as a separate project by a separate team – typically driven by IT. That is the worst way to use Power BI. It was designed to help EVERY user of data – which basically translates to every user of Excel. It is sad to see that most users of Excel do not even know that they have half of Power BI capabilities right within Excel.
The design principle of Power BI (and Power Platform) is to empower every user – commoditize and democratize BI. But sadly that has not happend.
In this video I provide a simple, practical and flexible way to make every Excel user benefit from Power BI – without any additional investment or license cost.
Table of Contents
- Intro
- Why people don’t know about Power BI
- Power BI inside Excel
- Data Analysis Steps
- Two types of inefficiency
- What is analysis? Simplest definition…
- Clean data – 10 item checklist
- Get and Transform data
- Instant Clean-up examples
- Handling Large, slow Excel files
- The database inside Excel – Data Model
- Data model based Pivot Table advantage
- Better analytics in Excel
- New Excel functions
- Best Practice: Try Power Query for data cleanup
- Featured Tables – Power BI to Excel publishing of data
- Distribute raw data centrally – no more CSV dumps
- Important Power BI visuals
- Three Dimensional Maps in Excel
- Never say – I will get back to you – using Q&A
- Excel Ideas and Q&A
- How to use Power BI for free and quntify value
- Why Power BI is useful even if you have other BI tools
- Call to action – what YOU can do to help everyone use Power BI
Power BI for Excel (and Inside Excel)
Power BI Import data and clean-up
Within Excel this part is represented by Power Query and the Get and Transform Data ribbon. It has exactly the same syntax and features as the Power BI based Power Query. Some data sources are not available in Excel but that is a minor disadvantage.
Power BI Publisher for Excel
Power BI can be used as a publisher of raw or input data for Excel. This is a very important use case which is completely ignored by most companies.
Despite having all kinds of BI tools, a typical user is still exporting data as CSV / Excel – that too repeatedly and creating their own reports.
The raw data which users play with (or rather – struggle with) every day leads to unimaginable amount of misery and colossal waste of time.
Unfortunately, users do not think this is a problem – because they have been doing it the same way for decades. That is why this never gets escalated to IT. On the other hand, IT is only focusing on the BI part of it- the ETL part is not very interesting.
That is where Power Query can completely transform the game across the organization.
Using Power BI as a publisher of raw data using Certified datasets, Excel import and cleanup can be completely eliminated. In fact, we got two benefits – Time saving and accuracy – dramatically. Above all, you got all this without writing a single line of code.
I don’t know of a single organization which has looked at this low-lying opportunity seriously and exploited it to everyone’s advantage. Indeed, there is a lot to do. Despite the current state of affairs, why not start using it at your level?
Analyzing data with Power bi and Power Pivot in Excel
The other opportunity is the Power Pivot data model. Most Excel users are oblivious to the presence of such a powerful tool. The data that is imported and cleaned is best kept inside the data model instead of the traditional destination of Excel sheet.
When the volume of data is slowing down the system, Data Model is an elegant solution – just waiting to be exploited. Unfortunately, it has been waiting for 10 years now. Nobody noticed. Sad!
Analyzing data using Power Pivot and Power BI is exactly like a regular Pivot table. The only difference is that the data comes from the Data Model instead of Excel sheet.
Grouping of data is a little different with Pivot Tables based upon the data model. But that problem can be easily managed by teaching the different method to users.
In short, a combination of Power Bi and Excel skills upgrades the analysis, eliminates enormous wastage of time and mitigates the operational risk of repetitive manual data cleanup.
Start using it.
What next?
Finally here is the process I recommend. Regardless of what your role is – user, boss, IT – these fundamentals will be relevant. In fact if you find this concept useful, start sharing it with whomever you can influence. Let it spread and benefit maximum number of people.
Related articles
How to use Power BI free of cost
How to conduct a Power BI Pilot
Firstly, download Power BI desktop for free from here. Also rest assured that it is free for life. Unlike other apps, there are no feature restrictions as well. With this in mind, start using it and be sure to highlight and quantify the benefits. Then decide whether it deserves the very reasonable payment of USD 9 per month (as of Feb 2021).
