Forum Discussion
Solved
Combine rows based on column value
I am not sure exactly how to phrase this, which probably explains why I can't find a pre-existing answer. Anyway I have a table that has multiple items which are repeated throughout by month and yea...
Hi bvilten,
You can try to use following formulas to extract item group and combine sub items.
Calculate column formulas:
Item Group = PATHITEM(SUBSTITUTE([Item],"/","|"),1) Combined = CALCULATE ( CONCATENATEX ( VALUES ( Sheet1[Item] ), REPLACE ( [Item], 1, LEN ( PATHITEM ( SUBSTITUTE ( [Item], "/", "|" ), 1 ) ) + 1, "" ), "/" ), FILTER ( ALL ( Sheet1 ), [Item Group] = EARLIER ( Sheet1[Item Group] ) ) )Regards,
Xiaoxin Sheng
Hi bvilten,
You can try to use following formulas to extract item group and combine sub items.
Calculate column formulas:
Item Group = PATHITEM(SUBSTITUTE([Item],"/","|"),1)
Combined =
CALCULATE (
CONCATENATEX (
VALUES ( Sheet1[Item] ),
REPLACE (
[Item],
1,
LEN ( PATHITEM ( SUBSTITUTE ( [Item], "/", "|" ), 1 ) ) + 1,
""
),
"/"
),
FILTER ( ALL ( Sheet1 ), [Item Group] = EARLIER ( Sheet1[Item Group] ) )
)
Regards,
Xiaoxin Sheng
