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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Naz
Regular Visitor

How to split a long column to make multiple columns

Hi, I have a long list of patients that I want to split into multiple columns. I can export the list to excel and do it manually but there must be a way in powerbi. Thank you so much for your help.

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Naz ,

 

I have created a sample for your reference. Please check the following steps as below.

 

1. Insert an index column in power query for the fact table.

2. Then created a calculatted column based on that.

index 2 = 
VAR inde =
    INT ( 'Table'[Index] / 10 )
RETURN
    IF ( 'Table'[Index] < 10, 'Table'[Index], 'Table'[Index] - inde * 10 )

 

3. Then we can get the excepted table by the formula.

Table 3 = 
VAR gen =
    GENERATESERIES ( 0, 9, 1 )
RETURN
    ADDCOLUMNS (
        gen,
        "1",
        VAR minindex =
            CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[index 2] = 0 ) )
        RETURN
            CALCULATE (
                MAX ( 'Table'[Patient] ),
                FILTER ( 'Table', 'Table'[index 2] = [Value] && 'Table'[Index] <= minindex )
            ),
        "2", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 20
                    && 'Table'[Index] > 10
            )
        ),
        "3", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 30
                    && 'Table'[Index] > 20
            )
        ),
        "4", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 40
                    && 'Table'[Index] > 30
            )
        )
    )

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @Naz ,

 

I have created a sample for your reference. Please check the following steps as below.

 

1. Insert an index column in power query for the fact table.

2. Then created a calculatted column based on that.

index 2 = 
VAR inde =
    INT ( 'Table'[Index] / 10 )
RETURN
    IF ( 'Table'[Index] < 10, 'Table'[Index], 'Table'[Index] - inde * 10 )

 

3. Then we can get the excepted table by the formula.

Table 3 = 
VAR gen =
    GENERATESERIES ( 0, 9, 1 )
RETURN
    ADDCOLUMNS (
        gen,
        "1",
        VAR minindex =
            CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[index 2] = 0 ) )
        RETURN
            CALCULATE (
                MAX ( 'Table'[Patient] ),
                FILTER ( 'Table', 'Table'[index 2] = [Value] && 'Table'[Index] <= minindex )
            ),
        "2", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 20
                    && 'Table'[Index] > 10
            )
        ),
        "3", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 30
                    && 'Table'[Index] > 20
            )
        ),
        "4", CALCULATE (
            MAX ( 'Table'[Patient] ),
            FILTER (
                'Table',
                'Table'[index 2] = [Value]
                    && 'Table'[Index] <= 40
                    && 'Table'[Index] > 30
            )
        )
    )

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Very smart!! How come a simple job like this has to be done in such a complicated way? Unbelievable!! Thank you so much for the codes.

Naz
Regular Visitor

Split column (powerbi).png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.