Create new column. Current column has values separated by a semicolon and hashtag

[Haydn_R]

I current have a column called Program. The values are separate by a semicoln and hashtag. The first table below is a mock up of the data and the 2nd table is my desired result.  In the actual data there are 11 unqiue Programs that I will need to cater for.

Mocked up data

Program	Count
Medical	43
New Support	59
All NRL;#Other	77
Other	94
Older Country;#Home and Parents;#Medical;#Work Services	28
Payment Integrity	89
Other	37
Young and Learners;#Work Services:#Payment Integrity	82

Desired Outcome

Progam Unqiue	Count
All NRL	77
Home and Parents	28
Medical	71
New Support	59
Older Country	28
Other	208
Payment Integrity	171
Work Services	110
Young and Learners	82

[Haydn_R]

Thank you for you assistance, it is very much appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

Desired table = 
VAR _newtable =
    ADDCOLUMNS ( Data, "@new", SUBSTITUTE ( Data[Program], ";#", "|" ) )
VAR _separate =
    ADDCOLUMNS (
        _newtable,
        "@first", PATHITEM ( [@new], 1 ),
        "@second", PATHITEM ( [@new], 2 ),
        "@third", PATHITEM ( [@new], 3 ),
        "@fourth", PATHITEM ( [@new], 4 )
    )
VAR _finaltable =
    UNION (
        SUMMARIZE ( _separate, [@first], Data[Count] ),
        SUMMARIZE ( _separate, [@second], Data[Count] ),
        SUMMARIZE ( _separate, [@third], Data[Count] ),
        SUMMARIZE ( _separate, [@fourth], Data[Count] )
    )
RETURN
    GROUPBY (
        FILTER (
            SELECTCOLUMNS (
                _finaltable,
                "@Program unique", [@first],
                "@Count", Data[Count]
            ),
            [@Program unique] <> BLANK ()
        ),
        [@Program unique],
        "Count", SUMX ( CURRENTGROUP (), [@Count] )
    )