Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

how to Reference previous row in same column

I have one column like this   City A A B C C   and my requirement is if previous(city)=city then 0 else city and first one should be  0   So output   city     Column A         0 A     ...
Need Help
Show and Tell
PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous  Please add an Index column in "Power Query Editor" and then add a new column in Data Pane as below

 

CheckPrev = 
VAR _PrevRow = LOOKUPVALUE(Test242PrevRow[City],Test242PrevRow[Index],Test242PrevRow[Index]-1)
RETURN IF(Test242PrevRow[City]=_PrevRow || Test242PrevRow[Index] = 1,"0",Test242PrevRow[City])

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Is it possible without adding column?

    • PattemManohar's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous  Don't think it's possible without having any supporting field to lookup on to retrieve next or previous row logic. 

       

      It's a dynamic solution and it will work irrespective of number of rows in your table....

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        AS my data source is AAS so it won't allow me to create column and it only allow me to create measure

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    and in real time scenario i don't know how many rows are there so please provide dynamic solution