Data Transformation Techniques in Power Bi : Columns From Example

Introduction

Data transformation is a critical task for data analysts, enabling the extraction of valuable insights from raw data. One essential aspect of data transformation in Power BI is using the “Columns From Examples” feature in the Power Query Editor. This guide will explore how to create columns from examples in Power BI, with practical examples and script snippets, tailored for data analysts.

What is “Columns From Examples”?

“Columns From Examples” is a feature in the Power Query Editor that allows you to create new columns by providing examples of the desired output based on existing columns. Power BI then uses these examples to generate the necessary transformation logic automatically. This feature is especially useful for data analysts who need to create custom columns without writing complex M code.

Importance of “Columns From Examples”

Using “Columns From Examples” allows data analysts to:

• Quickly create custom columns without coding.
• Simplify the data transformation process.
• Ensure accuracy by generating transformation logic based on user-provided examples.
• Enhance productivity by reducing the time spent on manual data manipulation.

Step-by-Step Process for Creating Columns From Examples in Power BI

1. Loading Data into Power BI

Begin by loading your dataset into Power BI. You can import data from various sources such as Excel, SQL databases, and web data.

1. Open Power BI Desktop.

2. Click on Get Data from the Home ribbon.

3. Select your data source and follow the prompts to load the data.

2. Opening the Power Query Editor

Once your data is loaded, access the Power Query Editor to start creating new columns.

1. In Power BI Desktop, navigate to the Home tab.
2. Click on Transform Data to open the Power Query Editor.

3. Creating Columns From Examples

In the Power Query Editor, you can create columns from examples using various techniques and options.

Example 1: Creating a Custom Column Based on Text

Suppose you have a column with full names, and you want to create a new column with only the first names.

1. In the Power Query Editor, select the table you want to modify.
2. Go to the Add Column tab.
3. Click on Column From Examples and select From Selection.
   • A new pane will appear where you can enter examples of the desired output.
4. Select the column containing full names.
5. Enter the first name for the first row in the new column.
6. Power BI will automatically generate the transformation logic based on your example.

let
    Source = <Your data source>,
    AddedCustom = Table.AddColumn(Source, "FirstName", each Text.BeforeDelimiter([FullName], " "))
in
    AddedCustom

Example 2: Creating a Custom Column Based on Dates

Assume you have a column with date values, and you want to create a new column that extracts the year from the date.

1. In the Power Query Editor, select the table you want to modify.
2. Go to the Add Column tab.
3. Click on Column From Examples and select From Selection.
   • A new pane will appear where you can enter examples of the desired output.
4. Select the column containing date values.
5. Enter the year for the first row in the new column.
6. Power BI will automatically generate the transformation logic based on your example.

let
    Source = <Your data source>,
    AddedCustom = Table.AddColumn(Source, "Year", each Date.Year([DateColumn]))
in
    AddedCustom

4. Fine-Tuning the Generated Column

After creating a column from examples, you can fine-tune the generated column to ensure it meets your requirements.

• Review the generated transformation logic.
• Modify the logic if necessary to handle edge cases or specific data conditions.
• Validate the new column to ensure accuracy.

Conclusion

Creating columns from examples in Power BI is a powerful technique for data analysts. This feature simplifies the data transformation process by allowing you to provide examples of the desired output, and Power BI generates the necessary transformation logic. Whether creating custom columns based on text, dates, or other data types, the “Columns From Examples” feature enhances productivity and accuracy in data transformation.

Frequently Asked Questions