Unleashing Business Insights with Power BI: A Comprehensive Guide
(Part II)
Alright, it’s time for Part II in our multi-part series, and like any solid sequel (not the query) you know it’s going to be way better than the first 😅
As a quick recap, remember that in Part I we outlined why organizations need a business intelligence solution — it allows them to create and share data-driven actionable insights. And, we identified a powerhouse of a tool to enable these actionable insights across your organization — Microsoft Power BI.
Up next we’ll walk through how to download and install Power BI Desktop and the basics of connecting to data (the extract, transform, and load (ETL) process), and how to make it useful!
Up & Running with Power BI
Because Power BI Desktop doesn’t require any sort of fee, subscription, or account creation, downloading and installing is a breeze. Here’s the process:
Download Power BI Desktop
- Visit the Microsoft Store or
- Download from the official Power BI site
In both cases follow the on-screen instructions for download and pick the version that’s compatible with your operating system
Install Power BI Desktop
- Run the installer
- Follow the onscreen prompts
Launch Power BI!
Sign In or create a Power BI account (I know, I know)
- This step can be skipped
- Creating an account isn’t required for Desktop but it is required if you want to publish and share your reports to Power BI Service — more on this later!
Hey that’s it!
You’ve successfully downloaded and installed Power BI Desktop, feel free to take a well earned break or, umm, poke around the interface a bit. There’s a ton to explore and loads of features but don’t get overwhelmed, there are some great courses out there to help you figure it all out (nudge, nudge, wink, wink).
Anywho, the thing with Power BI is that it’s a lot more fun, and interactive, when you’ve got some real, live data added into the tool. So let’s talk about the Get Data process, what types of data you can connect to, and how you can make it usable.
Get Data & the ETL Process
Let’s start from the beginning. What exactly is the “Get Data” process?
Well, the “Get Data’’ process refers to the initial step you take to import or connect to data from various sources so that you can create reports and visualizations. Power BI provides a wide range of options for importing and connecting to data, and the “Get Data” process (it’s an actual button in Power BI) is where you specify the source, select the tables or data you want to use, and perform any necessary transformations.
Here’s a high-level overview of the “Get Data” process in Power BI:
- Launch Power BI Desktop (I know this is obvious, but just want to start from zero 🙂)
- Click “Get Data” from the Home ribbon menu options to start the data connection process
- Choose a data source — Power BI allows you to connect to a wide variety of data sources which we’ll cover in a moment
- Connect to the data source. After you select the data source, you will need to provide connection details such as the server name, credentials, URL, file path, etc. depending on the source. Power BI uses this information to establish a connection to the data
- Navigate and select the data you need from your source. Once connected, you can typically navigate the data source’s structure to select the tables, views, or data you want to use in your report. What’s great is Power BI provides a preview of the data to help you make an informed decision — thanks Power BI 👏
- Transform the data (ETL) before importing it into Power BI’s data model. At this step Power Query allows you to apply various data transformation steps to help clean, shape, restructure, add features, filter, sort, etc. and get the data ready for loading and analysis.
*Don’t sleep on this step, the ETL process is one of Power BI’s superpowers and will in fact change your life. I’ll have to write a follow along article dedicated to the ETL process to really do it justice
- Load the data you’ve selected and transformed into Power BI’s data model. The data model is where your data is stored and can be used to create visuals and reports
- Modeling data & report enhancements. This step is all about creating relationships between tables and adding measures and calculated columns to enhance your reports
- Visualizing and sharing data is really the last step in the process and is focused on starting to build visualizations, reports, and dashboards using the data loaded into the model. Once your reports and dashboards are built, you can then share them with other people within your organization!
Steps 8 & 9 technically aren’t part of the “Get Data” process but I added them in to help round out the workflow. Let’s get back on track.
So you may be thinking, “Okay, I get the high-level process, but what exactly can I connect to?”
The short answer is that you can connect to almost any data source you want. I say almost because there’s always that one connector that’s either not yet supported, no longer supported, or has an “it’s complicated” relationship status — I’m looking at you QuickBooks!
Instead of listing out the hundreds of possible connections, let’s take a look at the categories of connectors and some examples:
Microsoft Services
- Power BI has native connectors for Microsoft services like Fabric, Azure, SQL Server, SharePoint, Dynamics 365, Power BI Dataflows, etc.
Cloud (online) Data Sources
- You can connect to various cloud-based data sources such as Azure Data Lake Storage, Azure SQL Data Warehouses, Azure Cosmos DB, AWS Redshift, Google Big Query, and many others
* One thing to note here is that many of the Azure data sources are grouped in an Azure specific bucket in the Get Data menu
Relational Databases & Data Warehouses
- Power BI can also connect to various relational databases like MySQL, PostgreSQL, Oracle, SAP, Access, Snowflake, Teradata, etc.
Web Data Sources
- Power BI lets you extract data from web services and APIs using Power Query
Excel
- I’ve added Excel as its own line item because you can import data from Excel along with fully built relational data models, which is seriously cool!
Flat Files
- You can import data from CSV, text files, and other structured file formats like JSON, Parquet, XML, and Folders!
*Pro Tip: Check out the folder option and the ability to refresh data when you add new data to the file
On-Premises Data
- Power BI has features like the On-Premises data gateway that allows you to connect to on-premises data sources securely
BIG DATA sources
- Power BI can also connect to big data platforms like Hadoop and Spark
Web Services & APIs
- You can use Power BI’s web connector to scrape data from websites or fetch data dynamically
Custom Data Connectors
- If you need to, and have the technical chops, you can install the Power Query SDK and build your own custom data connector to bring in specialized or proprietary data
So when I say that you can connect to virtually any data source, it’s very true. There are hundreds of pre-built connectors with more connectors released each month. So, if the connector you need isn’t available yet or isn’t supported, it may be on the road map. If not, maybe you can develop it yourself!
Now that you’ve got the lowdown on how to connect to data and the types of connectors available to you, how do you make it usable?
The real power of Power BI is its ability to make data from multiple disparate sources useful. Remember, the key here is that we want to turn data into actionable insights.
No one is going to remember a beautiful dashboard if it isn’t useful. So when you start to design and build your dashboard keep the following analytics workflow in mind and make sure you’re building based on what’s needed and who it’s needed for. Keep the following in mind:
- Understand the Business Case
- Build a measurement plan (optional but very helpful)
- Collect & prepare the data
- Understand the data (use QA & profiling tools for this)
- Analyze & visualize the data
- Develop data-driven insights
- Measure, test, and optimize
Follow this process and you’ll be unstoppable.
Hopefully you’re starting to realize the awesomeness that is Power BI and how valuable it is. Up next, in Part III, we’ll dig deeper into Data Modeling!
Learn on 🤘
- Aaron
Interested in learning more Power BI from Aaron and the other Maven experts?
Check out Part I & Part III of his Comprehensive Power BI Guide here on Medium, and check out our course catalog!
Our Power BI & Python Immersives are open for enrollment!
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!
