# Power Query Optimization: Reducing Decimal Numbers

In this tutorial, youâ€™ll learn a Power Query optimization technique to reduce RAM usage. Specifically, youâ€™ll learn how to reduce the numbers stored after a decimal place. This is helpful if youâ€™re dealing with columns stored in a decimal number format.

In this example, you can see that the Net Price is consuming 11MB of RAM whereas the Total Cost, Unit Cost, and Unit Price are consuming 8MB each.

When you go to Power Query and click the filter option of the Net Price column, you can see that the column is storing values of up to three decimal places.

This can cause a performance issue. If multiple digits are stored as the decimal number, the dictionary will contain more unique values. This will cause VertiPaq to create a huge storage structure for the dictionary.

Itâ€™s therefore recommended to optimize these columns.

## Power Query Optimization For Columns

Ideally, you want to store values up to two decimal places. Right-click on the column you want to optimize. Click Transform > Round > Round.

Then, set the Decimal Places to 2 and click Okay.

A quick way to transform all the columns in one go is by highlighting them and then following the same steps.

This applies the decimal number format to all the columns using a single step. Once done, save your work.

### Check RAM Usage

To check how much RAM was reduced, open DAX Studio. Go to the Advanced tab and click View Metrics.

By comparing the original with the optimized columns, you can see reductions in the Column Size and Dictionary Size.

For this example, it would appear that the difference in kilobytes doesnâ€™t amount to a good reduction in RAM space. However, if youâ€™re dealing with more distinct values such as numbers with seven decimal places, the savings in RAM would be great.

You can also notice that for the Unit Price column, the cardinality doesnâ€™t change but thereâ€™s a significant reduction in the column size.

When you reduce the granularity or make changes to the data model in a column, youâ€™re introducing a new sort order in Analysis Services. This can either increase or decrease the data model size.

Even if the number of distinct values didnâ€™t change, Analysis Services might have found a better sort order which reduced the size of the column.

This optimization technique is especially helpful if youâ€™re storing a Date/Time column. This could be storing values up to the millisecond.

When you reduce the cardinality of each value to a second, this reduces the unique values in the Dictionary.

Simple Power BI Transformations For More Optimized Data
Optimize Power BI Formulas Using Advanced DAX
Storage Engine â€“ Its Role In Optimizing DAX Queries In Power BI

## Conclusion

If you’re working with a tabular model, focus on reducing the cardinality of a column.

The cardinality is the deciding factor on the amount of RAM the data model will consume. It also tells you how much time it’ll take to scan a particular column when you execute your code.

Optimizing your DAX queries is crucial in maintaining a good Power BI report. Not only does it ensure that your work performs well, but it also reduces the strain on your machine.

Enterprise DNA Experts

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...