Power BI Desktop Tricks

To make the most of your tool usage, sometimes you need a little extra help. We've put together a few tricks you can use when creating reports with Microsoft Power BI Desktop and recent editions of Microsoft Excel or Excel 2013 Pro-Plus with the Power Pivot add-in enabled and Power Query installed and enabled.

In addition, we have prepared an extra gift for all those interested in getting the most out of this powerful Microsoft tool, which you will find at the end of this reading, enjoy it and leave us your comments.

Learn how to use the Query Editor in Power BI Desktop

The first Power BI Desktop tip we'll give you is that the Power BI Desktop Query Editor is very similar to the Excel 2013 Power Query add-in. While Power BI Support has several valuable articles, you can also check out Power Query information at support.office.com to get started.

Query Editor Data Types in Desktop

When loading data into Power BI using the Query Editor, we identify the data type. However, when using formulas, sometimes the data type settings previously established in the columns are not maintained. Verify that the data type in the columns is correct after performing any of the operations mentioned below: Initially loading data in the query tab, First row as header, Add column, Group by, Merge and before pressing load data for the first time.

An important point to note: the italics in the data box do not mean that the data type is correct, it simply indicates that the data recorded there is not considered text.

Reference Queries in the Query Editor

Another trick in Power BI Desktop is that in the Power BI Desktop Query Editor navigator, right-clicking on one of the queries will show the “ Reference ” option as enabled. This is useful for the following reason:

When using files as data sources for a query, the absolute path to the file is stored in the query. When you share or move the Power BI Desktop or Excel Workbook file, you'll save time updating paths because you only need to refresh the data once.

By default, all queries are loaded into the data model. Sometimes queries are intermediate steps and are not intended for end users, such as the case we just explained for reference to queries. You can control the loading behavior of the query by right-clicking on the query in the browser and toggling the “Enable loading” option on/off.

When the Enable Loading option is not checked, the query is still available on the query tab and can be used in conjunction with other queries. This is particularly convenient when combined with append, join, and reference transformation operations. However, since the query results are not loaded into the data model, the query will not reload the report field list or the data model.

Need for point identifier in scatter plots

Let’s use as an example a simple table of temperatures with the time at which the measurement was recorded. If we make a scatter chart directly, Power BI groups all the data into a single point. To display the individual data points, you need to add a field to the details in the fields area. A simple way to do this in Power BI Desktop is through the “Add index column” option in the queries tab, on the “Add column” ribbon.

Reference lines in Power BI Desktop

Another neat Trick in Power BI Desktop is that you can use a calculated column in Power BI Desktop to define a reference line. Identify the table and column where you want to place a reference line. Select “New Column” from the ribbon, and in the formula bar, type the following formula: Target Value = 100

This calculated column will return the value 100 regardless of where it is used. The new column will be displayed in the field list. Add the target value calculated column to a line chart to see how the series relate to that specific reference line.

Sort by another column in Desktop

When you use a category (string) value in Microsoft Power BI for chart axes or in slicers or filters, the default sort order is alphabetical. If you need to change this sort order, such as for information such as months or days of the week, you can tell Power BI Desktop to sort the data by a different column.

Map creation made easy with Bing suggestions

Power BI relies on Bing to perform the process known as geocoding, that is, providing the default coordinates for maps, thus making it easier to create maps. Bing uses several hints and algorithms to try to determine the correct location, although it is an approximate calculation. To increase the probability of doing the geocoding correctly, follow these recommendations:

When creating a map, you typically want to delineate regions, countries, states, and cities. In the case of Power BI Desktop , using columns of names after the geographic designation will help Bing guess what you want to show.

For example, if you have a field of US state names such as “California” and “Washington,” Bing might return the location of Washington, DC, instead of Washington State because of the word “Washington.” In these cases, naming the column “State” will improve geocoding. The same is true for columns named “Country” and “City.”

Some designations are confusing when handled in the context of multiple countries/regions. In certain regions or countries, “States” are considered as a “County” or a “Province” or even something else. Geocoding accuracy can be improved by implementing columns with multiple fields together to map data locations. An example of this would be, instead of passing just “Wiltshire”, pass “Wiltshire, England” to get a more accurate geocoding result.

Likewise, in Power BI or Power BI Desktop you can always provide specific longitude and latitude locations. When doing so, you must also pass a location field, otherwise the data is aggregated in a pre-defined manner and the latitude and longitude location is likely to not match what it should.

