Thanks @dufoq3
This step is pulling null in the "GroupHelper" column and because of that, description gets combined for all those lines. I have attached screenshot of those lines. Thanks!
Table.AddColumn(#"Filtered Rows", "GroupHelper", each if (try Text.Range([Description], 2, 1) otherwise null) = "-" then [Index] else null, type text)
Hi @vineshparekh,
check whether this is what you need.
Result (top 20 rows)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVdbb4IwFP4rJz6PpIIU2NMUp0vmhWiWJTN9aLAqGYKpuN8/hCm4S7ScJnuhqfR837l9p7hYtLoDw6NjMKAbyZXkWwEehXGaZBuYZ/IQZgfJY/A3InzPDy3mk3uwbZu12F1h2/Prpj2+j8K/D5vk+eK4Se4IITDwr1ONhsb0Kf/1vPJkGSVrGAouYSTWMP0QcsMP8Ymr7ZkXUeX7W8PixfNxYgSz/N15TdZRImBwiGMIhFylcsuTUMBM7LNU8ixKk8IucFzL7FjsBDIKSpByLUFG+WYmdjEPxVYkmQY+1TRVQVMGhp9KKcIjJKxkuoWdkFG6BNI2+iLMy2ZakKVgVVtMllGEN/TbER++ERTmN5OoCwIVUxNZHAkL+4asQdkf1fryozWCV99465M2sS4TWgAocH3BKUihbGnKmsVW8SH09FsTqfEXOPlIUQn8/wI+Oto04EaKcRhmhjioO8VRub4chh/PtqsDxFNx22XNa+OiauOiaqMlUzpqRknzDNI2JoM16wYZrFlfb5PTYVSmnI4OENxA8BDt7qGYqYoqKcJNitIV1VCjjg6Q2v8BdV0RlK4ISldERVdER7p1jDEHNUzU7hxPh7+mDpD8k/Whxdgn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Ad_GroupHelper = Table.AddColumn(AddedIndex, "GroupHelper", each if (try Text.Range([Description], 2, 1) otherwise null) = "-" then [Index] else null, type text),
FilledDown = Table.FillDown(Ad_GroupHelper,{"GroupHelper"}),
GroupedRows = Table.Group(FilledDown, {"GroupHelper"}, {{"Description", each Text.Combine([Description], " "), type text}}),
ExtractedTextBeforeDelimiter = Table.TransformColumns(GroupedRows, {{"Description", each
[ a = Text.PositionOf(_, "]", Occurrence.First) +1,
b = Text.Range(_, 0, if a > 0 then a else null )
][b], type text}}),
TrimmedText = Table.TransformColumns(ExtractedTextBeforeDelimiter,{{"Description", Text.Trim, type text}}),
Ad_SN = Table.AddColumn(TrimmedText, "SN", each
[ a = Text.PositionOf([Description], "[SN") +1,
b = Text.PositionOf([Description], " ", Occurrence.Last),
c = Text.Range([Description], if a > 0 then a else if Text.Contains([Description], "]") then b else Text.Length([Description]))
][c], type text),
CleanDescription = Table.ReplaceValue(Ad_SN,
each [SN],
null,
(x,y,z)=> if y = "" then x else Text.TrimEnd(Text.Replace(x, y, ""), {"[", " ", "-"}),
{"Description"}),
CleanSN = Table.ReplaceValue(CleanDescription,
null,
null,
(x,y,z)=> if x = "" then null else Text.Replace(Text.Trim(x, {" ", "]"}), "SN: ", ""),
{"SN"}),
RemovedGroupHelper = Table.RemoveColumns(CleanSN,{"GroupHelper"})
in
RemovedGroupHelper
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
