One of the questions we get asked a lot is how to manipulate Power BI data sources into manageable reports. But, in order to manipulate your data, you need to understand first how to load data from a file, and how to go through the pre-filtering process. So, in today’s blog, we’re going to walk you through three exercises that you can do to learn more about loading data into Power BI and getting the most out of that data.
Not an IT Professional? Check out our blog on Power BI reports, and learn what Power BI is, how you can use Power BI to illustrate your data into meaningful reports, plus, find out what else you can do with Power BI!
Power BI Data Sources – Exercise 1: Loading Data From a File 📁
Power BI can pull data from multiple sources. In this exercise, we’ll be using a .csv file. This is appropriate, given that many legacy systems will give you data in .csv format, so it’s very common to have data provided as a .csv file.
The purpose of this exercise is to show you how you can pre-process or pre-filter your data before you load it into Power BI. The truth of the matter is, when you pull the data from wherever you’re getting it from, you don’t always have the luxury of deciding what format it comes in. So, Power BI’s data capabilities allow you to extrapolate data from multiple sources – Power BI will take the data from whatever source it comes from and push it out into another format so that you can do what you’re trying to accomplish.
In this exercise, we’re going to show you the steps to opening and pre-filtering your data, so that you’re only importing the data you need into Power BI, and cut down on the amount of data you need to work with.
Step 1: Open the .csv file you want to import into Power BI.
Step 2: Remove any extra columns.
Step 3: Unpivot columns.
Step 4: Rename columns to your specifications.
Step 5: Change column type, if desired.
Step 6: Filter the data to your specifications.
Step 7: Rename table to your specifications.
Power BI Data Sources – Exercise 2: Loading Data From Multiple Data Sources 🗃️
As mentioned, there are often multiple ways for you to make use of the versatility that Power BI offers when it comes to data sources. A .csv file contains only one data set – for example, a list of all the sales that your business made in a particular period. But sometimes you may need to upload multiple data sets from one file into Power BI – like, for instance, an Excel file that has multiple tables (lookup tables, client tables, etc.). So, can this be done? The answer is, absolutely!
In this exercise, we’re going to show you how this is possible, by showing you how to load an Excel file into Power BI. This is a different approach to doing the same thing as we outline in the last exercise, just with a different data source than a .csv file.
Power BI looks at each one of these tabs as a separate data set, and scans each one of the tabs to see whether or not there is data in them. If so, Power BI will read them as different data sets.
- We're only loading the data we need.
- We're pre-filtering. We may only choose to work with 4 of the 10 tabs that are in that set, etc.
Step 1: Open the file that you want to upload into Power BI.
Step 2: Select the data set that you want to include in the report.
For example, let’s say that you had an Excel file with 9 different tabs (data sets), but you only wanted to include seven of them – you can take this step and filter out what content you don’t want included in your Power BI report, then you’ll see the content appearing in the left-hand side of the Power BI screen, under “queries”. Each of these queries represents one of the tabs at the bottom of the Excel file you’re adding.
Step 3: Check that your data sets (queries) have copied over properly.
Before you move on to the next step, make sure that the data you need has been copied over. In this screenshot below, you’ll see that there are 7 queries on the left-hand side – this is where you look to make sure your data sets have been copied over.
Step 4: Make any revisions to the various queries on the left-hand side.
I.e. edit or remove columns in the “Period” query, or adding a “Month” column, like in the example below.
Step 5: Review the various fields that you’re adding to the Power BI report.
Power BI Data Sources – Exercise 3: Establishing Relationships Between Data Sets 📊
So, now that we’ve shown you how to upload data from a .csv file and an Excel file, it’s time to show you how you can combine these two and, more important, understand the relationship between these various data sets. For instance, let’s say you have a sales file that shows all the countries you sold to. When you look at the data on the .csv file, there are multiple lines referring to the United States – this is an example of a one-to-many relationship, where one entry in the country table could have multiple reference entries in your sales data.
In this exercise, we’re going to help you think about your data in terms of these relationships – how are they structured, how is it organized, etc.? Once you’ve established these relationships, you’ll be able to work with your data and the process of using Power BI gets much easier. So, let’s get started!
Step 1: From the main screen, click on the “Relationship” tab on the left-hand side to open up the relationship view.
Step 2: From here, you can select the various model objects to set their properties and determine the relationship between data sets.
Step 3: Rearrange the tables in the relationship view to have fact tables in the middle and dimension tables outside – this helps to visualize those relationships and identify any gaps between your data.
Step 4: Click on “Manage Relationships” to add, edit, or remove relationships between your various tables.
Step 5: Create the relationships you want between your tables.
Step 6: Verify that your relationships have been created to your satisfaction. If necessary, manually join various tables together.
Get rid of manual reporting processes & gain confidence in your data with Power BI:
This demo-based video illustrates the capabilities of Power BI, compared to manual reporting processes via Excel.
ProServeIT: Helping You Build Amazing Reports
This blog is the first one in a series of blogs that is going to eventually get you to an amazing report that looks something like this:
Related:
MS Power BI Embedded A-Series - Creating a Custom .NET MVC App
MS Power BI Dataflows - Public Preview
Stay tuned for more exercises, and let ProServeIT help you build amazing reports that will let you make the most of your data.
Can’t wait? A Business Intelligence Workshop is a starting point for your continuous BI journey and takes the guesswork out of Business Intelligence.
💡 Learn best practices & industry standards around Business Intelligence and how you can use BI to your benefit in your organization.
📊 Review data sources with data experts to solidify which KPIs and metrics you want to report on & explore Business Intelligence reporting possibilities and capabilities.
📋 Explore Business Intelligence reporting possibilities and capabilities for your organization - we call it a "Magic Show".
