Transform a column with a logic

[Anonymous]

Hello everyone! 

I have a problem that can be simplified as follows:

There is a table, with a column named "a" with values [1,0,1,0,0,0,1,0,0,1,0,1] and I want to create another column "b" that will be [1,2,1,2,3,4,1,2,3,1,2,1]. The logic to build that column is:

If the value of a in a certain position is 1, the value of b in the same position is 1.
Between two different ones, there is to index that succession as it is shown in the example.
I'd appreciate any help! Thanks.

[PattemManohar · Accepted solution]

@Anonymous  Please try the below steps:

Step 1 : In Power Query Editor, please add an Index Field and Call it as MainIndex

Step 2 : Split the Data column using "Split Column" option as below

Step 3 : Now again, add an another Index field and call it as SubIndex

Step 4 : Now back to Report view, add an additional column using DAX as below

CalcColumn = 
VAR _Prev1SubIndex = CALCULATE(MAX(Test230FunnySplit[SubIndex]),FILTER(ALL(Test230FunnySplit),Test230FunnySplit[Data]=1 && Test230FunnySplit[MainIndex] = EARLIER(Test230FunnySplit[MainIndex]) && Test230FunnySplit[SubIndex] < EARLIER(Test230FunnySplit[SubIndex])))
RETURN IF(Test230FunnySplit[Data]=1,Test230FunnySplit[Data],(Test230FunnySplit[SubIndex] - _Prev1SubIndex)+1)

Step 5 : We have achieved the desired output but in row format, need to combine these back. So create a New Table as below

Test230Out = SUMMARIZECOLUMNS(Test230FunnySplit[MainIndex],"Data",CONCATENATEX(Test230FunnySplit,Test230FunnySplit[Data],",",Test230FunnySplit[SubIndex])
                                                          ,"CalcColumn",CONCATENATEX(Test230FunnySplit,Test230FunnySplit[CalcColumn],",",Test230FunnySplit[SubIndex])
                             )

[Anonymous]

Amazing solution! Thank you very much.