Need help with this power query

[Anonymous]

Input data 

Expected output 

Sample data 

ID	Date/Time (UTC)	User	Description	Security Change	Previous Value
2157902	15/11/2021 5:31	Testuser@test.com	Begin sync revision
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
2157903	15/11/2021 5:31	Testuser@test.com	Change Line Item
	15/11/2021 5:31	Testuser@test.com	Revision Synced		20211115_01
2157906	15/11/2021 5:31	Testuser@test.com	Sync revision completed
2157908	15/11/2021 5:39	Testuser@test.com	Import
2157909	15/11/2021 5:39	Testuser@test.com	Item Included in Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157909	15/11/2021 5:39	Testuser@test.com	Item Removed from Subset
2157956	15/11/2021 22:49	Testuser@test.com	Begin sync revision		20211116_01
2157957	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157957	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157957	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157957	15/11/2021 22:49	Testuser@test.com	Change Import
2157958	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157958	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157958	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157958	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157959	15/11/2021 22:49	Testuser@test.com	Change Line Item
2157959	15/11/2021 22:49	Testuser@test.com	Change Line Item
	15/11/2021 22:49	Testuser@test.com	Revision Synced
2157960	15/11/2021 22:49	Testuser@test.com	Sync revision completed

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dZNi8IwEAbgvzL0LNrETbWeRE+FPVlvIou2s92CSaRJhf33O0pd6kcxBxHRFgplIA/TMnmbxcLjTAxCn3sdj4keYz3ucwZi1GdUmaOxpcFibOmhm2hJtQlmuQLzqxIocJebXCuqwuFedo5e39mb/qxUhvCZK4TIomyxZ8LAmZlVwwAxjQamlbJfRZf48lmtucBZjetzBlTbbtD+6/UXHl6YYYMZya0u7BUidCfoO0Gkkk2ZYgq0H+JybfAO5gyl3hH5XWjZoi3aog9BxVkicT76aFKbf4BV2AUnYScG7vTtXH8xrSmKxfCujb2PFj6lBu7O9WNEranAd8dunh6Wfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Date/Time (UTC)" = _t, User = _t, Description = _t, #"Security Change" = _t, #"Previous Value" = _t]),

    Grouped = Table.Group(Source, "Description", {"grp", each let idxed = Table.AddIndexColumn(_, "Index", 0,1), id = List.Select([Previous Value], each Text.Trim(_)<>""){0}, completion = List.PositionOf([Description], "Sync revision completed") in Table.AddColumn(idxed, "status", each if [Index] <= completion then "Part of Sync|"&id else "Non Revision sync")}, 0, (x,y) => Byte.From(y="Begin sync revision")),
    #"Expanded grp" = Table.ExpandTableColumn(Table.RemoveColumns(Grouped, "Description"), "grp", {"ID", "Date/Time (UTC)", "User", "Description", "Security Change", "Previous Value", "status"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded grp", "status", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Type", "Sync id"})
in
    #"Split Column by Delimiter"

[Jakinta · Accepted solution]

There should be more elegant solution, but until somebody provides it, you can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dZNi8IwEAbgvzL0LNrETbWeRE+FPVlvIou2s92CSaRJhf33O0pd6kcxBxHRFgplIA/TMnmbxcLjTAxCn3sdj4keYz3ucwZi1GdUmaOxpcFibOmhm2hJtQlmuQLzqxIocJebXCuqwuFedo5e39mb/qxUhvCZK4TIomyxZ8LAmZlVwwAxjQamlbJfRZf48lmtucBZjetzBlTbbtD+6/UXHl6YYYMZya0u7BUidCfoO0Gkkk2ZYgq0H+JybfAO5gyl3hH5XWjZoi3aog9BxVkicT76aFKbf4BV2AUnYScG7vTtXH8xrSmKxfCujb2PFj6lBu7O9WNEranAd8dunh6Wfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Date/Time (UTC)" = _t, User = _t, Description = _t, #"Security Change" = _t, #"Previous Value" = _t]),
    Custom1 = List.Transform(Source[Description], each if Text.Contains(_, "sync revision", Comparer.OrdinalIgnoreCase) then "x" else null),
    Custom2 = List.Transform ( List.Split( List.PositionOf( Custom1, "x", Occurrence.All), 2), each List.Repeat({"x"},_{1}-_{0}+1)),
    Custom3 = List.Transform ( List.Split( List.RemoveLastN(List.RemoveFirstN(List.PositionOf( Custom1, "x", Occurrence.All), 1),1), 2), each List.Repeat({"y"},_{1}-_{0}-1)),
    HelperColumn = List.Accumulate ( List.RemoveNulls(List.Union(List.Zip({Custom2,Custom3})) ), {}, (s,c)=> s&c ),
    NewTable = Table.FromColumns( Table.ToColumns(Source) & {HelperColumn}  , Table.ColumnNames(Source)&{"Helper"}),
    Grouped = Table.Group(NewTable, {"Helper"}, {{"A", each let t=_ in Table.AddColumn ( Table.AddColumn(_, "Type Of Change", each if [Helper]="x" then "Part of Sync" else "Non Revision sync" ), "Sync ID", each List.Max (t[#"Previous Value"])), type table}}, GroupKind.Local),
    #"Expanded A" = Table.ExpandTableColumn(Grouped, "A", Table.ColumnNames(Source)&{"Type Of Change", "Sync ID"}),
    FINAL = Table.RemoveColumns(#"Expanded A",{"Helper"})
in
    FINAL