Having a clean, well-organized data model is essential for user adoption and consumption. Changes made to the Power BI model have no effect on source data or systems. This document reviews several types of data transformations and actions that can be done within Power BI Desktop to help you build an easy-to-use and performant model.

DAX Formatter

Using DAX Formatter helps you read the code easier.

1. Copy your DAX code from your PBI Desktop file. Example below is pre-formatted code
   
2. Paste your code on the DAX Formatter website
   
3. Copy and paste new formatted code back to your PBI Desktop file

Adding Data Refresh Date/Time

1. Create a table for Data Refresh Time
   1. = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
2. Create a Custom Column
   1. Use =DateTime.AddZone([LastRefresh], 5)
   2. For daylight saving, use =DateTime.AddZone([LastRefresh], 4)
   3. 

Rename Tables

Tables should be named to be easily understood by report authors and consumers. Instead of a table name ListOfCountries, name the table Countries.

Rename Columns

Like tables, column names should be short and easily understood. In the above Countries table, consider the simple column Country instead of RegionCountryName. Giving columns appropriate names in their tables, instead of renaming them in the visual, is both user-friendly and efficient, since names only have to be changed once.

Remove Unnecessary Columns

Columns with no analytical value should be removed entirely from your data model. This makes your model smaller and more performant, and also increases usability since consumers are not spending time wading through unnecessary data. For maximum efficiency, columns should be removed as an early step in the data cleansing process so query folding can occur.

Filter Data

Sometimes, you may need to exclude certain values from your data model, or only include certain values. For example, you might need records created on or after a certain date. Filtering data in the Query Editor stops the data from being loaded into your model and reduces load and refresh times in your report. Completing this step early on also leverages query folding.

Query Folding

Query folding refers to pushing the work of filtering or transforming data back to the source. When executing queries against databases, Power BI can push the filter back to the source query to improve performance. This optimized filtering is not an explicit action in Power BI, rather filtering data early on in transformation steps allows query folding to occur.

To see if your transformation has been included in query folding, right click on it in the Applied Steps pane. If View Native Query is available, then that step has been folded. Select View Native Query to see the query being sent back to the source.

Sources that support query folding

• Relational databases (such as SQL server and Oracle)
• Analysis Services
• OData sources (such as SharePoint lists, Azure Marketplace, etc.)
• Active Directory
• Exchange
• HDFS, Folder.Files and Folder.Contents

Things that prevent query folding

• Custom SQL statements
• Certain filters like "This Month"
• Some privacy level settings (refer to the Privacy Settings guidance for additional information)
• Using "Remove rows with errors"
• Defining and using your own functions