Transforming data is a critical step in Power BI that allows you to shape and prepare data for analysis. In this blog post, we’ll explore four essential column transformations that can enhance your data modeling capabilities.

Splitting Columns by Delimiter

Splitting columns by delimiter is useful when you have combined fields that need to be separated into individual columns. In the following example, we have the information regarding the state and country of airports in one column. The two values are separated by a comma. We now want to split these values so that we have one column for the state and one column for the country.

Steps:

• Go to the Power Query Editor.
• Select the column you want to split.
• Choose the Split Column option from the Home tab.
• Specify the delimiter and how you want to split the column (e.g., by each occurrence of the delimiter).

This function is handy for parsing out data like full names into first and last names or separating addresses into city, state, and zip code.

Creating a Conditional Column

Conditional columns allow you to create new data based on conditions or rules applied to your existing data. In the example below, we will add a column that contains True if a flight was delayed and False if it was on time.

Steps:

• In the Power Query Editor, navigate to the Add Column tab.
• Click on Conditional Column.
• Define the conditions and the output for each condition.

Creating a Custom Column

Custom columns are created using Power Query M language to calculate new data based on existing columns. Both examples above can also be achieved by using custom columns. When using the custom columns, however, the function must be written manually. But they give you more freedom and flexibility.

Steps:

• In the Power Query Editor, navigate to the Add Column tab.
• Click on Custom Column.
• Write your function and click on “ok”

In the example above I created a new column called “Country”. Using the Text.AfterDelimiter() function I extract the Country from the STATE_COUNTRY column.

A list including explanations of the available Power Query M function can be found here.

Creating a Column from Examples

Power BI allows you to create new columns by providing examples of the desired output. “Columns from Examples” uses AI to detect patterns and generate the corresponding formula to create the column.

Steps:

• In the Power Query Editor, go to the Add Column tab.
• Select Column from Examples.
• Type in the example output based on existing data.

In the example above I just typed in “USA” in the first row of the new Column. Power BI then searches the existing column for this value and detects the pattern. The formula applied is then also provided (red box in top left corner of image above). We can now see, that the formula applied is identical with the one used in the custom column.

Column transformations are just the a small part of what you can do with Power BI to turn raw data into insightful, report-ready information. Experiment with these tools to discover the full potential of your data!