Forum Discussion

BYENER's avatar
BYENER
Icon for Helper V rankHelper V
6 years ago

Add an index column based on a if formula

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 AColumn BIndex
ABlank1
AA1
AA1
BA2
BB2
AB3
AA3
AA3
CA4
CC4
CC4
 C 

 

Thanks in advance.

Need Help

10 Replies

    • BYENER's avatar
      BYENER
      Icon for Helper V rankHelper V
      6 years ago

      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.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi BYENER ,

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

     

    Best regards,

    Eyelyn Qin

     

    • BYENER's avatar
      BYENER
      Icon for Helper V rankHelper V
      6 years ago

      Hi Anonymous ,

       

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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