How to split a long column to make multiple columns

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.