let
fn=(Src,Cola,Colb,Newcol)=>let
NestedRecursive=(table,Num)=>let
Snum = "."&Text.From(Num+1),
Ncolb = Colb&(if Num>0 then "."&Text.From(Num) else ""),
Nested = Table.NestedJoin(table,{Ncolb},Src,{Cola},Snum,JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Nested, Snum, {Colb}, {Colb&Snum}),
Check = List.NonNullCount(Table.Column(Expand,Colb&Snum))=0,
Result = if Check then Expand else @NestedRecursive(Expand,Num+1)
in Result,
fn=(t)=>let
fx=(a,b)=>let
last = List.Last(a),
list = try last{1} otherwise {},
val = List.RemoveNulls(Record.FieldValues(t{b})),
add = a&{{List.Count(a)+1,val}},
repl = List.RemoveLastN(a)&({List.RemoveLastN(last)&{List.Distinct(list&val)}}),
Result = if List.Count(List.Intersect({list,val}))=0 then add else repl
in Result,
Result=#table({Newcol,Cola},List.Accumulate({0..Table.RowCount(t)-1},{},fx))
in Result,
Filter = Table.SelectRows(Src,each not List.Contains(Table.Column(Src,Colb),Record.Field(_,Cola))),
z=(_) => List.RemoveNulls(List.RemoveLastN(Record.FieldValues(_{0}),1)),
Nested = fn(NestedRecursive(Filter,0)),
Expand = Table.SelectColumns(Table.ExpandListColumn(Nested,Cola),{Cola,Newcol}),
Result = Table.Sort(Expand,{{Newcol,0},{Cola,0}})
in Result
in
fn(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],"№","аналог","group №") |