An Excel User Learns Power BI
Are you an Excel user who’s curious about Power BI?
Have you seen the cool dashboards people create in Power BI and wondered how hard it would be to do that too?
Have you wondered if your existing skills in Excel will help you make the transition?
This was me about 6-years ago when I first heard about Power BI.
It was during a coffee meet-up with a mentor. Having a reputation in my professional circle as being the go-to person for Excel spreadsheets, this mentor had asked if I was working with Power BI yet. They had seen some amazing dashboards that were created using Power BI and said they immediately thought of me.
Of course, I was intrigued and [spoiler alert] within 3‑years I was deploying my first ever Power BI dashboard.
So, remembering back to that moment, I’ve compiled a few insights on what I would have liked to know when I first embarked on my journey to learn Power BI coming from the perspective of a long-time Excel user.
Easier — Creating Interactive Visualizations
Wow! I was immediately impressed by how quick and easy it was to create and manipulate visuals in Power BI compared to Excel.
In the years leading up to learning Power BI, I had been creating incredibly complex Excel spreadsheets that leveraged things like pivot tables and slicers to create an interactive experience for the user. However, the complexity of these spreadsheets made them very susceptible to errors and issues… especially when I tried to build for every potential combination the user might expect when interacting with the data or visuals.
I found that creating the most common visuals like bar and line charts was surprisingly intuitive coming from Excel. In fact, creating a basic chart can be achieved simply by clicking and dragging! And the real game changer for me was the ability to add a slicer that automatically interacted with all the visuals on a page.
It was so easy to recreate the interactive experience I had worked so hard to create in Excel but with the added bonus of being more robust and easier to maintain.
Now much further along in my Power BI learning journey, I’ve been able to leverage even more sophisticated approaches to the interactivity by selecting exactly which visuals interact with each slicer, but the basic functionality was enough to get me immediately sold on the power of Power BI [see what I did there… 😊].
Same — Power Query for Importing, Cleaning and Readying Data
It was reassuring to see Power Query, a familiar “old friend” from Excel, when I first began loading data into Power BI.
Power Query is a powerful tool that can be used to import data from various sources (csv files, other Excel files, web locations etc). Plus, it allows you to automate the repetitive steps needed to clean or transform data as you ready it for analysis.
Automating time consuming and tedious steps like trimming data, extracting / combining text from multiple fields or creating calculated columns had become a game changer working in Excel so it was both familiar and reassuring to see that same capability when loading data into Power BI. This allowed me to quickly turn my focus into leveraging the new features that Power BI would give me in my data analysis such as data modeling.
Better — Data Modelling
Even though data modeling wasn’t completely foreign to me when I first started learning Power BI, my experience in Excel had come gradually over time without ever having a background or formal training in concepts like coding or database design. In the absence of that knowledge, I leveraged functions like VLOOKUP and HLOOKUP in Excel to look up data from one table and add it to another.
However, like for the interactive visualizations I mentioned earlier, over time I learned that while this approach [mostly] worked, it could become cumbersome and really bloat file size.
When I first started to learn about the ability to model data in Power BI, I knew it was a game changer.
The data modelling feature allows you to take data brought in from various sources using Power Query and create relationships to connect them together in a logical structure. Once they’ve been connected, you now have the ability to LOOKUP information from one table based on the value in another without having to add all those extra formulas and columns I had be relying on in Excel.
Learning how to create functional and then robust data models took me some time and lots of practice. But it’s totally worth it.
A few things you can focus on in your own journey of learning to model the data in Power BI:
1. Take the time to understand/create the appropriate Primary and Foreign keys in your data tables.
Connecting back to the experience using LOOKUP functions in Excel, the foreign key is often the value you’d be using as the lookup_value while the primary key is often the location you point to in your lookup_array or the first column in your table_array.
= XLOOKUP(lookup_value,lookup_array,return_array)
= XLOOKUP(Foreign Key, Primary Key, return value)
= VLOOKUP(lookup_value,table_array, col_index_num)
= VLOOKUP(Foreign Key, Primary Key: desired result column, column count to the desired result column)The column that contains a primary key should only have one row for each unique identifier whereas a foreign key can be repeated within a column many times.
2. Leverage the Model View in Power BI to help you visualize the structure of the data.
The Model View allows you to see all the tables you’ve loaded into your Power BI file as well as the way they are connected together through relationships.
I like to organize the tables in this view so that the relationships go from 1:MANY in a top / down or left / right flow [as much as possible]. I find this helpful because it gives me a visual cue as to the hierarchy and as you learn even more about data modelling, helps you see if it best aligns with a STAR or SNOWFLAKE schema.
Steepest Learning Curve — DAX Expressions
Ah… DAX. DAX was [and still is] the steepest part of my learning curve going from Excel to Power BI.
DAX is the language you use in Power BI to create the calculations/measures that are the foundation of your analysis. And like learning any new formula in any tool, there’s a learning curve in just familiarizing yourself with the expression(s) and the corresponding syntax.
Just think back to the first time you learned a new formula in Excel… like dynamic arrays. How many times did you go back to a reference source until you memorized the proper way to structure that formula? This is something you can expect to experience again when you first start learning to write DAX expressions.
But there’s a nuance with writing DAX expressions that I found particularly challenging.
When you write a formula in Excel, it exists in a particular cell (or maybe an array of cells), so once you hit enter you get immediate feedback on the output of that formula.
However, in Power BI you use DAX expressions to write a measure, but that measure is not calculated and displayed until you add it to some type of visual in the report view. This can make it very challenging when it comes to verifying or debugging your formulas because you can’t see the output.
To help me with this issue, I often start off my Power BI report view with a page that contains only matrix visuals.
A matrix visual is similar to a table and this allows me to see the output of the measure I’ve created and check if it’s returning the values I expected. This becomes particularly helpful when it comes to measures that leverage fields across multiple tables as the relationship between tables can have a significant impact on output of the calculation. This goes back to how you’ve structured your data model.
For me, it was really in this iterative cycle between developing your data model and creating the DAX measures required for your analysis that takes the most time but also presents the most opportunity when learning Power BI.
So plan to give yourself lots of time and seek out support as you learn to leverage these aspects of the Power BI tool. There’s an amazing Power BI data community that I’ve found to be supportive and sharing, so proceed with optimism!
Wrapping Up
For the most part, the transition from Excel to learning Power BI has seemed like a natural extension.
Learning this new and exciting tool has allowed me to first build off my existing skills from Excel but also grow in terms of data modeling and leveraging DAX expressions to create much more complex yet robust analysis.
And the best part has been the output — being able to create dynamic and interactive reports/dashboards that support the teams I’ve worked with to understand and explore their data to drive consistency and improvements.
Interested in learning more about Power BI from Stacy and other pros?
Master Power BI in just 10 weeks:
Admissions for our immersive programs are now open!
Key Deadlines:
- Super Early Bird — Save 25%: Friday, March 29th
- Early Bird — Save 10%: Friday, April 12th
- Admissions Close: Friday, April 19th
Here’s what you can expect from each immersive…
✔️ Guided portfolio projects
✔️ Access to your own private learning community
✔️ Weekly, 1-hour live sessions with recordings
✔️ Live support from expert instructors
✔️ Unlimited Maven access for an entire year
…and all of this is built into a program that’s designed to fit into your busy life, at a more affordable price.
Space is limited; secure your seat today!
