Introduction

Power BI offers with the Power Query Editor a powerful tool for cleaning and transforming data. One important part of data preparation is splitting columns. Sometimes a column may contain a lot of information. In such cases, it can be difficult to analyze and visualize the data. To get a better structure and organization of the information, the column can be divided into several columns. A typical example is a date column that can be split into day, month and year. In this tutorial, we will show you how to split columns in Power Query by using a hands-on example.

Data

The data for this tutorial comes from an Excel file. In the following tutorial, we have shown how to import Excel into Power BI:

Power BI - Import Data from Excel file
Introduction In this tutorial, we want to import Data from a Excel file into Power BI. This can be easily done in the Power BI Desktop interface. Data Source Our data source is the Excel file student. The file contains the following data: Power BI Desktop In the following, we
Deep Learning Nerds | The ultimate Learning Platform for AI and Data ScienceData Analyst

We have loaded the data into Power BI but we haven't executed any transformations on the data. The data looks as follows:

Open Power Query Editor

First, we open the Power Query Editor by clicking on "Transform Data":

This takes us to the Power Query Editor interface:

💡
All steps that are performed are displayed on the right-hand sidebar. You have the opportunity to undo steps at any time.

Split Column

Now, let's explore how to split columns in Power Query. In the following example, we want to split the column "birthdate" into three columns. We want to have day, month and year of the birthdate as separate columns.

In order to split the column "birthdate", we select the column, click on "Split Column" and choose "By Delimiter":

Next, a new window opens in which we enter the desired delimiter. Since the birth date has the format "dd.mm.yyyy" we enter "." as delimiter. We want to split at each occurrence of the delimiter. After making our selection, we click on "OK":

Now, we get three new columns: One column for the day, one column for the month and one column for the year:

Conclusion

Congratulations! Now you are one step closer to become an AI Expert. You have learned how to split columns in Power Query. You have seen that splitting columns can be simply performed in the user interface of the Power Query Editor. You have also seen a typical example of splitting columns: A date column often has to be split into day, month and year. Try it yourself!