Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a multiple columns named as overall , sub1 given, sub2 given , sub3 return
Overall | sub1 Given | sub2 Given | sub 3 Return |
Bag | |||
Bag | Bottle | Purse | |
Bag | Bag | ||
Headset | Monitor | ||
Notebook | Diary | ||
Bag, Notebook,Bottle | Bag | ||
Diary |
I want to concatenate (overall + sub1 given + sub2 given ) - sub3 return , If overall , sub1given,sub2given contains the value as in sub3 return then the new column should elimate that value. Also if overall , sub1 given , sub2 given contains the same string then it should write the string only one time ,The result should look like below
Overall Accessories |
Bag |
Bag,Bottle,Purse |
Bag |
Headset,Monitor |
Notebook,Diary |
Notebook,Bottle |
Thank you
Regards,
Sonali Saha
Solved! Go to Solution.
Hi @sonalisaha2310,
Here is a way to solve it with DAX:
DAX code in plain text for convenience (see also the attached file):
Result =
VAR curRow = SUBSTITUTE ( [Overall], " ", "" )
VAR curRowH = SUBSTITUTE ( curRow, ",", "|" )
VAR nItems = PATHLENGTH ( curRowH )
VAR res = IF ( NOT ISBLANK ( nItems ),
VAR _tbl1 = SUMMARIZE ( ADDCOLUMNS ( GENERATESERIES ( 1, nItems ), "sub", PATHITEM ( curRowH, [Value] ) ), [sub] )
VAR _tbl2 = DISTINCT ( UNION ( _tbl1, { [sub1 Given], [sub2 Given] } ) )
VAR _tbl3 = FILTER ( _tbl2, NOT ISBLANK ( [sub] ) && [sub] <> [sub3 Return] )
RETURN CONCATENATEX ( _tbl3, [sub], "," ),
VAR _tbl4 = FILTER ( DISTINCT ( { [sub1 Given], [sub2 Given] } ), NOT ISBLANK ( [Value] ) && [Value] <> [sub3 Return] )
RETURN CONCATENATEX ( _tbl4, [Value], "," ) )
RETURN res
Best Regards,
Alexander
Hello @barritown ,
I have written calculated column measure with the query that you have provided
Now suppose the table looks like below
Name | Accessories(Calculated Column Measure) |
A | Bag,Monitor,Mouse |
B | Mouse |
C | Monitor,Bag |
The result should be
Name | Accessories (Calculated Column Measure) |
A | Bag |
A | Monitor |
A | Mouse |
B | Mouse |
C | Monitor |
C | Bag |
Please note, i have tried to split it with delimiter from power query the column is not getting displayed if i open power query , please help me in splitting with delimiter and expand it in rows as shows above.
Hi @sonalisaha2310,
Sure, if you created a table or a column in DAX, you won't see it in Power Query.
Here is a way to create a new table with expanded rows via DAX:
I didn't remove temporary columns in order to let you see how this code works - you can get rid of them with the help of SELECTCOLUMNS.
Here is the same code as plain text:
Table3 =
VAR _tbl1 = ADDCOLUMNS ( ADDCOLUMNS ( Table2, "Accesories H", SUBSTITUTE ( [Accessories(Calculated Column Measure)], ",", "|" ) ),
"No of Items",
PATHLENGTH ( [Accesories H] ) )
VAR maxLen = MAXX ( _tbl1, [No of Items] )
VAR _tbl2 = FILTER ( CROSSJOIN ( GENERATESERIES ( 1, maxLen ), _tbl1 ), [No of Items] >= [Value] )
VAR _tbl3 = ADDCOLUMNS ( _tbl2, "Item", PATHITEM ( [Accesories H], [Value] ) )
RETURN _tbl3
The playground PBIX file is also attached.
Best Regards,
Alexander
Hi @sonalisaha2310,
Here is a way to solve it with DAX:
DAX code in plain text for convenience (see also the attached file):
Result =
VAR curRow = SUBSTITUTE ( [Overall], " ", "" )
VAR curRowH = SUBSTITUTE ( curRow, ",", "|" )
VAR nItems = PATHLENGTH ( curRowH )
VAR res = IF ( NOT ISBLANK ( nItems ),
VAR _tbl1 = SUMMARIZE ( ADDCOLUMNS ( GENERATESERIES ( 1, nItems ), "sub", PATHITEM ( curRowH, [Value] ) ), [sub] )
VAR _tbl2 = DISTINCT ( UNION ( _tbl1, { [sub1 Given], [sub2 Given] } ) )
VAR _tbl3 = FILTER ( _tbl2, NOT ISBLANK ( [sub] ) && [sub] <> [sub3 Return] )
RETURN CONCATENATEX ( _tbl3, [sub], "," ),
VAR _tbl4 = FILTER ( DISTINCT ( { [sub1 Given], [sub2 Given] } ), NOT ISBLANK ( [Value] ) && [Value] <> [sub3 Return] )
RETURN CONCATENATEX ( _tbl4, [Value], "," ) )
RETURN res
Best Regards,
Alexander
Hello @barritown , Thank you so much this worked as column measure , can you also please include the logic of splitting the text suing comma delimiter and expand it in the form of rows ?