itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: combine multiple values

In this article, I will explain how to do it with Power Query Editor but if you are more interested with formula in calculated columns, read Power BI: combine words based on criteria. I have those 2 tables:

Table1 Table2
power bi power bi

To go to the query editor, click on “home -> transform data -> transform data”:

power bi

I want to concatenate values of the “table2”, for that, I will click on “group by”:

power bi power bi

About the fields:

  • The first field: select the column to group
  • New column name: put a name
  • Operation: select the option like in the picture
power bi

Replace the “red” part with this formula:

each Text.Combine([argument], " ")

power bi

NOTE: change argument by yours

Now if my “table2” has multiple columns to combine like that:

power bi

Once the “group by” part is done, don’t replace the “red” part and follow the following steps. Click on “add column -> custom column”:

power bi

Put this formula:

Text.Combine(List.Transform([argument1][ argument2],Text.From)," ")

power bi

NOTE:

  • argument1: it is the name of the “new column name” of “group by”
  • argument2: it is the column that I want to combine values
  • new column name: I put “state” but I can put whatever I want
  • values are separated by a space, if you want to separate with coma, change " " by ","

I will repeat the same thing for the other columns:

power bi

If I want those results in the “table1”, I have 2 options:

power bi

Interesting Topics