In this tutorial, I will discuss Power BI datasets, including data types and the importance of naming conventions.
The data used in this example is a resource pack made available for Enterprise DNA members.
We want to make sure that when we import this Excel data into our Power BI model, we get an error-free rendering.
If we’re not careful, we could miss out on random errors hidden within our Power BI datasets. We can avoid this by making sure that the data types are correct.
Formatting Data Types
Data types are the common cause of errors in Power BI models.
The Query Editor is a feature in Power BI that can easily identify and format data types.
You can determine data types through the images or icons beside each column/data name.
For example, we can easily identify that the OrderDate column is in a date format.
We can also use the Home Ribbon to look at all the data types within our table.
Aside from date types, there are also number and text data types.
An important thing you need to remember is to make sure that numeric columns should have the correct data type since it can affect future calculations and aggregations.
Numeric columns have values as data types.
In this example, these are Unit Price, Order Quantity, Total Revenue, and Total Unit Cost.
Checking The Data Type
To check whether the data imported from Excel to Power BI is correct, we simply need to click on Data Type under the Home tab.
With this function, changing, correcting, and formatting the data types is easy within the Query Editor.
Also, always remember to click Close & Apply to keep the changes you’ve made.
Changing The Data Type
We can also change the data types within the data itself.
By selecting a column in the table and going to the Column Tools tab, we can quickly change the data type by making a selection in the Format drop down box.
This is a great technique to use to organize the data and columns in our tables.
Updating Naming Conventions
One of my best practices when it comes to organizing Power BI datasets is to have good naming conventions. I always make my data names as intuitive as possible.
IntelliSense is a feature in Power BI that automatically shows suggestions within a measure. However, it’s only possible if the names are in a proper format.
A proper format means refraining from using underscores, random names, and abbreviations.
As an example, if we create a new measure to calculate the Total Sales, suggestions appear as we type.
But again, this is only possible if we use names that are intuitive.
This is a crucial detail to implement in our data models because it makes it easy to reference the type of column we want to use in our formulas.
If we want to use the column in our Sales Data to calculate Total Revenue, all we need to do is type “Rev” and that column will appear in the formula bar.
It’s possible that other people in a team will be using the information within Power BI to create reports and analytics.
Thus, making data names intuitive makes it easier for everyone in the organization to use IntelliSense and find what they need to make their calculations.
Keeping Names Simple
We need to make sure that data, table, and column names are kept simple.
In this example, the underscores and abbreviations used to name data are unnecessary.
We need to make sure that when other people look at a formula, they’ll understand what it is.
Similar to data types, Query Editor is a great place in Power BI to update data names.
Click the Transform Data button under the Home tab to access the Query Editor.
The first step in naming conventions should be changing the table names.
Tables and columns are important aspects in Power BI since they’re used for referencing data. Thus, we need to make their names simple and accessible.
Formatting Data From A Web-Based System
Data and information downloaded from the web or a database are usually abbreviated and have unusual labels and column names.
Before using this data in our formulas and models, we need to make them clean and seamless inside the Query Editor.
The Power BI Query Editor presents a good summary and snapshot of all the data contained in our model. Thus, using this feature in Power BI allows for easy data referencing and indexing.
***** Related Links *****
Turning Calendar Type Layout Into Tabular Format In Power BI Using Query Editor
Custom Conditional Formatting Techniques In Power BI
My Practice Tips When Using The Power BI Advanced Editor
Conclusion
Good naming conventions and optimized data types allow Power BI users to quickly locate and reference data in their formulas and models.
Incorrect formats such as data type errors and underscores can be easily fixed in the Query Editor.
Compared to individually fixing titles, axes, and tool tips, the Query Editor allows us to make the adjustments all at once.
The techniques discussed in this tutorial are great ways to save time and effort on menial tasks.
All the best,
Sam
