1. Power Bi Merge Tables Into New Table
  2. Power Bi Merge Tables On Two Columns
  3. Power Bi Merge Vs Append
-->

APPLIES TO: Power BI service for consumers Power BI service for designers & developers Power BI Desktop Requires Pro or Premium license

Note

These visuals can be created and viewed in both Power BI Desktop and the Power BI service. The steps and illustrations in this article are from Power BI Desktop.

Just because you can merge all the tables using Power Query, it doesn’t mean you should do it. The process of data modeling is not based on CAD DOs, it is based on SHOULD DOs. Power BI modeling starts with a pen and paper This is very important that you start your modeling by a pen and paper. 0 votes n Power BI Desktop you can join two tables with Merge menu item in the Query Editor, in Home tab, Under Combine, Merge Queries. The Merge Window will appear with ability to select first table (Left part of the join), and the second table (Right part of the join). Answered Dec 16, 2020 by Gitika. Merge action in Power Query is a way to have two tables with one or more joining fields to match their records and create a flattened table. In the screenshot below, you can see that the two tables are merged based on the Title (in the left table), and Course (in the right table).

In Power BI, a combo chart is a single visualization that combines a line chart and a column chart. Combining the 2 charts into one lets you make a quicker comparison of the data.

Combo charts can have one or two Y axes.

When to use a Combo chart

Combo charts are a great choice:

  • when you have a line chart and a column chart with the same X axis.
  • to compare multiple measures with different value ranges.
  • to illustrate the correlation between two measures in one visualization.
  • to check whether one measure meet the target which is defined by another measure
  • to conserve canvas space.

Note

Sharing your report with a Power BI colleague requires that you both have individual Power BI Pro licenses or that the report is saved in Premium capacity.

Prerequisites

This tutorial uses the Retail Analysis sample PBIX file.

  1. From the upper left section of the menubar, select File > Open

  2. Find your copy of the Retail Analysis sample PBIX file

  3. Open the Retail Analysis sample PBIX file in report view .

  4. Select to add a new page.

Create a basic, single-axis, Combo Chart

Watch Will create a combo chart using the Sales and Marketing sample.

Note

This video uses an older version of Power BI Desktop.

  1. Start on a blank report page and create a column chart that displays this year's sales and gross margin by month.

    a. From the Fields pane, select Sales > This Year Sales > Value.

    b. Drag Sales > Gross Margin This Year to the Value well.

    c. Select Time > FiscalMonth to add it to the Axis well.

  2. Select More options (...) in the upper-right corner of the visualization, and select Sort by > FiscalMonth. To change the sort order, select the ellipsis again and choose either Sort ascending or Sort descending. For this example will use Sort ascending.

  3. Convert the column chart to a combo chart. There are two combo charts available: Line and stacked column and Line and clustered column. With the column chart selected, from the Visualizations pane select the Line and clustered column chart.

  4. From the Fields pane, drag Sales > Last Year Sales to the Line Values bucket.

    Your combo chart should look something like this:

Create a combo chart with two axes

In this task, we'll compare gross margin and sales.

  1. Create a new line chart that tracks Gross Margin last year % by FiscalMonth. Select the ellipsis to sort it by Month and Ascending.
    In January GM% was 35%, peaked at 45% in April, dropped in July and peaked again in August. Will we see a similar pattern in sales last year and this year?

  2. Add This Year Sales > Value and Last Year Sales to the line chart. The scale of Gross Margin Last Year % is much smaller than the scale of Sales which makes it difficult to compare.

  3. To make the visual easier to read and interpret, convert the line chart to a Line and Stacked Column chart.

  4. Drag Gross Margin Last Year % from Column Values into Line Values. Power BI creates two axes, thus allowing the datasets to be scaled differently; the left measures sales dollars and the right measures percentage. And we see the answer to our question; yes, we do see a similar pattern.

Add titles to the axes

  1. Select the paint roller icon to open the Formatting pane.

  2. Select the down arrow to expand the Y-axis options.

  3. For Y-Axis (Column), set Position to Left, set Title to On, Style to Show title only, and Display units as Millions.

  4. Under Y-Axis (Column), scroll down until you see Show secondary. Because there are so many options for the Y axes, you may have to use both scrollbars. The Show secondary section displays options for formatting the line chart portion of the combo chart.

  5. For Y-Axis (Line), leave Position as Right, turn Title to On, and set Style to Show title only.

    Your combo chart now displays dual axes, both with titles.

  6. Optionally, modify the text font, size, and color and set other formatting options to improve the display and readability of the chart.

From here you might want to:

  • Add the combo chart as a dashboard tile.
  • Save the report.
  • Make the report more accessible for people with disabilities.
Power bi merge tables on two columns

Cross-highlighting and cross-filtering

Highlighting a column or line in a combo chart cross-highlights and cross-filters the other visualizations on the report page... and vice versa. Use visual interactions to change this default behavior.

Next steps

When building Power BI reports we often need to join two (or more) tables together, but what if the relationship is defined by two or more columns? Relationships in Power BI are limited to single columns, but whilst this seems like a major limitation there is actually a simple solution to create a relationship with multiple columns in Power BI.
To create a relationship with multiple columns in Power BI we simply need to create a new column by merging the required columns together. What’s more, if we use the same name in both queries Power BI will automatically create the relationship for us.

To do this, we open the Power Query Editor using the Transform Data button…

Either from the Get Data Navigator when adding the data sources:

or from the ribbon:

For my example I have two queries; Job_Planning_Lines and Job_Task_Lines, and I want to create a relationship between them using the two columns Job_No and Job_Task_No:

For each query we select the column we want to include, hold down the CTRL key after selecting the first:

Note: the order in which you select the fields will determine the order the values are displayed in the new column.

Now it’s decision time, do we want to create the new field and remove the original fields.. or do we want to keep the original fields?

To create the new key field and remove the original fields we select Merge Columns from the Transform tab:

To create the new column but retain the original columns in our dataset we must use the Merge Columns button on the Add Column tab:

Once we’ve selected the appropriate Merge Column button, Power BI will ask for a delimiter and a name for this new column:

You can choose to add a separator or not, I’ve chosen the colon character above and I’ve named the new column JobNoJobTaskNo. Remember to use the same setting in both queries.

Once the new column has been created in both queries save the changes with the Close & Apply button on the Home tab:

Merge two identical tables power bi

Power Bi Merge Tables Into New Table

If we now view the data we can see the new column:

And the relationship has been automatically created by Power BI:

Power Bi Merge Tables On Two Columns

Using the new column:

Power Bi Merge Vs Append

That’s it!

Coments are closed

Most Viewed Posts

  • Tatsugotchi
  • Many Cam Software
  • Excel Combine Multiple Rows Into One

Scroll to top