This post demonstrates a simple example of how to split a single column into two separate columns, based on a delimiter contained in the original field. This technique is standard within the Power Query Editor page of Power BI. The build-in functionality is contained on the “Home” tab in the “Split Column” command. I recently took a training class in Power BI and will be building out a BLOG section on my site to get into the details of this tool!
Setup – Power BI Split Column By Delimiter
In the Power BI Desktop window, when you click the “Get Data” button on the “Home” tab, there is an option to import data from Excel. Here is what the raw data column in Excel looks like:
As you can see, the column “Player” is actually made up of two separate pieces of information, split by the pipe “|” symbol:
- Player Name
In order to create these two separate fields, go through the following steps in Power BI’s Power Query Editor.
First, click on the Split Column icon and select “By Delimiter”
Next, choose “Custom” and for our example, enter in the pipe “|” symbol as the delimiter. Make sure the “Split at” option “Each occurrence of the delimiter” is selected, then click on “OK”
Next, you will now see two separate columns in the Power Query Editor. Rename each column respectively (Player & Position). Then click on the “Close & Apply” option:
This will now execute the query against your Excel data source, and save a new table in the Power BI Desktop window of your application. You can then explore your newly created table, seeing the two separate columns in your data model!
It’s as simple as that! If you encounter this scenario when evaluating your source data, you can easily make this transformation within Power BI!