Split columns by DAX with varying length per row

[syuu]

Hi,

I have a table and one of the column have many values delimited by comma as below:

week	ID	Name
W401	002	abcde,abc,abcd,abcdef
W401	001	abcde,fgh
W402	001	ijk
W402	002	ijk,lmn,opeqrs

I want to split the name by comma, also keep the detail of them.
Length of name might be different, and the name is too much to use exhaustive way.

The output I expected as below:

week	ID	Name
W401	002	abcde
W401	002	abc
W401	002	abcd
W401	002	abcdef
W401	001	abcde
W401	001	fgh
W402	001	ijk
W402	002	ijk
W402	002	lmn
W402	002	lmn,opeqrs

I can use DAX only. (can't use power query)
Any answer will be helpful!

[bhanu_gautam · Accepted solution]

@syuu , Create a new calculated table by going into Modelling

SplitNamesTable =

ADDCOLUMNS(

    GENERATE(

        'OriginalTable',

        VAR NameList = SUBSTITUTE('OriginalTable'[Name], ",", "|")

        VAR NameCount = PATHLENGTH(NameList)

        RETURN

            SELECTCOLUMNS(

                GENERATESERIES(1, NameCount),

                "week", 'OriginalTable'[week],

                "ID", 'OriginalTable'[ID],

                "Name", PATHITEM(NameList, [Value], TEXT)

            )

    ),

    "Name", SUBSTITUTE([Name], "|", ",")

)

[syuu]

This is exactly what I need!! Thank you for your reply!!