Power BI for Beginners | 5 Tips to Get Started

In this blog post, intended for new Power BI developers, I identify some easy-to-use features that I wish I had started using at the beginning of my Power BI journey. My goal is to familiarize you with a few key concepts that will help you get more value out of Power BI. Learning and harnessing these features will help you take your report development skills to the next level. 

#1: Become Good Friends with the Power Query Editor 

Before loading data into Power BI, you will want to review the structure of the data and make changes to it as needed. Where does all of that transformation magic happen? The Power Query Editor! Power Query is Microsoft’s data transformation and preparation engine and the Power Query Editor is the graphical user interface for modifying and shaping your data before loading it into Power BI. 

You can access the Power Query Editor in one of two ways. The first way is through the ‘Get Data’ process. Once you select your data source, click on ‘Transform Data’ to get to the Power Query Editor. The second way to get there is by clicking on the ‘Transform Data’  button on the home ribbon. 

Let’s take a tour of the Query Editor: 

1. Ribbon - This is your control center. Use the tabs at the top to access  the various transformation tools. 
2. Queries - A list of all of your queries. 
3. Data View - A working preview of the data in the query.
4. Query settings - The applied steps list out each transformation that is applied to the query. Click the gear icon to edit the step or click on the ‘X’ to remove it. 

Here are some common transformations you can make from the Query Editor: 

• Change data types
• Reorder columns
• Rename columns
• Delete columns
• Promote first row to header
• Use built-in logic to split columns
• Add conditional columns
• Filter and replace values
• Merge or append tables 
• Pivot and unpivot columns

Taking some time to learn how to transform your data in the Query Editor will enable you to properly shape your data so that it’s ready to be visualized. 

#2: Identify Data Quality Issues with Ease through Column Profiling 

Within the Power Query Editor, you will find three valuable tools for exploring data quality and identifying issues. Those three tools are column quality, column distribution, and column profile. From the View tab in the Query Editor, check the box to enable each one. 

Column Quality: Shows the percentage of rows within a column that are valid, empty, or errors. From there you can hover over the summary box (or right-click on the column quality section) for additional prompts to correct any issues.

Column Distribution: Shows the number of distinct and unique values within a column. Bonus tip: if a column has the same number of distinct and unique values, that field can serve as the primary key for that table. For example, in the table below, we know that Row ID is the primary key for this table because it is the unique identifier for each row of data in the table. 

Column Profile: Colum profile provides additional summary statistics and a bar chart showing the distribution of values in the column. For example, you would use this to check which values for a dimension are present or to evaluate the minimum and maximum values for numeric data. 

Bonus Tip: Column profiling is based on the first 1000 rows by default. If your dataset is larger than that, make sure to change it to profile based on the entire dataset (this is located at the bottom of the Power Query Editor). 

#3: Don’t Be Intimidated by Data Modeling

What is a Data Model? A data model defines the relationship between tables. Because you are often bringing in data from multiple sources, you need to create a good data model to define the links between the data sources. In Power BI Desktop, you can access the data model via the Model view on the left-hand side navigation.

Here are some best practices for data modeling: 

• Use a fact table plus supporting lookup tables. A fact table contains all of the facts, such as sales amount, quantity sold, and transaction date. Those values are often aggregated in data visualizations. A lookup or dimension table stores the related attributes that allow you to slice and dice the data from different angles in your report, such as sales by region, or quantity sold by product. 
• Use a many-to-one (*:1 relationship) between the fact table to the dimension table. The type of relationship is referred to as the cardinality. According to Power BI, “A many-to-one relationship is the most common, default type of relationship. It means the column in a given table can have more than one instance of a value, and the other related table, often known as the lookup table, has only one instance of a value.”

How to set up a good data model. Let’s take a look at an example of a data model from a global kitchen supply retailer. 

Here, we have one fact table (Sales), that “looks up” to several dimension tables (Products, Customer Type, Regions, and Calendar). The Sales table is connected to each lookup table with a linked field using a many-to-one relationship. For example, there are many instances of Product Key in the Sales tables that relate to one instance of Product Key in the Products table. 

Connecting tables is as easy as dragging a field from one table onto the related field in the other. After forming relationships, you can hover over the connecting line and double-click on it to view and change the properties. Note that Power BI will try to detect the relationships, but you can turn this feature off to have more control over creating your own data model. 

Navigation: File > Options and settings > Options > CURRENT FILE > Data Load

Creating a good data model helps with making your data ready for visualization and enhances the efficiency of your report.  

#4: Get Off to the Races Using Quick Measures 

If you don't know much DAX (DAX stands for Data Analysis Expression and is the formula language of Power BI), take advantage of Quick Measures! Quick Measures allow you to drag and drop fields to create measures such as: 

• YTD / QTD / MTD total
• YoY / QoQ / MoM change
• Rolling average 
• Running total 
• And more! 

You can access Quick Measures either from the top ribbon or by clicking on the three ellipses next to a table in the Fields pane. You then use the Quick Measures interface to drag and drop fields into the right spots. When you create a Quick Measure, Power BI writes the DAX syntax behind the scenes. This is a great way to start using measures as you’re ramping up with learning DAX. 

#5: Take Your Charts from Boring to Awesome with Formatting 

One way to make your visualizations and reports stand out is to ditch the default formatting and jazz it up with some design updates! There is nothing wrong with using the default formatting, but Power BI has some built-in formatting options to help you take your reports to the next level. Here are my top formatting tips:

1. Use the theme editor in the View tab to update the entire report theme.  

2. Change the report page background color or add a background image. Click on the blank space in the report canvas and use the paintbrush to access the page formatting. 

3. For visual-level formatting, turn off gridlines on the X and Y axis formatting. For all visual-level formatting, click on a visual and access all of the formatting options in the Visualizations pane.

4. Use custom colors. Update chart colors under the Data colors and Background sections of the visualization formatting. Give your chart a different background than the report page to add some contrast. For determining what colors to use, refer to your company’s brand guide if they have one. I also like to use Adobe Color Wheel as a color scheme resource. I’m also a big fan of the Site Palette Chrome extension which you can use to extract colors from any website. 

5. Turn on the visualization Border and set the radius to 10 for rounded edges. 

6. Turn on the visualization Shadow to create a ‘drop-shadow’ effect. 

7. Use the Format Painter to quickly apply formatting from one visual to another. 

Here’s a look at a report after applying a few of these formatting techniques. 

BEFORE:

AFTER: 

In summary, we have covered topics including the Power Query Editor, data profiling, data modeling, Quick Measures, and formatting. These five areas are important areas you can focus on to improve your Power BI skills. Hopefully, you have learned something new about Power BI that will take your skills to the next level! 

Explore More