Power Bi Merge Two Columns

Apr 26, 2021 The Merge window appears. It prompts you to select which table you'd like merged into the selected table, and the matching columns to use for the merge. Select State from the RetirementStats table, then select the StateCodes query. When you select the correct matching columns, the OK button is enabled. Combining two queries in Power Query or in Power BI is one of the most basic and also essential tasks that you would need to do in most of data preparation scenarios. There are two types of combining queries; Merge, and Append. Database developers easily understand the difference, but the majority of Power BI users Read more about Append vs. Merge in Power BI and Power Query. How do we merge two queries in power bi when the primary key is a combination of 2 columns. For example i have 2 tables. Table A: year cost center col a col b col c Table B: year cost center col x col y col z The primary key in both the columns is a combination of the 1st two columns. How do i merge these two queries?


With Power BI Desktop, you can connect to many different types of data sources, then shape the data to meet your needs, enabling you to create visual reports to share with others. Shaping data means transforming the data: renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and so on. Combining data means connecting to two or more data sources, shaping them as needed, then consolidating them into a useful query.

In this tutorial, you'll learn how to:

  • Shape data by using Power Query Editor.
  • Connect to different data sources.
  • Combine those data sources, and create a data model to use in reports.

This tutorial demonstrates how to shape a query by using Power BI Desktop, highlighting the most common tasks. The query used here is described in more detail, including how to create the query from scratch, in Getting Started with Power BI Desktop.

Power Query Editor in Power BI Desktop makes ample use of right-click menus, as well as the Transform ribbon. Most of what you can select in the ribbon is also available by right-clicking an item, such as a column, and choosing from the menu that appears.

Shape data

When you shape data in Power Query Editor, you provide step-by-step instructions for Power Query Editor to carry out for you to adjust the data as it loads and presents it. The original data source isn't affected; only this particular view of the data is adjusted, or shaped.

The steps you specify (such as rename a table, transform a data type, or delete a column) are recorded by Power Query Editor. Each time this query connects to the data source, Power Query Editor carries out those steps so that the data is always shaped the way you specify. This process occurs whenever you use Power Query Editor, or for anyone who uses your shared query, such as on the Power BI service. Those steps are captured, sequentially, in the Query Settings pane, under Applied Steps. We’ll go through each of those steps in the next few paragraphs.

Join two columns power query

From Getting Started with Power BI Desktop, let's use the retirement data, which we found by connecting to a web data source, to shape that data to fit our needs. We'll add a custom column to calculate rank based on all data being equal factors, and compare this column to the existing column, Rank.

Power Bi Merge Two Text Columns

  1. From the Add Column ribbon, select Custom Column, which lets you add a custom column.

  2. In the Custom Column window, in New column name, enter New Rank. In Custom column formula, enter the following data:

  3. Make sure the status message is No syntax errors have been detected, and select OK.

  4. To keep column data consistent, transform the new column values to whole numbers. To change them, right-click the column header, and then select Change Type > Whole Number.

    If you need to choose more than one column, select a column, hold down SHIFT, select additional adjacent columns, and then right-click a column header. You can also use the CTRL key to choose non-adjacent columns.

  5. To transform column data types, in which you transform the current data type to another, select Data Type Text from the Transform ribbon.

  6. In Query Settings, the Applied Steps list reflects any shaping steps applied to the data. To remove a step from the shaping process, select the X to the left of the step.

    In the following image, the Applied Steps list reflects the added steps so far:

    • Source: Connecting to the website.

    • Extracted Table from Html: Selecting the table.

    • Changed Type: Changing text-based number columns from Text to Whole Number.

    • Added Custom: Adding a custom column.

    • Changed Type1: The last applied step.

Adjust data

Before we can work with this query, we need to make a few changes to adjust its data:

  • Adjust the rankings by removing a column.

    We've decided Cost of living is a non-factor in our results. After removing this column, we find that the data remains unchanged.

  • Fix a few errors.

    Because we removed a column, we need to readjust our calculations in the New Rank column, which involves changing a formula.

  • Sort the data.

    Sort the data based on the New Rank and Rank columns.

  • Replace the data.

    We'll highlight how to replace a specific value and the need of inserting an Applied Step.

  • Change the table name.

    Because Table 0 isn't a useful descriptor for the table, we'll change its name.

  1. To remove the Cost of living column, select the column, choose the Home tab from the ribbon, and then select Remove Columns.

    Notice the New Rank values haven't changed, due to the ordering of the steps. Because Power Query Editor records the steps sequentially, yet independently, of each other, you can move each Applied Step up or down in the sequence.

  2. Right-click a step. Power Query Editor provides a menu that lets you do the following tasks:

    • Rename; Rename the step.
    • Delete: Delete the step.
    • DeleteUntil End: Remove the current step, and all subsequent steps.
    • Move before: Move the step up in the list.
    • Move after: Move the step down in the list.
  3. Move up the last step, Removed Columns, to just above the Added Custom step.

  4. Select the Added Custom step.

    Notice the data now shows Error, which we'll need to address.

    There are a few ways to get more information about each error. If you select the cell without clicking on the word Error, Power Query Editor displays the error information.

    If you select the word Error directly, Power Query Editor creates an Applied Step in the Query Settings pane and displays information about the error.

  5. Because we don't need to display information about the errors, select Cancel.

  6. To fix the errors, select the New Rank column, then display the column's data formula by selecting the Formula Bar checkbox from the View tab.

  7. Remove the Cost of living parameter and decrement the divisor, by changing the formula as follows:

  8. Select the green checkmark to the left of the formula box or press Enter.

