Excel BI Excel Challenge 94 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Data", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    Group = Table.Group(SplitToRows, {"Index"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Data=nullable text, Index=number]}}),
    IncrementStep = Table.AddColumn(Group, "AddIncrement", each Table.AddIndexColumn([All],"Increment", 1,1)),
    Remove = Table.RemoveColumns(IncrementStep,{"Index", "All", "Count"}),
    Median = Table.AddColumn(Remove, "Median", each List.Median([AddIncrement][Increment])),
    Expand = Table.ExpandTableColumn(Median, "AddIncrement", {"Data", "Increment", "Index"}, {"AddIncrement.Data", "AddIncrement.Increment", "AddIncrement.Index"}),
    Filter = Table.SelectRows(Expand, each [Median] >= 2),
    Keep = Table.AddColumn(Filter, "Keep", each if Number.Abs([Median]-[AddIncrement.Increment]) <= 0.5 then 1 else 0),
    FiltereKeep = Table.SelectRows(Keep, each ([Keep] = 1)),
    Select = Table.SelectColumns(FiltereKeep,{"AddIncrement.Data", "AddIncrement.Index"}),
    Regroup = Table.Group(Select, {"AddIncrement.Index"}, {{"All", each _, type table [AddIncrement.Data=text]}}),
    AddConcat = Table.RemoveColumns( Table.AddColumn(Regroup, "Concat", each Text.Combine( [All][AddIncrement.Data], " ")), "All"),
    Join = Table.SelectColumns( Table.Join(Index, "Index", AddConcat, "AddIncrement.Index", JoinKind.LeftOuter), {"Data", "Concat"}),
    Rename = Table.RenameColumns(Join,{{"Concat", "Answer"}})
in
    Rename