A calculated column helps with data. It turns raw data into useful information. These columns make your data better. They let you look at your data more closely. You have to pick between Power Query and DAX. This choice is important. It helps your computer run well. It also keeps your data correct. Bad columns make your Power BI model bigger. They also make it take longer to update. This uses more computer memory. Just one column can make your data model much larger. This slows down how fast things are figured out. This guide will show you how to make great Power Query columns.
Key Takeaways
Power Query custom columns are good for cleaning data. They shape data before it goes into your Power BI model. They save computer memory. You can use them in many reports.
DAX calculated columns are for hard jobs. These jobs need all the data. An example is making parent-child lists. Use them for special things. Do not use them for normal data changes.
Always try Power Query first. Use it to make new columns. It makes your reports faster. It uses less memory.
Do not use too many DAX calculated columns. They can make your data model bigger. They can slow down your reports.
Use measures for math that changes. This depends on what users do. Measures do not use memory right away. This makes your reports work better.
Power Query custom columns
Power Query custom columns are strong tools. They figure out values early. This happens when you update your data. This is like DAX calculated columns. But, Power Query has big pluses. You can make your data analysis better. These columns fit well in your data. They are very flexible.
Power Query Column Benefits
You get good things with Power Query custom columns. First, you can use them again. You make a custom column one time. Then, you use it in many Power BI semantic models. This is true with Dataflows. Dataflows put your data prep in one spot. So, you do not need many connections. This way of reusing saves work. It makes your data model faster. Second, Power Query lets you remove extra columns. You might use a column to make a new one. After it’s made, you can delete the first column. This uses less memory. Power BI keeps data in memory. Removing columns you don’t need makes your model smaller. This makes it work better. Third, Power Query has a good look. You can make hard columns. You don’t write much M formula. Tools like “Column From Examples“ help. “Merge Columns” also helps. You can even find age from dates easily. This makes your work faster. This whole process changes data.
How to Create a Custom Column
Making a custom column in Power Query is easy.
Open the Power Query Editor.
Pick the table. You want to add a column.
Go to the Add Column tab.
Click on Custom Column. A new box opens.
Name your new custom column.
Write your custom column formula. Use columns from the list.
Click OK. Power Query adds the column. You do these steps in the Power Query Editor.
Practical Custom Column Examples
Let’s see some examples. You can make formulas easily.
Margin Calculation: Think you want profit. Margin is sales minus cost. You can make a custom column formula like
[Sales Amount] - [Total Product Cost]. This finds the margin for each row.Full Name Concatenation: You have “First Name” and “Last Name”. You want a “Full Name”. Use “Merge Columns”. It’s in the “Add Column“ tab. Or, make a custom column with
[First Name] & “ “ & [Last Name].Age Calculation: Power Query makes age simple. Pick your birth date column. Go to the “Add Column“ tab. Choose “Date” then “Age.” This gives age as a time. Then, pick “Total Years.” This gives age in years. You can even round it down. You don’t need a hard formula. This change is fast.
To change your custom column, go to “Applied Steps”. It’s in Power Query Editor. Find “Added Custom Column“. Click the gear icon. This opens the formula box. You can then change your custom column formula. This makes data prep good.
DAX Calculated Columns
DAX Column Fundamentals
DAX calculated columns are strong tools. They are in Power BI. You make these columns. You use a DAX formula. The system figures out values. This happens when data updates. It saves them in your data model. Values are ready. You see your report. Each formula runs row by row. It checks every record. This is in your table. If data changes, the column updates.
Calculated columns are not like DAX measures. Measures figure out values. This happens when you use them. They do not save results. Calculated columns save results. They are fixed. They are row-by-row. Measures change with context. Calculated columns work for each row. This is in the table.
DAX Column Creation
Making a DAX column is easy. This is in Power BI Desktop.
First, pick the table. It is in the ‘Fields pane’.
Next, go to the menu bar. You see ‘Table tools’. Click ‘New column’.
A formula bar shows up. Write your DAX formula here. For example, find margin:
Margin = ‘Internet Sales’[Sales Amount] - ‘Internet Sales’[Total Product Cost].Press Enter. Your formula is used.
A new column shows. It is in your table. You see it in the ‘Field Pane’.
Advanced DAX Column Use Cases
DAX calculated columns are good for certain things. They gather data. This is from other tables. You can also combine tables. These are from different places. This stops slow processing. This is for gathering data.
One advanced use is “Path” functions. These help with parent-child lists. For example, PATH finds a full path. This is in a list. PATHLENGTH tells you how deep it is. PATHITEM gets specific things. This is from the path. These make lists you can look through. This is harder in Power Query. You can build a strong chart. This shows how DAX columns are good. This is for complex tasks.
Power Query vs. DAX Columns in Power BI
You now get both Power Query custom columns and DAX calculated columns. You need to know when to use each. This choice changes your Power BI solution. It affects how fast it runs. It also affects how easy it is to keep up.
Key Differences and Performance
You will find big differences. These are between Power Query custom columns and DAX calculated columns. These differences help you decide.
First, think about the language. You make custom columns with Power Query M Language. You make calculated columns with DAX expressions. DAX is for Power BI calculated columns. M is for Power Query custom columns.
Both types of columns figure out values early. This happens when data updates. They save these values in your model. But, they are not reused the same way. You can reuse Power Query custom columns. Use them in many Power BI semantic models. This is true with Dataflows. DAX calculated columns are only for one model. You cannot easily reuse them.
Memory use is another big difference. Power Query custom columns use memory better. You can remove columns you used to make a new one. This makes your model use less memory. DAX calculated columns do not let you do this. You can only hide columns. They still use memory.
Power Query custom columns compute when data loads. They also compute during transformation. They become part of the loaded dataset. They use memory. This makes things faster. Calculations finish before reports run. DAX calculated columns also save results. They are in the data model. They use memory. These values stay the same. They stay static until you refresh the data model.
Power Query custom columns usually run faster. This is because of memory use. They change data earlier. They are also easier to write. They are easier to fix. They are easier to test. This makes them a better choice.
Choosing the Right Method
You need a clear plan. This helps you pick the right tool.
You should use Power Query first. Use it for most pre-calculated column needs. This is because of its good points. Power Query custom columns are best. Use them when you need a pre-calculated result. This makes reports run better. This is true with aggregated tables. This method works well for static ranking. It also works for pre-calculated ranking.
You should save DAX for special cases. Use DAX for specific things. This includes complex pathing. This is for hierarchies. You might also need it. This is when the calculation needs the full data model. These situations are rare. This is for calculated columns.
Best Practices for Columns
You want to make great columns. Focus on being efficient. Focus on being easy to keep up. Pick the right tool for the job.
Optimize DAX Formulas: You should make your DAX formulas better. Add comments. Use variables to make them clear. Use
DIVIDEfor division. Do not useIFERROR. Filter column values correctly. Filter measure values by columns. Do not filter by tables. AvoidDISTINCTCOUNT. This is on columns with many unique values. ChooseSUMMARIZECOLUMNS. Do not chooseSUMMARIZE. Avoid going over large tables. Do not do this if it is not needed.Leverage Query Folding in Power Query: You should send data changes back to the source. This makes the DAX model work less. Use Power Query for fast work. This makes things run quicker. Remove steps you do not need. Do this in Power Query. This stops longer load times.
Data Modeling Principles: You should make your data simple. Organize it into good tables. This avoids repeating data. It makes it easier to keep up. Use relationships wisely. Make clear and good relationships. Make data types better. This uses less memory. It makes things run better.
Prioritize Measures over Calculated Columns: You should not use too many calculated columns. Use them only when you must. Too many calculated columns make the data model bigger. They affect how fast it runs. Use measures for changing calculations. They handle totals. They handle rules. Measures do not use memory until you call them. This makes them work better.
You should always start preparing data. Do this in the Power Query editor. Its easy-to-use screen helps you. Its M language helps you. It helps you get data ready. This is for looking at it. This is for showing it. DAX is for a second step. It handles calculations. These are with many records. It also handles totals for single facts.
Using many DAX calculated columns makes things slow. This is true for changes you could do in Power Query. Calculated columns save values in the model. They use memory. They make data refreshes slower. You should change data in Power Query. Do this whenever you can. Use measures for changing totals. These need to follow filter rules.
Power Query is best for getting data. It is best for cleaning it. It is best for organizing it. It makes your data better. This is before you look at it. It does things like joining tables. It filters records. It changes data types. DAX is best for changing analysis. It handles calculations. These change with what the user does. Use DAX for making measures. Use it for totals. Use it for time-based calculations. DAX calculations happen right away. This makes reports more flexible.
You now get Power Query and DAX. They make calculated columns. Power Query custom columns change data well. DAX helps with hard analysis. This is in your Power BI model. Learn both ways. This makes strong Power BI models. Use these ideas. You will make your data better. This leads to great custom columns.
FAQ
What is the main difference between Power Query and DAX columns?
Power Query columns change data. This happens before it enters your model. DAX columns figure out values. This happens once data is in your model. Both figure out results early. This is during data refresh.
When should you use Power Query custom columns?
You should use Power Query. Use it for most columns figured out early. It is good for cleaning data. It is good for shaping data. It is good for combining data. Power Query helps save memory. It also lets you use columns again. This is across different models.
When should you use DAX calculated columns?
You should use DAX. Use it for special, hard jobs. This includes parent-child lists. Use
PATHfunctions for this. DAX is also good. This is when calculations need all data. This is rare for calculated columns.
Do Power Query and DAX columns affect memory differently?
Yes, they do. Power Query lets you remove columns. These are columns used in the middle. This saves memory. DAX columns keep all columns. These are columns used in the middle. You can only hide them. This means they still use memory. This is in your model.
Can you reuse calculated columns?
You can reuse Power Query custom columns easily. Use them in many Power BI models. This is true with Dataflows. DAX calculated columns stay in one Power BI model. You cannot easily reuse them.
