create two calculated columns (Concatenatex)

[Kris48]

Please help me create two calculated columns. They can be created in Power Query (I don't care). I would like to create new "Module_Ver 2" and "Value_Ver 2" speakers. I have explained the problem in the pictures below
Link PBIX: https://drive.google.com/file/d/1lebnBAOhJITh1b3DwQjT221upIkNVaDL/view?usp=sharing 

 

[tharunkumarRTK · Accepted solution]

@Kris48 

Module Ver 2

Value Ver 2

please fine the PBIX attached

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Kris48]

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:

[VahidDM]

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