cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculated column with comma delimited text split into rows in a new table

I have a calculated column called 'Tasks' in 'Table1' that is delimited by commas which I would like to split into separate rows in a new table called 'Table2'. As 'Tasks' is a calculated column I am not able to use split column in Power Query, in any case I would like a separate table.

Any help most appreciated. Many thanks

 

Table1

Id Event            Names                 Tasks

1  Halloween    Bob, Kate             invitations, emails, catering

2  Christmas     Kate, Pete, Tom   invitations, catering, decorations, carols, 

Table2

Id Event            Names                 Tasks

1  Halloween    Bob, Kate             invitations
1  Halloween    Bob, Kate             emails
1  Halloween    Bob, Kate             catering

2  Christmas     Kate, Pete, Tom   invitations
2  Christmas     Kate, Pete, Tom   catering
2  Christmas     Kate, Pete, Tom   decorations
2  Christmas     Kate, Pete, Tom   carols

1 ACCEPTED SOLUTION
Super User II
Super User II

@witbi Try this, also PBI file is attached below my signature:

1.PNG

Table =
VAR WordCount =
    ADDCOLUMNS (
        witbi,
        "@Word Count",
            LEN ( witbi[Tasks] ) - LEN ( SUBSTITUTE ( witbi[Tasks], ",", "" ) ) + 1
    )
VAR MaxWordCount =
    MAXX ( WordCount, [@Word Count] )
VAR TempTable =
    FILTER (
        GENERATE ( WordCount, GENERATESERIES ( 1, MaxWordCount, 1 ) ),
        [Value] <= [@Word Count]
    )
VAR SplitTextByNumber =
    ADDCOLUMNS (
        TempTable,
        "Final String", TRIM ( PATHITEM ( SUBSTITUTE ( witbi[Tasks], ",", "|" ), [Value] ) )
    )
VAR Result =
    SELECTCOLUMNS (
        SplitTextByNumber,
        "ID", [ID],
        "Event", [Event],
        "Tasks", [Final String]
    )
RETURN
    Result

Thank you,
Antriksh Sharma

View solution in original post

5 REPLIES 5
Super User II
Super User II

@witbi  You're welcome!


Thank you,
Antriksh Sharma
Super User II
Super User II

@witbi Try this, also PBI file is attached below my signature:

1.PNG

Table =
VAR WordCount =
    ADDCOLUMNS (
        witbi,
        "@Word Count",
            LEN ( witbi[Tasks] ) - LEN ( SUBSTITUTE ( witbi[Tasks], ",", "" ) ) + 1
    )
VAR MaxWordCount =
    MAXX ( WordCount, [@Word Count] )
VAR TempTable =
    FILTER (
        GENERATE ( WordCount, GENERATESERIES ( 1, MaxWordCount, 1 ) ),
        [Value] <= [@Word Count]
    )
VAR SplitTextByNumber =
    ADDCOLUMNS (
        TempTable,
        "Final String", TRIM ( PATHITEM ( SUBSTITUTE ( witbi[Tasks], ",", "|" ), [Value] ) )
    )
VAR Result =
    SELECTCOLUMNS (
        SplitTextByNumber,
        "ID", [ID],
        "Event", [Event],
        "Tasks", [Final String]
    )
RETURN
    Result

Thank you,
Antriksh Sharma

View solution in original post

Many thanks Antriksh. Appreciate the quick reply and sample. This does what I was looking for! 

Advocate II
Advocate II

You can use Split comma by delimeter to split the values into separate rows.

 

Click Edit queries to open Power Query Editor, select the column you want to split and click Split Column -> By Delimeter: Make sure the delimiter is correct, and Rows is selected in Split into:Capture.JPG

 

Thanks kumar27

 

I am familiar with the Power Query split column function, however as the table column is calculated this doesn't seem to be a solution I can use. Any other thoughts?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.