Power Query conversion from single columns to multiple columns based on rowname index

hi,@kvbe

    After my research, you can do these follow my steps as below:

Basic data

Step1:

Select column A and duplicate this column

and then select the duplicate column replace value

Step2:

duplicate the table

Step3:

Merge basic table with duplicate table

And then expand the table

Step4:

Add a conditional column

Step5:

filter custom column is 2

Step6:

Add the index column

Step7:

select Index column ,click Add Column->Standard->Modulo

Step8

filter column Modulo is 0

Step9:

select Column A and replace the value

Step10:

Remove unnecessary column and rename the column

Result:

here is demo, please try it.

https://www.dropbox.com/s/ablf38rvrhravgf/Power%20Query%20conversion%20from%20single%20columns%20to%20multiple%20columns%20based%20on%20rowname%20index.pbix?dl=0

Best Regards,

Lin