Skip to content

Instantly share code, notes, and snippets.

@bjulius
Created September 17, 2022 05:32
Show Gist options
  • Save bjulius/11bef4cc0c73bd9d13083cc95ebf08db to your computer and use it in GitHub Desktop.
Save bjulius/11bef4cc0c73bd9d13083cc95ebf08db to your computer and use it in GitHub Desktop.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUShPTSwqVshILFGK1YlWKslIVUgCiigU5CRWFiuUZ5ZkKCQX5SeDJT1SFQqKKhOLFUryFUAK0/NT9JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OriginalText = _t]),
AddSpace = Table.AddIndexColumn( Table.ReplaceValue(Source,"."," .",Replacer.ReplaceText,{"OriginalText"}), "SentenceIndex", 1, 1),
SplitBySpace = Table.ExpandListColumn(Table.TransformColumns(AddSpace, {{"OriginalText", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "OriginalText"),
MergedReplaceText = Table.ExpandTableColumn( Table.NestedJoin(SplitBySpace, {"OriginalText"}, ReplaceText, {"From"}, "ReplaceText", JoinKind.LeftOuter), "ReplaceText", {"To"}),
FinText = Table.AddColumn( MergedReplaceText, "FinalText", each if [To] = null then [OriginalText] else [To]),
GroupBySent = Table.Group(FinText, {"SentenceIndex"}, {{"All", each _, type table [OriginalText=nullable text, SentenceIndex=number, To=nullable text, FinalText=text]}}),
#"Concat&Clean" = Table.SelectColumns( Table.ReplaceValue(Table.AddColumn(GroupBySent, "Final Text", each Text.Combine( [All][FinalText], " "))," .",".",Replacer.ReplaceText,{"Final Text"}), "Final Text")
in
#"Concat&Clean"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment