how to concatenate texts from another table

[Tomek1982]

Hi,

I have follwing two tables: 

 

standard table looks like this:

How can I concatenate those strings to achieve such a result?

pbix is available here

[Resolutions]

@Tomek1982 To concatenate strings from two tables and achieve the desired result, you can use Power Query in Power BI. Here's how you can do it step-by-step:

Step 1: Load both tables into Power BI:
Make sure both tables are loaded into Power BI. The first table should be named "Table1," and the second table should be named "Table2."

Step 2: Merge the Tables:
Go to the "Home" tab in Power Query Editor. Select "Merge Queries" from the "Combine" group. Choose "Table1" as the first table and "Table2" as the second table to merge.

Select the "ID" column from both tables as the key to perform the merge. Choose "Inner" as the join kind. This will keep only the rows with matching IDs from both tables.

Step 3: Expand the Columns:
After the merge, go to the "Home" tab again and click on the expand icon next to the "Table2" column in the merged table. Select the "String" column to expand it.

Step 4: Concatenate the Strings:
Now, you should have a new column in the merged table containing the "String" values from "Table2." Go to the "Add Column" tab and click on "Custom Column." Name the new column, for example, "ConcatenatedStrings."

In the "Custom Column Formula" box, use the following expression to concatenate the strings from the "String" column:
```
[Column1] & ", " & [Column2] & ", " & [Column3] & ", " & [Column4] & ", " & [Column5] & ", " & [Column6]
```

Replace "Column1," "Column2," and so on with the actual names of the columns in "Table2" that contain the string values you want to concatenate.

Step 5: Remove Unnecessary Columns:
Once you have the "ConcatenatedStrings" column, you can remove the other columns from "Table2" if you don't need them.

Step 6: Load the Data:
Click on the "Close & Apply" button to apply the changes and load the data into Power BI.

Now, you should have a table with the desired result, where the "ConcatenatedStrings" column contains the concatenated strings from "Table2" based on the matching IDs.

Please note that the steps provided are based on the assumption that "Table2" has a one-to-one relationship with "Table1" based on the "ID" column. If the relationship is different, you may need to adjust the merge and expansion steps accordingly.

[Tomek1982 · Accepted solution]

@Resolutions 

Partly your solution is OK, up tp step 4 following this I have following result:

later I have 1 to many relation ship so I used the function:

Column = CALCULATE(CONCATENATEX('Merge1','Merge1'[cost 1], UNICHAR(10) ),FILTER('Merge1','Merge1'[case ID]='Table'[case ID]))

with a following result, which is good enough: