Add an index column based on a if formula

[BYENER]

Hi All,

Can someone help me with the following? I have to columns, column A with text value and column B the value of previous row of column A. Now I want to add another column (index) and will start with 1 and after if column A and column B are the same the value has to stay 1 else it have to be + 1.

For example

Column A	Column B	Index
A	Blank	1
A	A	1
A	A	1
B	A	2
B	B	2
A	B	3
A	A	3
A	A	3
C	A	4
C	C	4
C	C	4
	C

Thanks in advance.

[v-eqin-msft]

Hi @BYENER ,

Did I answer your question? Please mark my reply as solution, thank you~

Best regards,

Eyelyn Qin

[BYENER]

Hi @v-eqin-msft ,

My apologies, I didn't saw your message. Thank you for your solution. This is exactly what I want, but you asked me to put the formula in the query editor. Do you mean DAX or advanced editor in Power Query? I want this in Powery Query because after this I want to group by Index.

Kind regards,

B. Yener

[v-eqin-msft]

Hi @BYENER ,

Sorry for the confusion. I mean to add a Index column in Query Editor.

The DAX formula is used out of Query Editor.

Best regards,

Eyelyn Qin

[BYENER]

.

[BYENER]

@v-eqin-msftformula is not working. Please see attached pbix file.

text pbix 

[BYENER]

@v-eqin-msft  did you opened the test pbix?

[BYENER]

Can nobody help me?

[v-eqin-msft]

Hi @BYENER ,

According to my understanding, you want to accumulate based on index,  right?

You could use the following formula after add a Index column in Query Editor:

flag =
VAR _last =
    LOOKUPVALUE (
        'IndexTable'[Column A],
        'IndexTable'[Original Index], SELECTEDVALUE ( 'IndexTable'[Original Index] ) - 1
    )
RETURN
    IF (
        SELECTEDVALUE ( 'IndexTable'[Original Index] ) = 1,
        1,
        IF ( SELECTEDVALUE ( IndexTable[Column A] ) = _last, 0, 1 )
)

Accumulate =
SUMX (
    FILTER (
        ALL ( IndexTable ),
        IndexTable[Original Index] <= MAX ( IndexTable[Original Index] )
    ),
    [flag]
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[amitchandak]

@BYENER , refer to if the file attached after signature can help.

You can merge two columns into one

[BYENER]

Yes, that's exactly what I did Index and Index 1 , but you added the new column in DAX. I want to add in Power Query, because I want to use the group by function.