Replace values in a column containing delimiter list

[Anonymous]

I have a booking table with a column called DiscountID which contains discount codes that are comma delimetered (as below). I have a seperate discounts table with a name for each discount and the discount ID. Is it possible to replace the discount codes with the name of the discounts and keep the delimetered format? How would I do this in DAX (I'm using Direct Query so can't do it in M)

Booking ID	Discount IDs
123456	4567,5678
789101	7890
234567

[amitchandak]

@Anonymous , I think few steps.

1. Split delimiter into rows: Power Query

2. merge with discount code table: Power Query

3. Create a table with discount code delimited by comma: DAX or Power Query

[Anonymous · Accepted solution]

I can't do that in PowerQuery. Ended up doing it this way:

Discount Name = 
VAR DiscountList =  SUBSTITUTE(MasterTicket[DiscountIDs], ",", "|")
Var DiscountCount = if(DiscountList = "",0,PATHLENGTH(DiscountList))
var discountTable = ADDCOLUMNS(GENERATESERIES(1,DiscountCount),"DiscountCodeColumn",VALUE(PATHITEM(DiscountList,[Value])))
return CONCATENATEX(discountTable,LOOKUPVALUE(Discount[Name],Discount[DiscountId],[DiscountCodeColumn]),",")