Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 l...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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.

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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