Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Replace values in a column containing delimiter list

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 IDDiscount IDs
1234564567,5678
7891017890
234567 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.