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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Split columns by DAX with varying length per row

Hi,

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

weekIDName
W401002abcde,abc,abcd,abcdef
W401001abcde,fgh
W402001ijk
W402002

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:

weekIDName
W401002abcde
W401002abc
W401002abcd
W401002abcdef
W401001abcde
W401001fgh
W402001ijk
W402002ijk
W402002lmn
W402002lmn,opeqrs


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

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@Anonymous , 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], "|", ",")
)



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

2 REPLIES 2
bhanu_gautam
Super User
Super User

@Anonymous , 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], "|", ",")
)



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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