How to split a long column to make multiple columns

[Naz]

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.

[v-frfei-msft · Accepted solution]

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
            )
        )
    )

For more details, please check the pbix as attached.

[Naz]

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]