let
RowsOutline = Expression.Evaluate(Text.FromBinary(File.Contents("J:\PQ\ExcelWorksheetsRowOutlines.pq")),#shared),
FullPath = Excel.CurrentWorkbook(){[Name="filename"]}[Content]{0}[Column1],
DataWithOutline = RowsOutline(FullPath,"Январ2019",true)[DataWithOutline]{0},
Headers = List.Buffer(List.FirstN(DataWithOutline[Column1],3)),
Promoted = Table.PromoteHeaders(DataWithOutline, [PromoteAllScalars=true]),
SkipEmpty = Table.Skip(Promoted,(_)=>[Показатели]=null),
ReplaceNull = Table.ReplaceValue(SkipEmpty,null,0,Replacer.ReplaceValue,{"Column1"}),
FillDown = Table.FillDown(ReplaceNull,{"Счет"}),
Col = List.Buffer(List.Skip(Table.ColumnNames(ReplaceNull),4)),
Pos = List.Buffer(List.PositionOf(Col,{"сальдо","Оборот"},3,(a,b)=>List.AnyTrue(List.Transform(b,each Text.Contains(a,_))))),
Col2 = List.Buffer(List.Transform(Pos,each Col{_})),
Col3 = List.Buffer(List.Transform(List.Zip({Pos,List.Skip(Pos)&{List.Count(Col)}}),each if _{1}-_{0}>1 then List.Transform({_{0}+1.._{1}-1},each Col{_}) else Col{_{1}-1})),
Structure = List.Zip({Col2,Col3}),
AddRec = Table.RemoveColumns(Table.AddColumn(FillDown, "rec", each let rec =_ in Record.FromList(List.Transform(Col3,each if Value.Is(_, type list) then Record.SelectFields(rec,_) else Record.Field(rec,_)),Col2)),Col),
Group = Table.Group(Table.AddIndexColumn(AddRec, "i", 0, 1), {"Column1", "Счет","i"}, {{"_", each _}},0,(a,b)=>if a[Column1]=b[Column1] and a[Счет]=b[Счет] then 0 else 1),
Indexed = Table.Group(Table.ExpandTableColumn(Table.RemoveColumns(Table.AddIndexColumn(Table.Sort(Group,"i"),"Индекс"),"i"), "_", {"Показатели","rec"}), {"Column1", "Счет", "Показатели", "Индекс"}, {{"rec", each [rec]}}),
Pivot = Table.Pivot(Table.Sort(Indexed,{{"Индекс", Order.Ascending}}), List.Distinct(Indexed[Показатели]), "Показатели", "rec"),
Sorted = Table.Sort(Pivot,{{"Индекс", Order.Ascending}}),
PivotedCol = List.Skip(Table.ColumnNames(Sorted),3),
GroupRecursive = (optional t,optional it)=>
let
t = if t = null then Sorted else t,
it = if it = null then 0 else it,
header = Headers{it},
Grouped = Table.Group(t, {"Column1"},
{
{header, each Record.ToList(_{0}){1}},
{"_", each let
tbl=Table.Skip(Table.Sort(_,"Индекс"),each [Column1]<=it)
in if it<List.Count(Headers)-2
then @GroupRecursive(tbl,it+1)
else Table.RenameColumns(tbl,{Headers{0},List.Last(Headers)})}
},
0,(a,b)=>if b[Column1]>a[Column1] then 0 else 1
)
in
Table.ExpandTableColumn(Grouped,"_",List.Skip(Headers,it+1)&PivotedCol),
Merge = Table.CombineColumns(GroupRecursive(),{"Сумма", "Кол-во", "БУ", "Кол."},(a)=>Table.SelectRows(#table({"a","b"},List.Zip({PivotedCol,a})),each [b]<>null),"a"),
ExpandRec = Table.ExpandTableColumn(Table.ExpandTableColumn(Merge, "a", {"a", "b"}, {"Показатели", "b"}), "b", Col2),
FullExpad = List.Accumulate(List.Select(Structure,each Value.Is(_{1},type list)),ExpandRec,(a,b)=>Table.ExpandRecordColumn(a,b{0},b{1},List.Transform(b{1},each b{0}&"."&Text.Split(_,"_"){0}))),
Unpivot = Table.UnpivotOtherColumns(Table.RemoveColumns(FullExpad,"Column1"), {"Показатели"}&Headers, "Атрибут", "Значение"),
Result = Table.SplitColumn(Unpivot, "Атрибут", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"})
in
Result |