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

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

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

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

Replace the “red” part with this formula:

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

NOTE:

• Change argument by yours
• To define a table, replace [argument] by table[argument]
• For specific rows based on index for instance:
  Text.Combine(Table.SelectRows(table,each [index]>=10 and [index]<=20)[argument],", ")

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

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

Put this formula:

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

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 ","
• to remove duplicate, use the formula in Power BI: delete duplicates by replacing [argument] by this formula

I will repeat the same thing for the other columns:

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

• Use the “merge queries”, read Power BI: filter before loading data
• Use the RELATED function, read Power BI: extract columns from a table to another one