20% EXTRA DISCOUNT

Get started today and get fully certified in Power BI Complete with our course

100% online at your own pace
Practical exercises
Lifetime Access
Certified endorsed

Apply the coupon [DARE] and get an extra 20% discount for only 100 students. Click the button and join!

Categorizing geographic fields from Bing geocoding suggestions

Another way to ensure that your fields are properly geocoded when you specify the data category in your data fields is to: In Power BI Desktop, choose the table you want, go to Advanced, and then set the Data Category to Address, Continent, City, Country/Region, Postal Code, Province, or State. These data categories help Bing correctly encode your data.

Improved geocoding with more specific locations

Among the Tricks in Power BI Desktop we found that sometimes even setting map data categories is not enough. Using the Power BI Desktop Query Editor, create a more specific location such as a street address. Use the Add Columns feature to build a custom column. Then set the desired location as follows:Copy

= [Field1] & " " & [Field2]

Next, use the created field in map visualizations. This is really useful for generating street addresses based on the shipping address fields that are common in datasets. Note that concatenation can only be done with text fields. If required, transform the street number into text data before using it to generate an address.

Histograms in the query phase in Power BI Desktop

There are several different ways to create histograms in Power BI Desktop. We'll start with the simplest one and work our way up from there.

✅ Simpler histograms:

Determine which query has the field you want to generate a histogram from. Use the “Reference” option for the query to create a new query and name it “FieldName Histogram.” Use the “Group By” option on the “Transform” ribbon and select the “row count” aggregate. Make sure the data type is numeric for the resulting column. Then look at this data on the report page. The way to make this histogram is very simple and quick, but it is not the best option if you have a lot of data points and it does not allow you to highlight different visuals.

✅ Definition of cubes to create histograms:

Identify which query owns the field from which you want to generate a histogram. Use the “Reference” option to have the query generate a new query and call it “FieldName”. Define the buckets with a rule. Create a custom rule using the “Add Custom Columns” option in the options bar and then Add Column. This could be a simple bucket creation rule:

Copy

if([FieldName] \< 2) then "\<2 min" else if([FieldName] \< 5) then "\<5 min" else if([FieldName] \< 10) then "\<10 min" else if([FieldName] \< 30) then "\<30 min" else "longer")

Make sure that the data type is a number for the resulting aggregate column. You can now use the group using the methodology explained in the Simpler Histograms, Get the Histogram section. This alternative allows you to control more data points, however, it still does not help with charts.

✅ Definition of histograms that allow highlighting:

Highlighting refers to the fact that visual objects are related to each other so that when a person selects a data point from a visual object, several visual objects in the report sheet are filtered or highlighted data points that are related to the selected point. Since we will be handling several data points at query time, it is essential to generate a relationship between the tables and make sure that we know which detail element is linked to the histogram cube and vice versa.

Start the process with the “Reference” option of the query that has the field from which you want to create a histogram. Name the new query “Buckets.” In this case, we will name the initial query “Details.” Next, delete all columns except the column that will be used as the histogram bucket. Then use the “Remove Duplicates” option in the query that appears in the context menu that appears when you select the column.

This way, the other values will be the only values in the column. If you have decimal numbers, you can use the first bucketing tip to create a histogram to get a set of buckets that are easy to manage. Now, check the data displayed in the query preview. If you see null or blank values, you'll need to fix them before you build a relationship. See “Creating a relationship if my data has null or blank values.”

Applying this method can be complicated by the need for sorting. To get the buckets sorted correctly, see “Sorting: Getting Categories to Appear in the Order You Want.”

FREE DOWNLOAD

Download this completely free guide and learn all the Automatic Measures tools in your Power BI reports.

G-Talent Tips:

Before creating the visuals, it is important to consider the sort order. The next step in the process is to establish a relationship between the “Cubes” and “Details” queries in the cubes column. In Power BI Desktop , click Manage relationships on the ribbon. Create a relationship so that the cubes will be placed in the left table and the details in the right table. Then choose the field you are going to use for the histogram.

The last step is to create the histogram. Drag the Cube field from the “Cubes” table. Remove the default field from the resulting column chart. Now, from the “Details” table, drag the histogram field to the same visual. In the field set, change the default aggregate to Count.

The result will be a histogram. If you create another visual, such as a treemap of the Details table, select a data point in the treemap to see the histogram highlighted and display the histogram of the selected data point relative to the trend of the entire data set.

