Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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!
Solved! Go to Solution.
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.
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.
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:
If you are worried about performance and optimization, perhaps thinking about doing this in DAX instead of PQ.
@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.
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.
@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?
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!
Please supply data otherwise we're working in the dark.
Regards
Phil
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
141 | |
109 | |
69 | |
55 |