Forum Discussion

ARP331AHS269's avatar
ARP331AHS269
Frequent Visitor
1 year ago
Solved

Need help with splitting data

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 n...
  • ronrsnfld's avatar
    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"