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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
medmbchr1989
Helper I
Helper I

Return value of previous column

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!

1 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.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

8 REPLIES 8
hnguy71
Memorable Member
Memorable Member

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.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

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:

hnguy71_1-1731171823620.png

 

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

@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.

 

medmbchr1989_0-1731172371764.png

 

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.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

@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?

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:

hnguy71_0-1731194199346.png


Then, you would buffer that result:

hnguy71_1-1731194220722.png

 

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!

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
PhilipTreacy
Super User
Super User

@medmbchr1989 

 

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

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.