Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Haydn_R
Helper I
Helper I

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

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

ProgramCount
Medical43
New Support59
All NRL;#Other77
Other94
Older Country;#Home and Parents;#Medical;#Work Services28
Payment Integrity89
Other37
Young and Learners;#Work Services:#Payment Integrity82

 

Desired Outcome

Progam UnqiueCount
All NRL77
Home and Parents28
Medical71
New Support59
Older Country28
Other208
Payment Integrity171
Work Services110
Young and Learners82

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Jihwan_Kim_0-1675745400460.png

 

 

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] )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Haydn_R
Helper I
Helper I

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

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Jihwan_Kim_0-1675745400460.png

 

 

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] )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.