Power BI the Problem Solver
Power BI is a problem solver. A really big one!
There’s so many ways that Power BI can make your (or your company’s) life easier from saving time on data transformation, to extending your analytical capabilities to protecting your data. That means that those who know how to use Power BI:
- have more time to do more interesting work,
- get some sort of data magician label at work (seriously, on seeing Power BI’s capabilities people have described it to me as magic),
- finish work on time
- could get a salary increase
You may at this point be wondering how Power BI can genuinely help you. So, to put my money where my keyboard is, below are some examples of the various ways that Power BI can solve problems that you may be experiencing!
1. The ‘I’m bored of transforming data’ problem
Very frequently when creating repetitive Excel reports from other datasets such as analysis, dashboards and so on, we have to do a whole lot of data transformation on our original datasets before you even get to the fun part of the analysis or dashboard. Transformations might include deleting columns or rows, adding in new columns with functions, splitting text at delimiters and loads of other possibilities.
Every time we get a new set of data, we have to perform these transformations. They might not take that long, say 15 minutes or a half hour each day or week or so, but 15 minutes every day for a year is 65 hours – or nearly 2 working weeks! Depending on the complexity of the transformation, it’s also quite easy to make a mistake resulting in inaccurate date for your reports.
Power BI solves this problem through Power Query. Power Query connects to our Excel files, and you teach Power Query to perform these transformations. Power Query then remembers the transformations so that every time you present a new version of your report’s supporting Excel files, it knows how to transform them for you automatically.
2. The ‘I’ve got to join datasets together’ problem’
Often, to create analysis in Excel, you need a base dataset that has the information you’re analysing e.g. sales, and then a lookup file which has additional information with which you’d like to imbue your base dataset. You then use functions like VLOOKUPs and XLOOKUPs to bring information from the lookup dataset to the base dataset What happens though if your datasets are at different granularities and you can’t use an XLOOKUP, or your lookup dataset changes and you have to redo all your lookups for YTD data, or you have duplicate matching values in your lookup table? Maybe you just don’t want to have to perform 10 XLOOKUPs every week just to create a table that you can analyse?
Power BI solves these problem using the merge function in Power Query or by using relationships. The merge function allows you to join multiple datasets together to create wider tables. Relationships in the data model allow you to link datasets together virtually so that they act like one big dataset
3. The ‘Excel is really slooooooow to open’ problem
Hefty Excel files can take a long time to open. You might not even need to be near the row limit, if you have lots of columns with lots of formulas that can be enough to really slow Excel down. I used to have a file that would take hours to open at my most time pressured periods of the month and year, and I was always desperately hoping that the file wouldn’t crash and I’d have to start again!
Power BI solves this problem by being specifically designed to deal with tens of thousands, millions and even billions of rows. Slow files are a thing of the past!
4. The ‘Excel Row Limit’ problem’
If you’re trying to do your analysis in a spreadsheet, one day you’ll run out of room. Excel only has 1,048,576 rows and so any significant datasets, say daily sales transactions for a mid sized company, will eventually be too large for a single worksheet. You could start putting data on a different worksheet, but then you won’t be able to use PivotTables to do analysis, needing to rely on aggregation functions which are much slower and static rather than dynamic.
Power BI solves this problem by connecting to multiple datasources at once using any of about 1000 different data connectors. You can connect to CSV files, .xlsx files, multiple Excel files simultaneously, Outlook inboxes, systems like Salesforce or Oracle, websites, databases like Analysis Services and so on. This removes the row limit problem.
5. The ‘Business Continuity’ problem
Companies that rely on spreadsheets to run their business also rely on employees to run those spreadsheets. What happens when those employees go on holiday, are ill or leave the company? If you have advanced notice of an absence, then you might have handovers and process notes, but in nearly 15 years of working with Finance teams, I’m yet to find a company that does this consistently. This often means that when an employee is absent, then their spreadsheet don’t get updated or falls over altogether, impacting the business.
Power BI solves this problem by remembering all the steps taken to transform base datasets to beautiful Power BI report. If the Power BI report is connected to Excel files, then you only need to replace the Excel dataset or add in a new one and click Refresh in Power BI. It doesn’t get much simpler than that.
6. The ‘Time vs Security’ problem’
One report often goes to multiple recipients, but frequently we’d prefer that only part of that report goes to different recipients. For example, a Finance team may have a budget file that needs to go to Department Heads, but it may be preferable that the Department Heads can only see budget data relating to their own departments. The Finance team then has the choice to create an individual budget report for each Head of Department, which takes time, or to send the same report to everyone and forgo the security of individual reports.
Power BI solves this problem by using Row Level Security, a way of assigning data filtering profiles to Power BI Reports. When Power BI reports are then shared online, colleagues email addresses are then assigned to each profile so that the owner of that email address can can only see the data filtered for that profile. Requires Power BI Pro.
7. The ‘it’s easy to make a mistake when creating our analysis’ problem
Very similar to problem number 1, frequently when creating repetitive Excel reports from other datasets such as analysis, dashboards and so on, we have to do a whole lot of data transformation on our original datasets before you even get to the fun part of the analysis or dashboard. Transformations might include deleting columns or rows, adding in new columns with functions, splitting text at delimiters and loads of other possibilities.
Every time we get a new set of data, we have to perform these transformations and it’s easy to make a mistake, like deleting the wrong rows, looking up the wrong columns, copying and pasting over live data and so on.
Power BI solves this problem through Power Query connecting to untouched Excel files, and you teach Power Query to perform transformations. Power Query then performs the transformations so that every time you present a new version of your report’s supporting Excel files, it knows how to transform them for you automatically. Crucially, it performs what you ask it to, without a mistake.
8. The ‘Data Privacy’ problem’
Sensitive data, like those protected by GDPR, is held securely in systems. When staff want to analyse or use the data, guess what…? They extract it into Excel, mash it with other data, and then send it in Excel files round the business. If that sensitive data then needs updating or deleting, then it is really rather onerous for data security teams to find all the iterations of the data, in inboxes, outboxes, Sharepoint sites, OneDrives and so on.
Power BI solves this problem by connecting to lots of different systems, so that the information doesn’t need to be extracted into Excel. Power BI can then mash that data with information outside those systems.
9. The ‘it takes ages to update our analysis’ problem
Similar to problems 1 and 7, repetitive analysis and reports that rely on numerous large datasets that themselves need transforming and mashing together can take a long time to update for new data. This can lead to decision makers not getting the information they need, when they need it (not to mention wasting a whole load of time)!
Power BI solves this problem by connecting to all necessary datasets and then recording the steps to transform the data. It remembers all the calculations, all the graphs and charts, the structure of your reports, everything. When you have updated data, simply add to or change your datasets, then click refresh. Power BI will do everything else in a fraction of the time that you can do it.
Ready to get started?
A free 1 hour course to find out more about the different parts of Power BI, see a finished report up close in the flesh (on the screen) and understand how it’s put together. By the end of the course you’ll have enough to go off and start experimenting with Power BI yourself.
This 7 hour course that builds on Introduction to Power BI by teaching the basic elements of Power BI and then guiding you through creating a beginner report. You’ll then be guided through a second more complex report to get started on your Power BI Journey.
A complete 18 hour course building on Getting Started With Power BI that teaches the basics of Power BI and then guides you through the creation of 4 varied reports ranging from beginner to advanced, combining Power BI theory with report writing and error fixing practice
You must be logged in to post a comment.