Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RWL01XOM
New Member

Is it possible to have a custom column reference the values of previous rows for that same column?

I'm cleaning up some data that does not have a grouping key but is grouped in order. Using the sample table below, I want to do the following for column C:

  • Iterate through each row
  • If A = 1 Then C = 1
  • ElseIf B <> aaa Then C = The previous row's value of C
  • ElseIf B = aaa Then C = The previous row's value of C + 1

Question:

Can a custom column reference the previous row's value of that column? From my experience, it looks like this is not possible.

 

RWL01XOM_0-1645627051667.png

 

2 REPLIES 2
Anonymous
Not applicable

I find the easiest way to do this on non SQL sources is to add two index columns, one starting at 1 and the other at zero, and then a self left join on the first and then second index. Then you can just do same row logic.

 

--Nate 

RWL01XOM
New Member

This doesn't answer my exact question but it is a work around for anbody who comes across this issue in the future.

 

let
  MaxInd = (x) => List.Sum(Table.SelectRows(G1_Col, each [A] <= x) [G1]), 
  Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    G1_Col = Table.AddColumn(Source, "G1", each if [A] = 1 then 1 else if [B] = "aaa" then 1 else 0),
    G2_Col = Table.AddColumn(G1_Col, "G2", each MaxInd([A]))
in
    G2_Col

 

I basically marked the begining of all the groupings with one column G1 and then summed all of the previous rows of G1 in G2. This got me to where I needed but I still would like to be able to reference previous rows of the same column if possible.

 

RWL01XOM_0-1645630582120.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.