How to Add a Calculated Column to Power BI Direct Lake Models

Understanding the Challenge

When attempting to add a calculated column to a Direct Lake model, you may find that the option is greyed out in the Model view. This can be frustrating, especially if you’re used to adding calculated columns with ease in other contexts. Currently, there’s no direct way to do this within Power BI’s interface. However, there are workarounds that can help you achieve your goals.

In our discussion, we emphasize the importance of addressing this issue as far upstream as possible. This means making changes at the source level before bringing the data into your semantic model.

Using Excel as a Data Source

Let’s start with a common scenario: using Excel as your data source. If you have access to the spreadsheet, it’s simple to add a calculated column directly in Excel. For example, consider a table with columns for CustomerID, FirstName, and LastName. You can create a new column that concatenates the FirstName and LastName by simply using the formula:

FirstName & " " & LastName

Drag down to apply this formula for all rows, and now you’ve added the calculated column at the source level.

Working with SQL Server

If your data source is SQL Server, the process is slightly different. You may not have direct access to modify the database, so you would need to communicate with your DBA or data engineer. Request them to create a view that includes the calculated column. This view can concatenate the FirstName and LastName, similar to the Excel example.

The SQL command for this might look like:

SELECT CustomerID, FirstName, LastName, (FirstName + ' ' + LastName) AS CustomerName FROM Customers

Once the view is created, you can use Dataflow’s Gen2 as your ETL tool to pull this data into your Power BI model.

Creating the View

As a data engineer or ETL developer, you would create a view in SQL that looks like this:

CREATE VIEW vw_Customers AS SELECT CustomerID, FirstName, LastName, (FirstName + ' ' + LastName) AS CustomerName FROM Customers

Once the view is set up, you can proceed to configure your Dataflow to point to this view instead of the original table. This way, when you run your Dataflow, the new calculated column will be included in the data pulled into your model.

Updating Your Dataflow

Next, you need to update your Dataflow to reflect these changes. In Dataflow’s Gen2, change the table reference to point to your new view:

FROM vw_Customers

After saving your settings and running the Dataflow, you should see the new CustomerName column populated with the concatenated values.

Using Spark SQL in Lakehouse

For those utilizing a lakehouse, you can also add a calculated column using Spark SQL. After altering the table to include the new column, ensure that your ETL process is updated to reflect this change. The Spark SQL command would look something like this:

ALTER TABLE Customers ADD COLUMN CustomerName STRING

Then you can run an update statement to populate it:

UPDATE Customers SET CustomerName = CONCAT(FirstName, ' ', LastName)

Handling DBA Restrictions

If your DBA is unwilling to create views or alter tables, you can still achieve a similar outcome using Power BI’s data flows. You can add a new column from examples in your data flow. This method allows you to specify how the new column should be formed based on existing data.

To do this, go into your data flows, select “Add Column” and then use the “Column from Examples” feature. Provide an example of what the concatenated CustomerName should look like, and Power BI will generate the necessary transformations for you.

Refreshing Your Semantic Model

Once you’ve added the calculated column through any of the methods above, the next step is to ensure it appears in your semantic model. To do this, navigate to your semantic model and select “Open Data Model.” If the new column doesn’t show up, you might need to refresh the schema.

In Power BI, you can do this by going to your workspace settings, enabling the option for users to edit data models, and then clicking on “Fetch Schema.” This should update your model to include the new column.

Final Thoughts

While adding a calculated column directly in Power BI’s Direct Lake models is currently not supported, there are several effective workarounds. By making these changes upstream, whether through Excel, SQL Server views, or Spark SQL in a lakehouse, you can still achieve your desired outcomes. Remember to refresh your semantic model to ensure all changes are reflected in your reports.

For more in-depth training on Power BI and its features, consider checking out our AI training programs designed to equip you with the necessary skills and knowledge.