Hi I would like to refer in powerquery to a value from a previous column as part of a custom column but always return "null" for it, while my cells have data in them, here is my M code: let currentRow = [Index], prevRowValue = try [Column6]{currentRow - 1} otherwise null, // issue here, always returns null currentVal = [Column6] in if (prevRowValue = null and currentVal = null) then "ok" else null Any solution to resolve this? Thanks!

Hi medmbchr1989 I'm uploading a sample pbix for you to use as a reference. In it, I have two different alternate versions for you to choose from. medmbchr1989-previous-row.pbix Both versions work and you'll have to adapt to your specific needs.

Return value of previous column | Microsoft Fabric Community

8 Replies

PhilipTreacy
Super User
1 year ago
medmbchr1989

Please supply data otherwise we're working in the dark.

Regards

Phil

hnguy71

Super User

1 year ago

Hi medmbchr1989

Try this instead:

let 
currentRow = [Index],
prevRowValue = currentRow - 2, // assuming your index starts at 1
currentVal = try YOUR_PREVIOUS_STEP[Column6]{preRow} otherwise null
in 
currentVal

You're missing a reference to your previous step.

medmbchr1989

Helper I

1 year ago

Hi hnguy71

Not working unfortunatelly. I tried the following code but it consumes a lot of RAM and the file became heavy while loading data:

let
    currentRow = [Index],
    prevRowValue = try List.FirstN(List.Skip(#"Index ajouté"[Column6], currentRow - 1), 1){0} otherwise null,
    currentVal = [Column6]
in
    if (prevRowValue = null and currentVal = null) then "Bibronnage" else nulllet
    currentRow = [Index],
    prevRowValue = try List.FirstN(List.Skip(#"Index ajouté"[Column6], currentRow - 1), 1){0} otherwise null,
    currentVal = [Column6]
in
    if (prevRowValue = null and currentVal = null) then "ok" else null

hnguy71

Super User

1 year ago

Hi medmbchr1989 ,

What do you mean it didn't work? Copy and paste this sample query into your model:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWMgKyjSBMYyDTzBTMNAEyLUzATFOQqBGYaQZkmgBFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    PreviousValue = Table.AddColumn(ChangedType, "PreviousValue", each let 
curRow = [Index],
preRow = curRow - 2, // assuming your index starts at 1
value = try ChangedType[Value]{preRow} otherwise null
in 
value)
in
    PreviousValue

The expected output:

If you are worried about performance and optimization, perhaps thinking about doing this in DAX instead of PQ.

Forum Discussion

Return value of previous column