Combine 2 Columns In Power Bi

  1. Combine 2 Columns In Power Bi Using
  2. How To Combine 2 Columns In Power Bi

Combine Multiple Tables in Power BI: In real-time, your data is in a normalized format, but in some situations, you might need the de-normalized data. In this situation, you can combine those tables using a query editor. 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. @AngadK, i got if after using the dax in a new table, is it possible to combine them in the same table? What i want: am planning to show the hours spent on each project on a particular date, i have 4 columns of project IDs for each person everyday hence i want to combine the IDs into one column.

Let’s say you have a few numerical columns [A], [B] and [C] in your table and want to sum them to the new column in Power Query or Query Editor in Power BI.

Now you will get the new table name as ‘Merge 1’ and you can see the last two columns have the same name. You can remove the duplicate column by right clicking on that column and clicking on ‘Remove’. 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.

In Power Query we have special buttons for this:

For example, we want to sum columns [A] and [C]. Just click (holding Ctrl button) column headers you want to sum, then go to “Add Column” – “Standard” – “Add”, and you’ll get a new column named “Addition” with the row-by-row sum of desired columns:

If we want to add three columns at a time, then we’ll also get a desired result:

But if in this table we want so sum columns [A] and [B], we are not expecting a pitfall, aren’t we?

The reason of this behaviour is simple and it reveals itself when we look at our data a little bit close: there is a null in column [B] in that row. In Power Query formula language (M) the expression null + value always returns a null (see this excellent post of Ben Gribaudo about null type and operations with null values).

Power

But why we get a correct result when we sum up three columns? It is because Power Query uses different formulas when we sum two columns or three and more columns:

List.Sum function used in this case ignores null values and sums up only numerical values. Indeed, it gives more intuitive result, but on the contrary has not such intuitive syntax of simple addition.

Columns

I do not know what is the reason of such difference, and already complained to the development team. But if you rely on the buttons there, then you have to be aware of such behaviour.

Combine 2 columns in power bi when sharing

What is the possible solutions there? It depends on what you want to get as a result, but in any case you should take a look at the formula bar and decide what to correct there:

  • If the logic of your calculations assume that value + null = null, then you should use simple + symbol between column names.
  • If you want to get value + null = value, then you should use List.Sum finction, like in that example: List.Sum({[A], [B], [C]})

THE SAME BEHAVIOR Power Query shows when you’ll try to multiply two columns and three or more columns: with two columns there will be the simple * symbol, with three or more columns there will be List.Product function used.

Ok, it is a really short post which I planned to (and ought to) write a long time ago…

Incremental Refresh For Pro Accounts With Power BI Service Dataflows
-->

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.

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

Combine 2 Columns In Power Bi Using

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

How To Combine 2 Columns In Power Bi

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.

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