Power Query Editor replaces the data with the revised values and the Added Custom step completes with no errors.


You can also select Remove Errors, by using the ribbon or the right-click menu, which removes any rows that have errors. However, we didn't want to do so in this tutorial because we wanted to preserve the data in the table.

  1. Sort the data based on the New Rank column. First, select the last applied step, Changed Type1 to display the most recent data. Then, select the drop-down located next to the New Rank column header and select Sort Ascending.

    The data is now sorted according to New Rank. However, if you look at the Rank column, you'll notice the data isn't sorted properly in cases where the New Rank value is a tie. We'll fix it in the next step.

  2. To fix the data sorting issue, select the New Rank column and change the formula in the Formula Bar to the following formula:

  3. Select the green checkmark to the left of the formula box or press Enter.

    The rows are now ordered in accordance with both New Rank and Rank. In addition, you can select an Applied Step anywhere in the list, and continue shaping the data at that point in the sequence. Power Query Editor automatically inserts a new step directly after the currently selected Applied Step.

  4. In Applied Step, select the step preceding the custom column, which is the Removed Columns step. Here we'll replace the value of the Weather ranking in Arizona. Right-click the appropriate cell that contains Arizona's Weather ranking, and then select Replace Values. Note which Applied Step is currently selected.

  5. Select Insert.

    Because we're inserting a step, Power Query Editor warns us about the danger of doing so; subsequent steps could cause the query to break.

  6. Change the data value to 51.

    Power Query Editor replaces the data for Arizona. When you create a new Applied Step, Power Query Editor names it based on the action; in this case, Replaced Value. If you have more than one step with the same name in your query, Power Query Editor adds a number (in sequence) to each subsequent Applied Step to differentiate between them.

  7. Select the last Applied Step, Sorted Rows.

    Notice the data has changed regarding Arizona's new ranking. This change occurs because we inserted the Replaced Value step in the correct location, before the Added Custom step.

  8. Lastly, we want to change the name of that table to something descriptive. In the Query Settings pane, under Properties, enter the new name of the table, and then select Enter. Name this table RetirementStats.

    When we start creating reports, it’s useful to have descriptive table names, especially when we connect to multiple data sources, which are listed in the Fields pane of the Report view.

    We’ve now shaped our data to the extent we need to. Next let’s connect to another data source, and combine data.

Combine data

The data about various states is interesting, and will be useful for building additional analysis efforts and queries. But there’s one problem: most data out there uses a two-letter abbreviation for state codes, not the full name of the state. We need a way to associate state names with their abbreviations.

We’re in luck; there’s another public data source that does just that, but it needs a fair amount of shaping before we can connect it to our retirement table. TO shape the data, follow these steps:

  1. From the Home ribbon in Power Query Editor, select New Source > Web.

  2. Enter the address of the website for state abbreviations, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations, and then select Connect.

    The Navigator displays the content of the website.

  3. Select Codes and abbreviations.


    It will take quite a bit of shaping to pare this table’s data down to what we want. Is there a faster or easier way to accomplish the steps below? Yes, we could create a relationship between the two tables, and shape the data based on that relationship. The following steps are still good to learn for working with tables; however, relationships can help you quickly use data from multiple tables.

To get the data into shape, follow these steps:

  1. Remove the top row. Because it's a result of the way that the web page’s table was created, we don’t need it. From the Home ribbon, select Remove Rows > Remove Top Rows.

    The Remove Top Rows window appears, letting you specify how many rows you want to remove.


    If Power BI accidentally imports the table headers as a row in your data table, you can select Use First Row As Headers from the Home tab, or from the Transform tab in the ribbon, to fix your table.

  2. Remove the bottom 26 rows. These rows are U.S. territories, which we don’t need to include. From the Home ribbon, select Remove Rows > Remove Bottom Rows.

  3. Because the RetirementStats table doesn't have information for Washington DC, we need to filter it from our list. Select the Region Status drop-down, then clear the checkbox beside Federal district.

  4. Remove a few unneeded columns. Because we need only the mapping of each state to its official two-letter abbreviation, we can remove several columns. First select a column, then hold down the CTRL key and select each of the other columns to be removed. From the Home tab on the ribbon, select Remove Columns > Remove Columns.


    This is a good time to point out that the sequence of applied steps in Power Query Editor is important, and can affect how the data is shaped. It’s also important to consider how one step may impact another subsequent step; if you remove a step from the Applied Steps, subsequent steps may not behave as originally intended, because of the impact of the query’s sequence of steps.


    When you resize the Power Query Editor window to make the width smaller, some ribbon items are condensed to make the best use of visible space. When you increase the width of the Power Query Editor window, the ribbon items expand to make the most use of the increased ribbon area.

  5. Rename the columns and the table. There are a few ways to rename a column: First, select the column, then either select Rename from the Transform tab on the ribbon, or right-click and select Rename. The following image has arrows pointing to both options; you only need to choose one.

  6. Rename the columns to State Name and State Code. To rename the table, enter the Name in the Query Settings pane. Name this table StateCodes.

