Interesting problem - trying to get a value from the next row

Hi there!

I have a scheduled report that pulls in data from a CRM program that has to be cleaned so I can import into a task management platform.

The output from the CRM has one column that indicates :

For each "Project" the Internal ID is the same- but there can be several Services to that client within the project.

Each time the "Internal ID" changes, it reflects a different Customer and Project, but the way it is output, there is no entry in the "Item" (or what we use for the different Services).

So, what I have done so far in Power Query is to replace values in Col B so that there are no blanks (replaced by null) and where there was an asterisk in the original table, I replaced with "CatName" as below. I also added a custom field called "Category" in Col G which is where I have issues.

When I manually edit each table in excel, I use this formula for Category

=IF(AND(B2<>"null",C3=C2),F3,F3)

Essentially for the "Category" in G2, we want the "Item" from the next row F3 or "ServiceA," and so on as below

Now the issue is with the code I am using.

Here is the code

let
    Source = Excel.Workbook(File.Contents("C:\Users\Jeffrey\Internal\Internal Projects - Internal Projects TEST\CAPACITY_RESOURCE_FORECAST\Wrike_Capacity_DataFiles\OpenOppsforJeffreyResults857TEST.xlsx"), null, true),
    OpenOppsforJeffreyResults857_Sheet = Source{[Item="OpenOppsforJeffreyResults857",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(OpenOppsforJeffreyResults857_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Internal ID", Int64.Type}, {"*", type text}, {"Customer", type text}, {"Opportunity", type text}, {"Transaction Number", Int64.Type}, {"Item", type text}, {"Opportunity Created Date", type date}, {"Expected Close Date", type date}, {"Probability", type number}, {"Projected Total", type number}, {"Weighted Total", type number}, {"Memo", type text}, {"Estimated Project Duration", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"*", "Project"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index"," ",null,Replacer.ReplaceValue,{"Project"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","*","CatName",Replacer.ReplaceText,{"Project"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", try each if Text.Contains("Replaced Value1" [Index] [Project] = "CatName") then #"Replaced Value1" {[Index] + 1} [#"[Item]"] else #"Replaced Value1" {[Index]} [#"[Item]"] otherwise Null.Type, type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Internal ID", "Project", "Customer", "Opportunity", "Transaction Number", "Item", "Category", "Opportunity Created Date", "Expected Close Date", "Probability", "Projected Total", "Weighted Total", "Memo", "Estimated Project Duration", "Index"}),
    Category = #"Reordered Columns"{25}[Category]
in
    Category

The issue is with the line

#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Category", try each if Text.Contains("Replaced Value1" [Index] [Project] = "CatName") then #"Replaced Value1" {[Index] + 1} [#"[Item]"] else #"Replaced Value1" {[Index]} [#"[Item]"] otherwise Null.Type, type text),

While I don't get a Syntax error, I get this error

Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Replaced Value1
Key=Index

I have no idea where to start as I thought that the structure was ok, but apparently not....

Any help is greatly appreciated!!