create two calculated columns (Concatenatex)

Hi Kris48 

To create the two calculated columns "Module_Ver 2" and "Value_Ver 2" in Power BI, you can use the CONCATENATEX function in DAX. Here's how you can do it:

Assumptions:

• Table Name: Replace 'YourTable' with the actual name of your table.
• Grouping Column: Replace 'KeyColumn' with the column you want to group by (e.g., an ID or date).
• Columns to Concatenate: 'Module' and 'Value' are the columns you want to concatenate.

Steps:

1. Module_Ver 2 Column

Create a new calculated column in your table with the following DAX formula:

Module_Ver 2 = 
VAR CurrentKey = 'YourTable'[KeyColumn]
RETURN
CONCATENATEX(
    FILTER(
        'YourTable',
        'YourTable'[KeyColumn] = CurrentKey
    ),
    'YourTable'[Module],
    ", "
)

2. Value_Ver 2 Column

Create another calculated column with this formula:

Value_Ver 2 = 
VAR CurrentKey = 'YourTable'[KeyColumn]
RETURN
CONCATENATEX(
    FILTER(
        'YourTable',
        'YourTable'[KeyColumn] = CurrentKey
    ),
    'YourTable'[Value],
    ", "
)

Explanation:

• CurrentKey: Captures the value of the grouping key for the current row.
• FILTER: Filters the table to include only rows with the same key as the current row.
• CONCATENATEX: Concatenates the specified column's values (e.g., 'Module' or 'Value') from the filtered table, separated by ", ".

Alternative Using Power Query:

If you prefer to do this in Power Query:

1. Group By Key Column:

   • Go to Home > Group By.
   • Group by your key column (KeyColumn).
   • Create two aggregation columns:
     • Module_Ver 2: Use the operation All Rows.
     • Value_Ver 2: Use the operation All Rows.

2. Add Custom Columns:

   • Add a custom column for Module_Ver 2:

= Text.Combine([GroupedRows][Module], ", ")

Add a custom column for Value_Ver 2:

= Text.Combine([GroupedRows][Value], ", ")

1. Cleanup:

   • Remove unnecessary columns resulting from the grouping.
   • Ensure only the desired columns remain.

Note:

• Replace 'YourTable', 'KeyColumn', 'Module', and 'Value' with the actual names from your dataset.
• Ensure that your data model relationships support the calculations.

By following these steps, you should be able to create the two new columns as per your requirements.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

Thanks for the reply. Previously, I also tried to use ChatGPT - gpt4, unfortunately it could not help me as in this case

Adding column Value_Ver 2 comes out nonsense:

The expected result is as follows: