Previous and next value by category, specific type and date

[Kargap7]

Hello,

I want to have two columns: one for finding previous "REIS" value and other for next "REIS" value for those rows whose type is not "REIS" and when there are different categories.

Let's say category 12B have four "REIS" on the 9th of May. Each row after "REIS" by date should return me the previous or next row value with "REIS" type. 

Idk if the explanation is clear, the attached picture shows what result I need in Previous and Next columns.
Please help.

Category	Type	Number	Date	Previous	Next
12B	RYT	45B	2023-05-09 07:11		65T
12B	REIS	65T	2023-05-09 07:11
12B	PAS	56T	2023-05-09 07:56	65T	56C
12B	PAS	35G	2023-05-09 08:24	65T	56C
12B	REIS	56C	2023-05-09 08:42
12B	PAS	43V	2023-05-09 11:45	56C	78K
12B	REIS	78K	2023-05-09 13:04
12B	PAS	52B	2023-05-09 13:04	78K	23S
12B	PAS	56H	2023-05-09 13:05	78K	23S
12B	PAS	45P	2023-05-09 16:42	78K	23S
12B	KUR	89L	2023-05-09 19:52	78K	23S
12B	REIS	23S	2023-05-09 20:03
12F	PAS	56H	2023-05-09 07:56		89L
12F	PAS	45P	2023-05-09 08:24		89L
12F	REIS	89L	2023-05-09 08:42
12F	PAB	23S	2023-05-09 16:42	89L	65W
12F	REIS	65W	2023-05-09 13:05
12F	PAS	56H	2023-05-09 13:05	65W
12F	PAS	45P	2023-05-09 16:42	65W
12F	KUR	89L	2023-05-09 20:03	65W

[Ahmedx]

OK,  pls again try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHBDoIwEER/xfRsSLvtLtCbGKsGDwRQYwj//xtCJNp2KfGy7eFldmZ2GISCSuxF++qnaXD+yzKTmIEEvcutUmLcf6nTtZsewn4Taw4zhcQoJEZpPIdUYcHwlUjHGDPAxIx+hJRS1iBXy4s64rSVhieA6i+MLhxDbg6bCKMwQ31vp1mUtwgrLQLPALoLOZBW6oVzaXPeFVzam3cF99vJvHlX+IhVa878oJ4a4TPd20aCdWyzXpeud6ltfAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Type = _t, Number = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Type", type text}, {"Number", type text}, {"Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Count", 
    (x)=>  Table.ReplaceValue(
   Table.FillDown(
     Table.AddColumn(
        Table.AddIndexColumn(x,"Index",1,1),"Previous", each if [Type] ="REIS" then [Number] else null),
       {"Previous"}) ,each [Previous] ,each if [Type] = "REIS" then null else [Previous]  ,Replacer.ReplaceValue,{"Type", "Previous"})
}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Type", "Number", "Date", "Index", "Previous"}, {"Type", "Number", "Date", "Index", "Previous"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Category"}, 
   {{"Count", 
    (x)=> 
Table.ReplaceValue(
      Table.FillUp(
       Table.ReplaceValue(
        Table.FillUp(Table.DuplicateColumn(x, "Previous", "Next"),{"Next"}),
       each [Next] ,each if [Type] <> "REIS" then null else [Next]  ,Replacer.ReplaceValue,{"Type", "Next"}),
       {"Next"}),each [Next] ,each if [Type] = "REIS" then null else [Next]  ,Replacer.ReplaceValue,{"Type", "Next"})
}}),
    #"Removed Other Columns" = Table.Combine( Table.SelectColumns(#"Grouped Rows1",{"Count"})[Count])
in
    #"Removed Other Columns"

[Kargap7]

Still have issues.. @Ahmedx , first formula almost works with "Next" ones, and second formula almost works with "Previous" ones. 
I attach a picture below of what is wrong in both formulas. It is important to pay attention to the date, category and "REIS".

@Ahmedx could you please check it again?

[Ahmedx]

plse try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHBDoIwEER/xfRsSLvtLtCbGKsGDwRQYwj//xtCJNp2KfGy7eFldmZ2GISCSuxF++qnaXD+yzKTmIEEvcutUmLcf6nTtZsewn4Taw4zhcQoJEZpPIdUYcHwlUjHGDPAxIx+hJRS1iBXy4s64rSVhieA6i+MLhxDbg6bCKMwQ31vp1mUtwgrLQLPALoLOZBW6oVzaXPeFVzam3cF99vJvHlX+IhVa878oJ4a4TPd20aCdWyzXpeud6ltfAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Type = _t, Number = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Type", type text}, {"Number", type text}, {"Date", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "step1", each if [Type] ="REIS" then [Number] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"step1"}),
    from1 = Table.AddColumn(#"Filled Down", "Previous", each if [Type] = "REIS" then null else [step1]),
    #"Removed Columns" = Table.RemoveColumns(from1,{"step1"}),
    from2 = Table.AddColumn(#"Removed Columns", "N", each [Previous]),
    from3 = Table.FillUp(from2,{"N"}),
    #"Added Custom1" = Table.AddColumn(from3, "Custom", each [ t = 
Text.Combine(
List.Range( from3[N] &{null},[Index],1)),
to= if [Type] = "REIS" then null else if [Previous] =null or [N]<> t then t else null][to]),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Up", "Next", each if [Type] = "REIS" then null else [Custom]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Index", "N", "Custom"})
in
    #"Removed Columns1"

[Kargap7]

Hi @Ahmedx  thank you so much for your response.
It almost works, it just ignores Category. It should return values from the same Category, could you please help with that?