Forum Discussion

ARP331AHS269's avatar
ARP331AHS269
Frequent Visitor
1 year ago
Solved

Need help with splitting data

Hello,

 

I have data in the format

 

INCWKN
INC12345606-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 

 

INCDate-TimeAgent NameNotes
INC12345602-03-2023 13:30FirstName1 Lastname1Monitoring the server
INC12345602-03-2023 15:13FirstName2 Lastname2logs has been shared with vendor
INC12345606-03-2023 07:47FirstName3 Lastname3Waiting for the mass migration of VMs to be completed.
INC12345606-03-2023 07:49FirstName4 Lastname4Server is now updating the OS patching.
We will wait for this step to complete

 

 

Please advise on how to achieve this 

Need Help
Show and Tell
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    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"

     

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    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"