Forum Discussion

witbi's avatar
witbi
Icon for Helper I rankHelper I
5 years ago
Solved

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

  • AntrikshSharma's avatar
    AntrikshSharma
    5 years ago

    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

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      witbi
      Icon for Helper I rankHelper I
      5 years ago

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

  • kumar27's avatar
    kumar27
    Icon for Advocate V rankAdvocate V
    5 years ago

    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's avatar
      witbi
      Icon for Helper I rankHelper I
      5 years ago

      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?