cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

 

Thanks in Advance.

Dhrutivyasa070_0-1685439389109.png

 

1 ACCEPTED SOLUTION
some_bih
Memorable Member
Memorable Member

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
 
some_bih_0-1685445678606.png

 

Sarajevo, Bosnia and Herzegovina

View solution in original post

4 REPLIES 4
devanshi
Helper V
Helper V

cobovalues =

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

 

some_bih
Memorable Member
Memorable Member

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
 
some_bih_0-1685445678606.png

 

Sarajevo, Bosnia and Herzegovina

Thanks for the solution, it worked for me!

tamerj1
Super User
Super User

Hi @Dhrutivyasa-070 

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", "-" )

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors