Return value of previous column

[medmbchr1989]

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!

[hnguy71]

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]

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]

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.

[medmbchr1989]

@hnguy71 yes your code is not working, still returning the same issue i had, check out my output, only one "ok" is correct, the rest shouldnt be as the value in the previous column6 value is not null, all results marked in yellow are not correct.

I can't work it in DAX as this is a used value in hundreds of tables already.

[hnguy71 · Accepted solution]

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.

[medmbchr1989]

@hnguy71 I have marked your answer as a solution however this solution doesn't allow data to refresh, too much time for refresh which doesn't end eventually, do you have a better suggestion to replace this function with something more manual?

[hnguy71]

Hi @medmbchr1989 

Transformations like this could be quite heavy while also adding bloat to your model. Typically this would be built as a DAX expression. I understand each person's requirements are always a bit different and unique and you previously stated that you can only manage this via PowerQuery and there could be some other things you can do to optimize it further.

One attempt would to be to use TABLE.BUFFER to commit the subset of info into memory once and then call the snapshot from there. 

An example would be to reference the previous step and then only select the two columns you require:

Then, you would buffer that result:

And then do the same as which version you chose previously. I've updated the same sample pbix to include a third version with the table buffer.

medmbchr1989-previous-row.pbix

Hope that helps!

[PhilipTreacy]

@medmbchr1989 

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

Regards

Phil