Forum Discussion
ARP331AHS269
1 year agoFrequent Visitor
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...
- 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"
ronrsnfld
1 year agoSuper User
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"