Comma Separated Values

[Creative_tree88]

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	CLUNGB
30357	ULIVEB
52545	CBONEB
46729	IURRBS
68719	ICHOSD
32829	IGSJXG
59756	UHANRJ
78302	IASDRD,USPUCI
38219	FUREH
74123	UNECKN
38429	FINJTJ,FUPLR
56246	ZZSPICC
37045	IURDR,ZANAE,IURRRS
56017	ZCONS
52414	UDRAID
41634	INEPXG
80000	UGMDCI
55208	FLOLL,FINJTJ
76559	INEPXG
79946	FHIPR,FINTJ
32153	UANKRJ
52167	FJSHR
70130	IAVEME

to this:

Report ID	Examinations
57818	FCNRTJ
53863	ZANAE
53863	IBIOPB
53863	IBILXD
53863	FPTCH
31285	UFOOLJ
75614	UGMDCI
70578	UNECKN
47734	CLUNGB
30357	ULIVEB
52545	CBONEB
46729	IURRBS
68719	ICHOSD
32829	IGSJXG
59756	UHANRJ
78302	IASDRD
78302	USPUCI
38219	FUREH
74123	UNECKN
38429	FINJTJ
38429	FUPLR
56246	ZZSPICC
37045	IURDR
37045	ZANAE
37045	IURRRS
56017	ZCONS
52414	UDRAID
41634	INEPXG
80000	UGMDCI
55208	FLOLL
55208	FINJTJ
76559	INEPXG
79946	FHIPR
79946	FINTJ
32153	UANKRJ
52167	FJSHR
70130	IAVEME

Essentially splitting the examinations column into separate rows, using Report ID as identifier.  I cannont do this in Power Query, as the column I'm using is a calculated column based on a complicated Lookup, designed to fetch these values in the first instance.

I either need to somehow create a new table with these split values, or find a way to count each individual exam as at some point I'm going to need to present this on a chart (by month) to show just how many exams (individually) have occurred.

Your help is always appreciated!

Sample Data 

[Anonymous]

Hi @Creative_tree88 ,

Using Power Query makes it easy to accomplish your needs.

Final output

Best Regards

[danextian · Accepted solution]

Hi @Creative_tree88 

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.

[Kedar_Pande]

@Creative_tree88 

Create a new table:

Examinations_Split = 
VAR AddRows = 
ADDCOLUMNS (
'YourOriginalTable',
"Examination", 
PATHITEM('YourOriginalTable'[Examinations], 1)
)
RETURN
UNION(
SELECTCOLUMNS( AddRows, "Report ID", 'YourOriginalTable'[Report ID], "Examination", [Examination] ),
ADDCOLUMNS(
'YourOriginalTable',
"Examination", 
PATHITEM('YourOriginalTable'[Examinations], 2)
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

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:

[Creative_tree88]

@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??

[Bibiano_Geraldo]

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: