let
    f=(x)=>[a=Splitter.SplitTextByAnyDelimiter({"[","]","""-"","})(x[Name]),
            b=List.RemoveItems(List.Transform(a,Text.Trim),{""}),
            c=((val)=>if Value.FromText(val) is number then Number.From(val) else null)(List.Last(b))][c],
    from= Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    add=Table.AddColumn(from,"new",f)
in
    add

let
    from = Excel.Workbook(File.Contents(fParam("Path"))){[Name="Sheet1"]}[Data],
    val = List.Skip(Record.ToList(from{0})),
    lst=List.Buffer(Table.ToList(Table.Skip(from,2),(x)=>x)),
    tr=List.TransformMany(lst,(x)=>List.Zip({val, List.Skip(x)}),(x,y)=>List.FirstN(x,1) & y),
    to=Table.FromList(tr,(x)=>x,{"No.","QTY","Value"})
in
    to

let
    f=(x)=>Text.Combine(List.Transform(x,Text.From)," "),
    from = Excel.Workbook(File.Contents(fParam("Path"))){[Name="наборка"]}[Data],
    sel= Table.SelectRows(from,(x)=>not List.Contains({null,0},x[Column2])),
    fio=List.Buffer(Table.FirstN(sel,2)[Column2]),
    dog=Table.PromoteHeaders(Table.SelectRows(sel,(x)=>x[Column3]<>null))[[Текст],[Код работ],[Договор]],
    txt=Text.Combine({"XP 14"} & Table.ToList(dog,f) & fio, " ")
in
    txt

let
    from = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nms=List.Buffer(Table.ColumnNames(from)),
    typ=Table.TransformColumnTypes(from,{{nms{0},type date},{nms{1},type date}}),
    lst=List.Buffer(Table.ToList(typ,(x)=>x)),
    gen=List.Transform(lst,(w)=>List.Generate(
        ()=>[n=1,dt={w{0},w{0}+#duration(1,0,0,0),1 } ],
        (x)=>x[n]<= Duration.Days(w{1}-w{0}),
        (x)=>[n=x[n]+1,dt={x[dt]{1},x[dt]{1}+#duration(1,0,0,0),1 } ],
        (x)=>x[dt] )),
    tbl=Table.FromList(List.Combine(gen),(x)=>x,nms),
    gr=Table.Sort(Table.Buffer(Table.Group(tbl,List.FirstN(nms,2),{"Количество",(x)=>List.Sum(Table.Column(x,nms{2}))})),nms{0})
in
    gr

let
    f=(x)=>((a)=>List.Select(a,(x)=>Value.FromText(x) is date or List.AllTrue({Text.Length(x)=4,Text.StartsWith(x,"20")})){0}?)
            (List.Buffer(Text.SplitAny(x[Оглавление 2]," ,"))),
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    add = Table.AddColumn(from,"Дата",f)
in
    add

let
    f=(x)=>((a)=>Text.Combine(List.Select(a,
            (x)=>Value.FromText(x) is date or List.AllTrue({Text.Length(x)=4,Text.StartsWith(x,"20")})),", "))
            (List.Buffer(Text.SplitAny(x[Оглавление 2]," ,-"))),
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    add = Table.AddColumn(from,"Дата",f)
in
    add

let
    f=(x)=>Excel.CurrentWorkbook(){[Name=x]}[Content],
    g=(x)=>Table.SelectRows(dict,(y)=>Text.Contains(x[Материалы],y[материалы],Comparer.OrdinalIgnoreCase))[группа]{0}?,
    dict=f("справочник"),
    add=Table.AddColumn(f("Table1"),"группа",g)[группа]
in
    add

let
    f=(x)=> List.Sum(Record.ToList(Excel.Workbook(x,true,true){[Name="Лист1"]}[Data]{0})),

    fld = Folder.Contents(fParam("Path"))[[Name],[Content]],
    exp = Table.ExpandTableColumn(fld, "Content", {"Name","Content"},{"Месяц","Content.1"}),
    tr=Table.TransformColumns(exp,{"Месяц",(x)=>Text.Split(x,".xlsx"){0}}),
    bin=Table.TransformColumns(tr,{"Content.1",f}),
    piv = Table.Pivot(bin, List.Sort(List.Distinct(bin[Name])), "Name", "Content.1"),
    sort=Table.Sort(piv,(x)=>Date.FromText("1"&x[Месяц]))
in
    sort 

let
    f=(x)=>List.Sum(List.Transform(Text.Split(x[text],"#(lf)"),(y)=>Number.From(Text.Split(y," "){0}))),
    from = Excel.CurrentWorkbook(){[Name="text"]}[Content],
    add=Table.AddColumn(from,"сумма",f)
in
    add

let
      f=(x)=>((a)=>Text.Combine(List.Transform(a,(i)=>Text.TrimStart(i,Text.At(i,0)))))
      (Splitter.SplitTextByPositions({0,3,18,21})(x[text])),
      from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
      add=Table.AddColumn(from,"new",f)
in
    add

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ= Table.TransformColumns(from,{},Text.From),
    lst =List.Buffer(Table.ToList(typ,(x)=>List.Skip(x))),
    f = (row)=>List.Transform(List.Skip(row), (x)=>Text.Remove(row{0},x)),
    to = Table.FromRows(List.Transform(lst, f),{"1".."6"})
in
    to

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ= Table.TransformColumns(from,{},Text.From),
    lst = List.Buffer(Table.ToList(typ,(x)=>List.Skip(x))),
    gen=List.Generate(()=>[i=0,j=lst{i}],
                      (x)=>x[i]<List.Count(lst),
                      (x)=>[i=x[i]+1,j=lst{i}],    
                      (x)=>List.Transform(List.Skip(x[j]),(y)=>Text.Remove(x[j]{0},y))),
    to = Table.FromList(gen,(x)=>x,{"1".."6"})
in
    to

let
    h=(s,c)=>Number.From(c<>null),
    f=(x)=>[a=(tb,lst)=>Record.FromList(List.RemoveNulls(Record.ToList(tb{0})),lst),
            b=Table.Group(Table.Skip(x),"Column2",{"tmp",g},GroupKind.Local,h),
            g=(w)=>((a1)=>Table.Skip(Table.ReplaceRows(w,1,1,{a1})))(w{1} & a(w,{"Column1","Column2"})),
            c=Table.Combine(b[tmp]),
            d=Table.SplitColumn(c,"Column1",(x)=>{null,null,x},{"1","2","3"}),
            e=Table.ReplaceRows(d,0,1,{d{0} & a(x,{"1","2"})})][e],

    from = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    gr=Table.Group(from,"Column1",{"tmp",f},GroupKind.Local,h),
    sel=Table.SelectRows(gr,(x)=>x[Column1]=УК)[tmp]{0}
in
    sel

let
    g=(x,y)=>Record.FromList(List.Repeat({y},x), k(x)),
    h=(x,y)=>Record.FromList(x,y),
    k=(x)=>List.Buffer(List.Transform({1..x},(x)=>Date.MonthName(#date(2025,x,1)))),
    f=(x)=>Table.AddColumn(x,"new", if x[Column6]{0}="ежемесячно" 
                                    then (r)=>g(x[Column4]{0},List.Sum(x[Column5]))
                                    else (r)=>h(x[Column5],List.Transform(x[Column6],Text.Proper))),
    from = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    ren=Table.RenameColumns(from,{{"Column1","Код"},{"Column2","Наименование статьи"}}),
    gr=Table.Group(ren,{"Код","Наименование статьи"},{"tmp",(x)=>f(x){0}[new]}),
    exp = Table.ExpandRecordColumn(gr, "tmp", k(12))
in
    exp

let
    fx_date=(nms as list)=>
    [f=(x)=>List.Select(List.Transform({"ru","en","nso"},(y)=>Value.FromText(x,y)),(x)=> x is date or x is datetime){0}?,
    to=List.Transform(nms,(x)=>{x,f})][to],
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    lst={"Document Date","Столбец2"},
    tr=Table.TransformColumns(from,fx_date(lst))
in
    tr

let
//для столбцов с именами
    from = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    nms = List.Skip(List.Buffer(Table.ColumnNames(from)),2),
    col = Table.ToColumns(Table.SelectColumns(from,nms)),
    to = Table.FromColumns(List.Transform(col,List.RemoveNulls),nms)
in
    to
//=======================//   
let
//для всей таблицы
    f=(x)=>((a)=>Table.FromColumns(List.Transform(a,List.RemoveNulls),nms))
                 (List.Buffer(Table.ToColumns(x))),
    from = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    nms = List.Buffer(Table.ColumnNames(from)),
    gr=Table.Group(from,nms{0},{"tmp",f},GroupKind.Local,(s,c)=>Number.From(c<>null)),
    com=Table.Combine(gr[tmp])
in
    com

let
    f=(x)=>((a)=> if List.PositionOfAny(a,Value)<>-1 then "входит" else "") 
           (List.Buffer(Text.Split(x[data],"|"))),
    from = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    tr=Table.AddColumn(from,"result",f)
in
    tr 

let
    f=(x)=>[a=List.Buffer(Table.ToList(Table.Sort(x,"ревизия"),(x)=>x)),
            b=List.Transform(a,(x)=>{List.FirstN(x,3)}&List.Split(List.Skip(x,3),4)),
            c=List.Transform(b,(x)=> if List.NonNullCount(x{3})<>0 then {x{0},x{1}, List.ReplaceRange(x{2},0,4,x{3})} else x),
            d=List.Last(List.Transform(c,
            (x)=>if List.NonNullCount(x{2})=0 
               then List.Combine(List.Alternate(List.Last(List.Select(c,(y)=> List.NonNullCount(y{2})<>0)),1,1,1)) & {x{0}{1}} & x{1} 
               else x{0} & x{2})) ][d],
    from = Table.PromoteHeaders(Table.Skip(Excel.Workbook(File.Contents(fParam("Path"))){[Name="исходные данные"]}[Data])),
    gr=Table.Group(from,"ШифрРД",{"tmp",f}),
    to=Table.FromList(gr[tmp],(x)=>x,nms)    
in
    to

let
    f=(x)=>if x{1}="Исходящие звонки" then Text.Start(x{0},1)&"_"&x{1} else x{1},
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    com = Table.CombineColumns(from,{"Тип Показателей","Статистическое значение"},f,"Статистическое значение"),
    nms=List.Buffer(Table.ColumnNames(com)),
    unp = Table.UnpivotOtherColumns(com, List.FirstN(nms,2), "atr", "val"),
    piv = Table.Pivot(unp, List.Distinct(Table.Column(unp,nms{1})), nms{1}, "val")
in
    piv