Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago
Solved

Comma Separated Values

Hi all - I need to go from this:

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

 

to this:

 

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

 

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 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

     

6 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
      Creative_tree88
      Helper V
      1 year ago

      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's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        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:

         

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Creative_tree88 ,

    Using Power Query makes it easy to accomplish your needs.

    Final output

     

    Best Regards