Histograms in Power BI Desktop

Almost finished with the Tricks in Power BI Desktop we reveal to you that in Power BI Desktop , you can use a calculated field to define a histogram. Identify the table and the column in which you want to create a histogram. In the calculation area, write the following formula: Frequency:=COUNTROWS()

Save your changes and return to the report. Add and Frequency to a table, then convert it to a bar chart. Make sure that the calculated field Frequency is on the x-axis and that it is on the " Y" axis.

Power BI Desktop Tricks for Creating Relationships

Typically when loading specific data sets from different sources, issues such as blank, null, or duplicate values do not allow relationships.

Let's see an example:

Let's assume we load two different data sets, one of active customer support requests and another of work components that have schemas like the one shown below:

CustomerIncidents: {IncidentID, CustomerName, IssueName, OpenedDate, Status} WorkItems: {WorkItemID, IncidentID, WorkItemName, OpenedDate, Status, CustomerName}

If you want to track all incidents and work items related to a given CustomerName value, you cannot simply create a relationship between these two data sets. Some WorkItems cannot be related to a CustomerName, so that field would be blank or NULL. There can be multiple records in WorkItems and CustomerIncidents for any given CustomerName.

Creating relationships in Power BI Desktop if my data has null or blank values

Data sets often contain columns with blank or null values. This can cause conflicts when trying to use relationships. Broadly speaking, there are two options for dealing with these problems. On the one hand, you can remove the rows that have blank or null values; to do this, you can use a filter feature on the query tab or, if you are combining queries, select the “Keep only matching rows” option. On the other hand, you can replace the null or blank data with values that will work in relationships, such as the strings “NULL” and (“Blank”), for example.

Neither option is correct or better than the other; filtering rows at the query stage removes rows and can affect calculations and summary statistics. In the second option, those rows of data are kept, but unrelated rows may later be displayed as related in the model, leading to erroneous calculations. If we decide to apply the latter solution, we need to make sure that we use filters in the view/chart where appropriate to be sure that they yield correct results. What is really important is to evaluate which rows will be removed or kept and understand the overall impact on the analysis.

Creating relationships in Power BI Desktop if your data has duplicate values

Often when loading detailed data sets from multiple sources, duplicate data values prevent relationships from being created. This problem can be solved by creating a dimension table with the unique values from both data sets.

Let's see an example:

Suppose we load datasets of active customer support requests and another dataset of work items that have schemas like the following:

CustomerInicdents: {IncidentID, CustomerName, IssueName, OpenedDate, Status} WorkItems: {WorkItemID, IncidentID, WorkItemName, OpenedDate, Status, CustomerName}

If you want to track all incidents and work items related to a given CustomerName value, you cannot simply create a relationship between these two data sets. Some WorkItems may not be related to a CustomerName, so that field would be blank or NULL. If there are blank or null values in the CustomerNames table, you may not be able to create a relationship yet. See Creating relationships if my data has null or blank values. There can be multiple WorkItems and CustomerIncidents for a single CustomerName.

To create a relationship in this case, we need to create a logical dataset of all the CustomerNames from the two datasets. In the Query tab, you can use the following sequence to create the logical dataset:

1. Duplicate both queries, naming the first one Temp and the second one CustomerNames.

2. In each query, remove all columns except the CustomerName column.

3. Use Remove Duplicates.

4. In CustomerNames , select the Append option from the ribbon, select the Temp query.

5. And under CustomerNames , select Remove duplicates.

You now have a dimension table that you can use to create a relationship between CustomerIncidents and WorkItems that includes all the values for each.

Patterns for getting started with the Query Editor

The query editor is very efficient at manipulating data to clean and shape it so that it is ready for modeling or visualization. There are certain patterns that you should consider.

⭕ Temporary columns can be deleted after a result is calculated

It is often necessary to create a capable calculation in Power BI Desktop that can transform data from multiple columns into a single new column. This can be complex. A simple way to solve the problem is to break the operation into steps. Start by duplicating the initial columns. Next, create the temporary columns. Then, create the column for the final result. At this point, you can delete the temporary columns so that the final dataset is not cluttered. This can be done because the query tab performs the steps in order.

⭕ Duplicate or reference queries followed by the join in the original query

