Forum Discussion
Transform a column with a logic
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.
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]) )
2 Replies
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]) )