Repeat Switching/Changing Values in a Column

Hi Vijay

We are getting very close here. However i do find some weird results when following your example. This is my full query from advanced editor:

let
Source = Excel.CurrentWorkbook(){[Name="Project_Transactions"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee No", Int64.Type}, {"Employee Name", type text}, {"Account Date", type datetime}, {"Activity Sequence", Int64.Type}, {"Activity Short Name", Int64.Type}, {"Country", type text}, {"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Description", type text}, {"Transferred To Payroll", type logical}, {"Payroll ID", type any}, {"Sub Project Name", type text}, {"Activity ID", Int64.Type}, {"Activity Description", type text}, {"Report Code", type text}, {"Report Code Name", type text}, {"Sub Project ID", Int64.Type}, {"Report Code Type", type text}, {"Price Adjustment ID", type any}, {"Price Adjustment Name", type any}, {"Internal Quantity", type number}, {"Internal Price", Int64.Type}, {"Overhead Price", Int64.Type}, {"Total Internal Price", Int64.Type}, {"Alternative Cost", Int64.Type}, {"Internal Amount", type number}, {"Sales Quantity", type number}, {"Currency Code", type text}, {"Currency Type", Int64.Type}, {"Sales Price Basis", Int64.Type}, {"Markup Price", Int64.Type}, {"Sales Price", type any}, {"Sales Price In Accounting Currency", Int64.Type}, {"Sales Amount Basis", type number}, {"Markup Amount", Int64.Type}, {"Sales Amount", type number}, {"Sales Amount In Accounting Currency", type number}, {"Internal Comments", type text}, {"Invoice Comments", type any}, {"Transaction Source", type text}, {"Invoicability", type text}, {"Invoice ID", Int64.Type}, {"Invoice No", Int64.Type}, {"Invoice Status", type text}, {"Organization Code", Int64.Type}, {"Create Cost Accounting", type text}, {"Approved", Int64.Type}, {"Cost Accounting Voucher Created", Int64.Type}, {"Correction Status", type text}, {"Report Code Group ID", Int64.Type}, {"Report Code Group Name", type text}, {"Day Confirmed", Int64.Type}, {"Billing Category", type any}, {"Billing Category Description", type any}, {"Customer", Int64.Type}, {"Customer Name", type text}, {"Created By", type text}, {"Redistributed By", type any}, {"Redistributed By Name", type any}, {"Registered By", type text}, {"Registered By Name", type text}, {"Jinsui Enabled", type logical}, {"Delivery Type Description", type any}, {"Delivery Type", type any}, {"Resource ID", type any}, {"Resource Description", type any}, {"Program ID", type text}, {"Program Name", type text}, {"Multi-Company Transaction Type", type any}, {"Revenue Accounting Voucher Created", Int64.Type}, {"Customer Transaction Approved", Int64.Type}, {"Multi-Company Reporting Company ID", type any}, {"Employee Invoice No", type any}, {"Project Transaction Seq", Int64.Type}, {"Approved For Payroll", type logical}, {"Created By Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Report Code identifier", each Text.End([Report Code], 2)),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Report Code identifier", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Report Code identifier] = "30")),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Employee Name", Order.Ascending}, {"Account Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Duplicate_Trans ud/hjem", {"Index"}, "Duplicate_Project_Transactions", JoinKind.LeftOuter),
#"Expanded Duplicate_Project_Transactions" = Table.ExpandTableColumn(#"Merged Queries", "Duplicate_Project_Transactions", {"Employee No"}, {"Duplicate_Project_Transactions.Employee No"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Duplicate_Project_Transactions",{{"Duplicate_Project_Transactions.Employee No", "Emp No Prev Row"}}),
#"Sorted Rows2" = Table.Sort(#"Renamed Columns",{{"Index", Order.Ascending}}),
#"Merged Queries1" = Table.NestedJoin(#"Sorted Rows2", {"Index.1"}, #"Duplicate_Trans ud/hjem", {"Index.1"}, "Duplicate_Project_Transactions", JoinKind.LeftOuter),
#"Expanded Duplicate_Project_Transactions1" = Table.ExpandTableColumn(#"Merged Queries1", "Duplicate_Project_Transactions", {"Employee No"}, {"Duplicate_Project_Transactions.Employee No"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Duplicate_Project_Transactions1",{{"Duplicate_Project_Transactions.Employee No", "Emp No Next Row"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Transport Out/Home", each if [Employee No] <> [Emp No Prev Row] then "Out" else if [Employee No] <> [Emp No Next Row] then "Home" else null),
#"Added Index2" = Table.AddIndexColumn(#"Added Custom1", "Index.2", 1, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index2",each [#"Transport Out/Home"],each if [#"Transport Out/Home"]<>null then [#"Transport Out/Home"] else (if #"Added Index2"[#"Transport Out/Home"]{0} ="Out" then {"Home","Out"} else {"Out","Home"}){Number.Mod([Index.2],2)},Replacer.ReplaceValue,{"Transport Out/Home"})
in
#"Replaced Value"