Sometimes it is useful to calculate summary statistics for a set of data. An easy way to do this is to duplicate or reference the query in the query tab. Then, use the Group By option to calculate summary statistics. Summary statistics help you normalize the data in the original data so that it can be better compared. This is especially useful for comparing individual values to the entire set. To do this, go to the original query and select the join option. Then, join the data from the summary statistics query that matches the appropriate identifiers. You are now ready to normalize the data as required for your analysis.

⭕ Using DAX for the first time

Another of the tricks in Power BI Desktop is DAX, a Power BI Desktop calculation formula language. It is optimized for BI analysis. It is a bit different from the languages you are probably already familiar with if you have only used SQL as a query language. There are numerous resources available online, as well as DAX learning documentation in our Power BI online course.

⭕ Keep the public in mind

What are the key metrics that will help you make decisions? How will the report be used? What cultural or learned assumptions might affect design alternatives? What information does your audience require to achieve good results?

Where will the report be displayed? If the dashboard is presented on a large screen, it can accommodate more information. If readers will be viewing it on tablets, the fewer views it has, the more readable it will be.

⭕ Tell a story and show it on a screen

Each page of your report should tell a story at a glance. Can you avoid scroll bars on the pages? Is the report too cluttered or busy? Remove everything except essential information that can be easily read and interpreted.

⭕ Show the most important information in a larger size

If both the visualizations and the text on your report page are the same size, it will be difficult for readers to focus on what is really important. For example, one way to highlight an important number is through visualizations:

card display

⭕ But don't forget to provide context

Use features such as tooltips or text boxes along with tools to add context to your visualizations.

⭕ Put the most important information at the top

Most people read from top to bottom, which is why it is suggested to put the most detailed information at the top and show more details as you go in the direction the audience reads (from left to right, or from right to left).

⭕ Use the correct format and visualization for data to make it easier to read

Discovering another of the Power BI Desktop Tricks, we recommend avoiding using different types of visualizations just to provide variety. Visualizations have to convey a picture, and be easy to “read” and understand. For some data and visualizations, a simple graphical visualization is enough. However, other data may require a more complex visualization. Therefore, be sure to use titles, labels, and any necessary customizations to help the reader.

✅ Be consistent with chart scales on the axes, the order of chart dimensions, and the colors you use for chart dimension values.

Be careful when using graphics that distort reality, such as three-dimensional ones and those that don't start from scratch. Keep in mind that the human brain has a harder time interpreting circular shapes. Pie charts, doughnut charts, gauge charts, and other types of circular charts may look appealing, but there are alternatives.

✅Try to avoid mixing up accuracy and time levels. Make sure that time intervals are fully understood. Don't put a chart from last month next to filtered charts from a specific month of the year.

Make sure you code quantitative data correctly. Use no more than three or four digits when displaying numbers. Show measurements with one or two numbers to the left of the decimal point, and reduce thousands or millions—that is, write “3.4 million” instead of “3,400,000.”

✅Don't clutter your charts with unnecessary data labels. Typically, bar chart values, if large enough, are easy to understand without the need to show the actual number.

Don't mix large and small measures on the same scale, such as in a bar or line chart. For example, one measure might be in millions and another might be in thousands. With such a large scale, it would be difficult to understand the differences in the thousands measure. If you must mix them, choose a visualization, such as a combination chart, that allows you to use a second axis.

✅Pie charts work best if they don't exceed eight categories. Values can't be compared side-by-side, and it's harder to do so in a pie chart than in bar and column charts. Pie charts are useful for seeing the relationships of a part to a whole, not for comparing elements. Finally, gauge charts are great for showing current status in the context of a goal.

Pay attention to how you sort your charts. If you want to draw attention to the highest or lowest number, sort by measure. If you want users to be able to quickly find a particular category among many other categories, sort by axis.

Learn more about designing panels according to best practices

These are some of our favorite books:

✔ Narrative with data, by Cole Nussbaumer Knafic

✔ Data Points, by Nathan Yau

✔ The True Art, by Alberto Cairo

✔ Now You See It, by Stephen Few

✔ Information Visualization, by Edward Tufte

✔ Advanced Presentation Design, by Andrew Abela

Ultimately, these Power BI Desktop tricks will help you optimize your time and the use of this fabulous tool for an impressive dashboard and smart decision making.

Creating professional management dashboards using the powerful Power BI Desktop tool IS possible. Join our Microsoft Power BI – Business Intelligence course today and start acquiring all the knowledge necessary to create your Dashboard, automate your area and improve presentations for managerial decision making.