Forum Discussion
Solved
Comma Separated Values
Hi all - I need to go from this: Report ID Examinations 57818 FCNRTJ 53863 ZANAE,IBIOPB,IBILXD,FPTCH 50320 FNVRK 31285 UFOOLJ 75614 UGMDCI 70578 UNECKN 47734 CLUN...
You can make use of PATHITEM and then some crossjoins.
-calculated table DAXSplit = VAR _Path = --add a pathitem column by substituting commas with pipes ADDCOLUMNS ( 'Table', "@PathItem", SUBSTITUTE ( 'Table'[Examinations], ",", "|" ) ) VAR _MaxLength = --get the overall max path length MAXX ( ADDCOLUMNS ( _Path, "@PathLength", PATHLENGTH ( [@PathItem] ) ), [@PathLength] ) VAR _Crossjoined = CROSSJOIN ( _Path, GENERATESERIES ( 1, _MaxLength, 1 ) ) VAR _ExamItem = FILTER ( ADDCOLUMNS ( _Crossjoined, "Exam Item", PATHITEM ( [@PathItem], [Value], TEXT ) ), NOT ( ISBLANK ( [Exam Item] ) ) ) RETURN SELECTCOLUMNS ( _ExamItem, "Report ID", [Report ID], "Examinations", [Examinations], [Exam Item] )Please see the attached sample pbix.
Hi Creative_tree88 ,
You can achieve the desired result by creating a new calculated table using the following DAX:
NewTable =
VAR Separator = "|"
RETURN
SELECTCOLUMNS(
GENERATE(
'YourOriginalTable',
VAR ExaminationsList = SUBSTITUTE('YourOriginalTable'[Examinations], ",", Separator)
RETURN
SELECTCOLUMNS(
GENERATESERIES(1, LEN(ExaminationsList) - LEN(SUBSTITUTE(ExaminationsList, Separator, "")) + 1),
"Report ID2", [Report ID],
"SplitValue", PATHITEM(ExaminationsList, [Value], TEXT)
)
),
"Report ID2", [Report ID2],
"SplitValue", [SplitValue]
)
Your output will look like this:
Bibiano_Geraldo that works well. Is there any way I can now bring the rest of the dataset into this new table (or somehow lookup from original table?), to effectively create a copy of the original data (with ALL the fields) but with these exams now sitting (correctly) on one row??
You can use this DAX:
NewTable = VAR Separator = "|" RETURN GENERATE( 'YourOriginalTable', VAR ExaminationsList = SUBSTITUTE('YourOriginalTable'[Examinations], ",", Separator) RETURN SELECTCOLUMNS( GENERATESERIES(1, LEN(ExaminationsList) - LEN(SUBSTITUTE(ExaminationsList, Separator, "")) + 1), "Examination", PATHITEM(ExaminationsList, [Value], TEXT) ) )Your output will look like this: