Microsoft Power BI – From Data Modelling to Stunning Reports

What can this visualization tool do for you?

Sep 23, 2022

10 min read

Image by Lukas Blazek on Unsplash, with enhancements by author.

What’s Power BI? Can it replace good ol’ Microsoft Excel?

Or do they work better together? How easy is it to create amazing visualisations? Is Power BI good for ETL and data modelling too?

We’ll answer these in this demo article. Data provided!

Prefer watching it done? Follow my YouTube channel.

Excel versus Power BI

Excel suits small ad-hoc analyses. It’s great for direct data entry, organising your data and making some simple visualisations.

Power BI is an industry-standard business intelligence and visualisation tool that equips businesses with easy-to-create visual dashboards and reports. These actionable insights allow bosses to make informed decisions quickly.

Many users leverage both Excel and Power BI synergistically.

Export that sales data into an Excel file, perform some basic checks and analysis, then push the .xslx into Power BI to create some beautiful interactive reports.

From an Excel file to a dashing Power BI dashboard! Image by author

Microsoft’s "Power Platform"

Power BI belongs in Microsoft’s suite of Power Platform apps, which includes PowerApps (build apps without code) and Power Automate (automate business processes).

Together, you can build some powerful workflows with little technical skills. The possibilities are endless.

Popular apps within Microsoft's "Power Platform" ecosystem. Image by author — Popular apps within Microsoft’s "Power Platform" ecosystem. Image by author

For instance, do you own some data at your company that colleagues regularly need access to? Are you dealing with it via. endless emails and manual processes?

Build a PowerApp application that serves as a front-end for colleagues. No coding skills necessary.

Then create a Power Automate flow that automatically pings you an Approvals request on Microsoft Teams whenever someone needs files.

All the electronic paperwork is pushed onto a SharePoint list, which can feed into Power BI for on-demand analytics and tracking.

Congratulations, you’ve just reduced hours and hours of menial tasks into an automated workflow that takes minutes, supported by interactive Power BI dashboards for reporting any time you want.

Microsoft’s Power Platform and the wider ecosystem of Microsoft 365 apps democratises the ability to streamline your work life – no coding skills required.

Work smart, not hard.

Leverage technology to save you time every day. Make it a habit.

Scale those savings across the year, and that’s how you become a productivity monster who gets off on time at 5pm.

Enjoying this story? Get an email when I post similar articles.

Data Analyst’s Journey in Power BI

What does a typical workflow in Power BI look like?

You have a sequence of steps that should look familiar to business analysts and data scientists.

You’ll…

feed the data into Power BI (ingestion)
clean and prepare it for analysis (transformation & modelling)
uncover insights (visualisation) and share with colleagues.

Note that there are 3 versions of Power BI:

Power BI Desktop (local app on your PC)
Power BI Service (browser-based)
Power BI Mobile.

The transformation and modelling steps are only available on the Desktop version. I’ll use Power BI Desktop so we can demo the entire workflow end-to-end. Great!

Let’s Get Started!

Wanna follow along?

I’m going to use the popular AdventureWorks dataset, which you can download here: AdventureWorks Sales.xlsx.

Suppose your boss wants to know the answers to questions like:

Which day of the year had the most sales?
Which countries is AdventureWorks seeing the most success?
Which product categories should the company continue investment in?

Not easy to answer just wrangling around in Excel – this is where Power BI’s magic really shines. Let’s start.

1. Ingestion

Open up Power BI and import your Excel spreadsheet.

Go through and select the columns/fields you need and click Load…

Wait! Maybe we want to make further changes to the data? Let’s click on Transform Data instead.

Quick note: Power BI can connect to a lot of data sources.

SQL Server? No problem.

What about traditional data warehouses? Teradata or Oracle? You’re good.

Working with big data and/or cloud? You can pull straight from Microsoft’s Dataverse, Azure Cloud or Synapse Analytics.

Clever data engineers might have even hooked up Power BI directly to your organisation’s Hadoop big data lake. Wonderful!

Confused with what your organisation’s data landscape looks like? You’re not alone.

2. Transformation (Power BI Desktop only)

This is where you use Power BI’s Power Query Editor to massage your data. (You can also do a lot of this in Excel too.)

