Today we’re exploring how to merge columns Power Query Editor. Sometimes you get stuck with data where two columns have data that SHOULD be in one column. We’re going to learn how to easily bring them together.

Post Subjects: Power Query

This post (and video below) uses a new blank Power BI report and connects some simple data to introduce how to merge columns. To download the source Excel data file this report uses to try out what I do in this post and video, below is the data file you can connect to Power BI Desktop.

If you need help learning how to connect Power BI to an Excel Data file, check this post.

Video:

Written Instructions:

Sometimes, we have to connect to data where a data point is split into different columns when it should be in one, and you can’t modify the data source. Let’s connect our data source (download link above the video above) and learn how to bring them together.

Let’s start with a blank Power BI report and click the big green “Import Data from Excel button”. We’ll navigate to where our data source is, named “Power Query Merging Columns” if you’re using the data linked above, select it, and click Open.

When we get our Navigator window opening up, we can see the one tab in our spreadsheet, named “Location”, click it and let’s have a look at our data preview.

This is data with addresses, but we have a problem. Our Street Address is split into 2 different columns. The street number is in one column and the street name is in another.

We want them in the same column, so instead of clicking Load in our Navigator window, we’re going to click “Transform Data”.

Now we have our data loaded into Power Query Editor and to merge our “Address 1” and “Address 2” columns together, we only need to click a few things.

First, click the “Transform” tab up at the top of the ribbon to load up the Transform ribbon that has all kinds of fun transformer-y tools.

You may see a “Merge Columns” button in the Transform ribbon, but it’s greyed out. We need to activate it by telling it what columns we’re interested in Merging.

Hold your CTRL key and click the headers of your Address 1 and Address 2 columns.

The “Merge Columns” button activates, and now we can click it to merge our two columns!

Power BI asks if we want a separator between them, and what the new column should be called. We’ll indicate that we want a space between them and that we want the new column to be named “Address”.

Then click OK.

That’s it! Now we have those two pieces of data in ONE column that we can use for visuals like Maps.

Take care,

Joe.