Need help with splitting data

[ARP331AHS269]

Hello,

I have data in the format

INC

WKN

INC123456

06-Mar-2023 07:49:24 - FirstName1 Lastname1 (Work notes) Monitoring the server 06-Mar-2023 07:47:58 - FirstName2 Lastname2 (Work notes) logs has been shared with vendor 02-Mar-2023 15:13:07 - FirstName3 Lastname3 (Work notes) Waiting for the mass migration of VMs to be completed. 02-Mar-2023 13:30:25 - FirstName4 Lastname4 (Work notes) Server is now updating the OS patching. We will wait for this step to complete

I'll have to split this data into 

INC	Date-Time	Agent Name	Notes
INC123456	02-03-2023 13:30	FirstName1 Lastname1	Monitoring the server
INC123456	02-03-2023 15:13	FirstName2 Lastname2	logs has been shared with vendor
INC123456	06-03-2023 07:47	FirstName3 Lastname3	Waiting for the mass migration of VMs to be completed.
INC123456	06-03-2023 07:49	FirstName4 Lastname4	Server is now updating the OS patching. We will wait for this step to complete

Please advise on how to achieve this 

[ronrsnfld · Accepted solution]

If I understand your setup correctly:

this should work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBNT4NAEIb/yoSTJq2BXSi6VxMTE6mHJnJoOKxlChthl+yM5e+7JUIb7W0/Mu/zvLPfR9EqijfrQvu1iIWEOFfpkxIprOHFeOKt7jGBN01sp9Nd6fwXWMdI91G1muYLZw07b2wD3CIQ+hP6+fNveK6yx+twsYSLm+GdawhaTfCJaIFa7bGG0XALJ7S1u3DEhZNkKpEqzq85cuHI/5zX7XMiZJptQlCpDZ+rHJ2f6vSaCHrTeM3GWXBH+CgI2AUhOLh+6JCxfripIZWMlciuNdJFI71ZdzctDwyF9xG+h1rzvNf3HQyaD224L7gSwy66DsYg/WscRolxOBvOelFV/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INC = _t, WKN = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INC", type text}, {"WKN", type text}}),

//Replace INC space with nulls 
//  so can fill up/down
    #"Replace with nulls" = Table.ReplaceValue(
        #"Changed Type","",null,Replacer.ReplaceValue,{"INC"}),
    #"Filled Down" = Table.FillDown(#"Replace with nulls",{"INC"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"INC"}),

//Extract the date-time into a column
// then fill down and group
    #"Added Custom" = Table.AddColumn(#"Filled Up", "Date-Time", each try DateTime.From(Text.Start([WKN],20)) otherwise null, type datetime),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Date-Time"}),
    #"Grouped Rows" = Table.Group(#"Filled Down1", {"INC","Date-Time"}, {
    
    //extract Agent Name and Notes from each group
        {"Agent Name", each Text.Trim(Text.Middle(Splitter.SplitTextByDelimiter("(")([WKN]{0}){0},23)), type text},
        {"Notes", each Text.Combine(List.RemoveFirstN([WKN],1),"#(lf)"), type text}
        })
in
    #"Grouped Rows"

[bhanu_gautam]

@ARP331AHS269 , Try spliting data using deliemter in Power Query