An example of a simple transformation include modifying data types for your columns/fields. Here, would Post Code work better as Whole Number or Text? Decide by clicking a couple of buttons. Easy!

Make changes according to your business needs.

Once you’re satisfied, it’s time to model the data.

3. Modelling (Power BI Desktop only)

What are the relationships between your data?

Make linkages between different tables so you can join them later.

You can also create more fields here. This includes crafting custom measures that provide targeted insights. More on that soon.

Here’s our data before any modelling.

The data model employs a star schema where a centralised fact table (storing measures about sales) is directly related to each dimension table (storing information about AdventureWorks‘ customers, products, resellers and so on).

Notice immediately that the Date dimension table sits alone, not related to the rest of my data! Whoops.

A typical star schema. Image credit: Microsoft DP-500 public training

Using Power BI’s GUI interface, I was able to make a bunch of modifications to our data model:

Relate the Date table to the Sales table. Phew!
Create a set of hierarchy columns for each dimension table. For example, products can be organised into categories, subcategories and models. Customers can be organised by their country, state, city and postal codes. I can then use these new columns later to dig deeper in my visualisations!
Hide all ‘key‘ columns used for table joining but not useful for analysis.
Rename a couple of tables because… I’m feeling fussy.

Wanna do something that requires more customisation than clicking buttons?

You can write custom measures in DAX (Data Analysis eXpressions), which will look familiar to the Excel formula maestros.

For example, I created a new measure Sales Amount by Due Date with the following DAX code.

Sales Amount by Due Date = CALCULATE(SUM(Sales[Sales Amount]), USERELATIONSHIP(Sales[DueDateKey],'Date'[DateKey])

Our final data model looks like this:

Now I’m ready for the part everyone knows Power BI for – the pretty interactive dashboards!

4. Visualisation

This is where Power BI (and competitors like Tableau) really shine.

Drag and drop things between the Visualization and Fields panels on the right to make interactive graphs in seconds.

Pretty reports and dashboards. Image by author

My dashboard above contains 3 visuals:

Area plot
Map
Matrix.

Drag and drop panes on the right. Image by author

For instance, take the area plot, which provides information of AdventureWorks’ sales over time.

I first dragged the measure Sales Amount into the Y-axis for my visual.

I then dragged the Month field into the X-axis. (Remember we created this hierarchy-based field earlier during modelling! Had we not made this, we’d only have granularity down to the fiscal year. Whoops!)

Friendly reminder: you can always go back and modify your data model to create something you need for your visualisations. Data analysis isn’t linear!

Creating my first visual. Image by author

I also added that custom measure (from DAX) Sales Amount by Due Date on the same chart and coded it red (see below).

Don’t forget that all graphs are interactive!

This is the superpower of modern BI visualisation tools.

For example, I can dig into the data for just bike sales from our warehouses. All other visuals and their corresponding data will immediately update to reflect my selection. Very useful!

Diving into sales data by specific segments. Image by author

Or I can jump to a particular date and find out what’s going on across the business. Again, all visuals immediately update.

Diving into sales data by specific dates. Image by author

This is powerful stuff if you’re presenting a report to management who’s grilling you on the spot for more details, or telling a data story to an audience where a listener suddenly wants to dig into some more granular insights.

This capability of custom intricate data insights at your fingertips would be akin to magic just twenty years ago.

5. Publishing

Got some crazy insights and want to share them with colleagues?

You can do this in a few ways.

For example, you can save your dashboard as a Power BI .pbix file and email it, or you publish it onto Power BI Service and make it available across your Microsoft 365 cloud network.

Need to showcase your findings in a presentation?

Make sure to leverage data storytelling techniques – an integral career skill in order to be able to deliver impact and sell the value of your work.

Insights don’t sell themselves.

Final Words

Enterprise BI tools like Power BI and Tableau democratise the ability for anyone to create stunning reports and dashboards without requiring coding experience.

If you’re working in a company leveraging the Microsoft ecosystem, chances are you’ll have access to Power BI, along with its wider suite of Power Platform apps that allow you to create and automate workflows, and make powerful visual reports for yourself, your colleagues and of course, your managers.