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.
![Launch Power BI](https://articles.geekster.in/wp-content/uploads/2024/07/7-16-1024x576.png)
2. Click on Get Data from the Home ribbon.
![Launch Power BI](https://articles.geekster.in/wp-content/uploads/2024/07/8-15-1024x576.png)
3. Select your data source and follow the prompts to load the data.
![Launch Power BI](https://articles.geekster.in/wp-content/uploads/2024/07/9-15-1024x576.png)
![Launch Power BI](https://articles.geekster.in/wp-content/uploads/2024/07/10-12-1024x576.png)
![Launch Power BI](https://articles.geekster.in/wp-content/uploads/2024/07/11-14-1024x576.png)
2. Opening the Power Query Editor
Once your data is loaded, access the Power Query Editor to start creating new columns.
- In Power BI Desktop, navigate to the Home tab.
- Click on Transform Data to open the Power Query Editor.
![Power Query Editor](https://articles.geekster.in/wp-content/uploads/2024/07/25-4-1024x576.png)
![Power Query Editor](https://articles.geekster.in/wp-content/uploads/2024/07/26-4-1024x576.png)
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.
- In the Power Query Editor, select the table you want to modify.
- Go to the Add Column tab.
- Click on Column From Examples and select From Selection.
- A new pane will appear where you can enter examples of the desired output.
- Select the column containing full names.
- Enter the first name for the first row in the new column.
- 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
DAXExample 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.
- In the Power Query Editor, select the table you want to modify.
- Go to the Add Column tab.
- Click on Column From Examples and select From Selection.
- A new pane will appear where you can enter examples of the desired output.
- Select the column containing date values.
- Enter the year for the first row in the new column.
- 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
DAX4. 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
You can create various types of columns, including text transformations (e.g., extracting first names), date manipulations (e.g., extracting years), numerical calculations, and more.
Power BI uses machine learning algorithms to analyze the examples you provide and generate the corresponding M code to perform the transformation.
Yes, you can review and modify the generated transformation logic to handle specific data conditions or edge cases, ensuring the new column meets your requirements.