Combine queries

Now that we’ve shaped the StateCodes table the way we want, let’s combine these two tables, or queries, into one. Because the tables we now have are a result of the queries we applied to the data, they’re often referred to as queries.

There are two primary ways of combining queries: merging and appending.

Power bi merge two columns in excel
  • When you have one or more columns that you’d like to add to another query, you merge the queries.
  • When you have additional rows of data that you’d like to add to an existing query, you append the query.

In this case, we want to merge the queries. To do so, follow these steps:

  1. From the left pane of Power Query Editor, select the query into which you want the other query to merge. In this case, it's RetirementStats.

  2. Select Merge Queries > Merge Queries from the Home tab on the ribbon.

    You may be prompted to set the privacy levels, to ensure the data is combined without including or transferring data you don't want transferred.

    The Merge window appears. It prompts you to select which table you'd like merged into the selected table, and the matching columns to use for the merge.

  3. Select State from the RetirementStats table, then select the StateCodes query.

    When you select the correct matching columns, the OK button is enabled.

  4. Select OK.

    Power Query Editor creates a new column at the end of the query, which contains the contents of the table (query) that was merged with the existing query. All columns from the merged query are condensed into the column, but you can Expand the table and include whichever columns you want.

  5. To expand the merged table, and select which columns to include, select the expand icon ().

    The Expand window appears.

  6. In this case, we want only the State Code column. Select that column, clear Use original column name as prefix, and then select OK.

    If we had left the checkbox selected for Use original column name as prefix, the merged column would be named NewColumn.State Code.


    Want to explore how to bring in the NewColumn table? You can experiment a bit, and if you don’t like the results, just delete that step from the Applied Steps list in the Query Settings pane; your query returns to the state prior to applying that Expand step. You can do this as many times as you like until the expand process looks the way you want it.

    We now have a single query (table) that combines two data sources, each of which has been shaped to meet our needs. This query can serve as a basis for many additional and interesting data connections, such as housing cost statistics, demographics, or job opportunities in any state.

  7. To apply your changes and close Power Query Editor, select Close & Apply from the Home ribbon tab.

    The transformed dataset appears in Power BI Desktop, ready to be used for creating reports.

Next steps

For more information on Power BI Desktop and its capabilities, see the following resources:


When Power BI Desktop creates visuals, it aggregates your data into chunks (or groups) based on values found in the underlying data. Often that's fine, but there may be times when you want to refine how those chunks are presented. For example, you might want to place three categories of products in one larger category (one group). Or, you might want to see sales figures put into bin-sizes of 1,000,000 dollars, instead of chunks of 923,983-dollar sizes.

In Power BI Desktop, you can group data points to help you more clearly view, analyze, and explore data and trends in your visuals. You can also define the bin size to put values into equally sized groups that better enable you to visualized data in ways that are meaningful. This action is often called binning.

Using grouping

To use grouping, select two or more elements on a visual by using Ctrl+click to select multiple elements. Then right-click one of the multiple selection elements and choose Group data from the context menu.

Once it's created, the group is added to the Legend bucket for the visual. The group also appears in the Fields list.

Once you have a group, you can easily edit the members of that group. Right-click the field from the Legend bucket or from the Fields list, and then choose Edit groups.

In the Groups dialog box, you can create new groups or modify existing groups. You can also rename any group. Just double-click the group title in the Groups and members box, and then enter a new name.

You can do all sorts of things with groups. You can add items from the Ungrouped values list into a new group or into one of the existing groups. To create a new group, select two or more items (using Ctrl+click) from the Ungrouped values box, and then select the Group button below that box.

Power Bi Concatenate Two Columns

You can add an ungrouped value into an existing group: just select the one of the Ungrouped values, then select the existing group to add the value to, and select the Group button. To remove an item from a group, select it from the Groups and members box, and then select Ungroup. You can also move ungrouped categories into the Other group or leave them ungrouped.


You can create groups for any field in the Fields well, without having to select multiple items from an existing visual. Just right-click the field, and select New group from the menu that appears.

Using binning

You can set the bin size for numerical and time fields in Power BI Desktop. You can make bins for calculated columns, but not for measures. Use binning to right-size the data that Power BI Desktop displays.

To apply a bin size, right-click a Field and choose New group.


From the Groups dialog box, set the Bin size to the size you want.

When you select OK, you'll notice that a new field appears in the Fields pane with (bins) appended. You can then drag that field onto the canvas to use the bin size in a visual.

Power Bi Merge Two Columns Using

To see binning in action, take a look at this video.

Power Bi Union

And that's all there is to using grouping and binning to ensure the visuals in your reports show your data just the way you want them to.