Forum Discussion
Need help with this power query
Input data Expected output Sample data ID Date/Time (UTC) User Description Security Change Previous Value 2157902 15/11/2021 5:31 [email protected]...
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
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"