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

[witbi]

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

[AntrikshSharma]

@witbi  You're welcome!

[AntrikshSharma · Accepted solution]

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

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

[witbi]

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

[kumar27]

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:

[witbi]

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?