Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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]),",")
@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
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]),",")
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
45 |