cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Need to Create New Column using Combination of multiple row with same Code

Hello Users,

I need to create one custom column using Power Query / DAX

as per below images, i have one column conatins code against each item Name

in result i  need one column that conatins only single line for each item (IF both item having same code)

please find below images for refrence. i need output as per shown in output snapshot.

1 ACCEPTED SOLUTION
Super User

Hi @Dhrutivyasa-070 you can create NEW table in Power BI as following:

TableCombo = ADDCOLUMNS (
VALUES ( Sheet7[Code] ),
"Qty", [M_Qty],
"Item (Combo)",
CONCATENATEX (
CALCULATETABLE ( VALUES ( Sheet7[Item] ) ),
Sheet7[Item],
" + ",
Sheet7[Item],
ASC
)
)

TableCombo - is name of NEW table
Sheet7 you should adjust to your table
M_Qty = SUM(Sheet7[Qty]) - this is measure which you should create so table above is working

Original reference to this solution: https://dax.guide/concatenatex/
Output should be like on picture below
I hope this help

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Helper V

cobovalues =

CALCULATE(DISTINCT(code),CONCATENATE([item],"+",[item]),SUM(Qty),IF(COUNTROWS(DISTINCT(code),"Combo","-")))

Super User

Hi @Dhrutivyasa-070 you can create NEW table in Power BI as following:

TableCombo = ADDCOLUMNS (
VALUES ( Sheet7[Code] ),
"Qty", [M_Qty],
"Item (Combo)",
CONCATENATEX (
CALCULATETABLE ( VALUES ( Sheet7[Item] ) ),
Sheet7[Item],
" + ",
Sheet7[Item],
ASC
)
)

TableCombo - is name of NEW table
Sheet7 you should adjust to your table
M_Qty = SUM(Sheet7[Qty]) - this is measure which you should create so table above is working

Original reference to this solution: https://dax.guide/concatenatex/
Output should be like on picture below
I hope this help

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper I

Thanks for the solution, it worked for me!

Super User

please try the following measures

Item (Combo) =
CONCATENATEX ( VALUES ( 'Table'[Item] ), 'Table'[Item], ", " )

Total Qty =
SUM ( 'Table'[Qty] )

Remark =
IF ( COUNTROWS ( VALUES ( 'Table'[Item] ) ) > 1, "Combo", "-" )

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors