I am currently cleaning a dataset in the Power BI Power Query Editor and need to append an additional column to an existing table. I want to derive this new column based on values from two other columns to create a "Total Profit" metric. Should I be using a Custom Column with M formula language, or is it better to use the "Column from Examples" feature to ensure the data types are handled correctly during the transformation process?
3 answers
To add a column in Power Query, navigate to the "Add Column" tab on the top ribbon. If you need a simple calculation, select "Custom Column" and enter your formula, such as [Revenue] - [Cost]. This uses the M language. For logic-based columns, like assigning a category based on a value range, use the "Conditional Column" UI, which provides a user-friendly "If-Then-Else" interface. Once added, remember to check the data type of the new column, as it often defaults to "Any." Changing it to "Fixed Decimal Number" or "Whole Number" is crucial for your DAX measures to work later in the report view.
Are you planning to perform this calculation in Power Query for data shaping, or would it be more efficient to create a Calculated Column using DAX once the data is already loaded into the model?
I highly recommend trying "Column from Examples." You just type in what you want the result to look like for a few rows, and Power BI writes the M code for you automatically.
I agree with Nancy. "Column from Examples" is incredibly powerful for complex string manipulations or date extractions where writing the exact M function manually might be confusing for beginners.
That is a great question, Steven. I've heard that adding columns in Power Query is better for data compression and performance, whereas DAX columns can bloat the file size. Since I have over five million rows, would moving the "Total Profit" logic into the Power Query M code provide a noticeable improvement in report refresh times compared to doing it in the front-end model?