Forum Discussion
Reference Previous Value in adjacent column based on multiple criteria using variables
I'd like to stay away from EARLIER() since my table is large and that may create to many behind the scenes calculations. I'm hoping for a solution using variables.
Based on the below table, I'm looking to reference the Prior Priority based on the OS_Number. Prior Priority would be the OS_Version - 1. If Version is 1 the new column should show null or NA.
Yes, this can be done in Power Query using the function that I've described here: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
If your data is clustered by priority already, you can use the integrated group-function (as it uses the fast GroupKind.Local). You would call the function with the following parameters:
fnTableReferenceDifferentRow(YourTableName, -1, {"Priority"}, {"OS_Number"})Just replace "YourTableName" by the name of your table and "fnTablReferenceDifferentRow" by the name of your function, if yu have named it differently.
9 Replies
- Not sure how you are going to avoid using EARLIER unless you do it in Power Query maybe.
I came close with variables using the below based on another post. I'm just having trouble making sure there is a filter on OS_Number.
Prior Version Priority = VAR CurrentPriority = SELECTEDVALUE ( OPENSEAT_Hist[OS Priority] ) VAR VersionID = CALCULATE ( MIN ( OPENSEAT_Hist[OS_VERSION] ), OPENSEAT_Hist[OS Priority] = CurrentPriority ) VAR PriorPriority = CALCULATE ( VALUES ( OPENSEAT_Hist[OS Priority] ), OPENSEAT_Hist[OS_VERSION] = CurrentPriority - 1 ) RETURN PriorPriority- Reach out to ImkeF, she's the M genius. For some reason the @ tagging isn't working for me.
Hi,
This works
let Source = Excel.CurrentWorkbook(){[Name="Data1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prior Priority", type text}, {"OS_Number", Int64.Type}, {"OS_version", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Next value" = Table.AddColumn(#"Added Index", "custom column", each #"Added Index"{[Index]}[Prior Priority]), #"Added Custom" = Table.AddColumn(#"Next value", "Custom", each if [custom column]="NA" then [Prior Priority] else [custom column]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"custom column"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Priority"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Priority", "Prior Priority", "OS_Number", "OS_version", "Index"}), #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Index"}) in #"Removed Columns1"Hi there,
You can try this, I hope that understood what you are trying to achive.
New Column (Prior Priority) = IF( Table[OS_Version] > 1, VAR osNr = Table[OS_Number] VAR osVer = Table[OS_Version]-1 RETURN MAXX( FILTER( Table, Table[OS_Number] = osNr && Table[OS_Version] = osVer ), Table[Priority] ) )
If this works for you please LIKE and mark as solution.Regards,
Kristjan76
