//connection
let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Путь к файлу]),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name", "Column2", "Column4", "Column8", "Column16"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Дата/Время"}, {"Column3", "Тип"}, {"Column5", "Категория"}, 
{"Column6", "Col6"}, {"Column7", "Col7"}, {"Column9", "Col9"}, {"Column10", "Описатель"}, 
{"Column11", "Пар"}, {"Column12", "Состояние"}, {"Column13", "Уровень"}, 
{"Column14", "Описат1"}, {"Column15", "Описат2"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"Дата/Время", type datetime}})
in
    #"Changed Type" 

//Journal  
let 
src = connection, 
filter_level = Table.SelectRows(src, each Text.Contains([Уровень], "8") or Text.Contains([Уровень], "9") 
or Text.Contains([Уровень], "10") or Text.Contains([Уровень], "11") 
or Text.Contains([Уровень], "12") or Text.Contains([Уровень], "13") 
or Text.Contains([Уровень], "14") or Text.Contains([Уровень], "15")) 
in
    filter_level

src = JOURNAL,
    group_sum = Table.Group(src, {"Col9", "Описатель", "Col6"}, 
    {{"Сумма", each Table.RowCount(_), Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(group_sum,{{"Описатель", "Описание"}, {"Col6", "Обл"}}),
    filter_state = Table.SelectRows(src, each [Состояние] = "АКТ" or [Состояние] = "ACT"),
    rename_mod = Table.RenameColumns(filter_state,{{"Col9", "Наим."}}),
    group_Qty= Table.Buffer(Table.Group(rename_module, {"Наим.", "Описатель", "Col6"}, 
    {{"Общее кол-во", each Table.RowCount(_), Int64.Type}})),
    sort = Table.Sort(group_Qty,{{"Общее кол-во", Order.Descending}}),
    KeptRows = Table.FirstN(sort,20),
    add_AVG = Table.AddColumn(KeptRows, "Average", each Number.Round([#"Общее кол-во"]/#"calc_Total Hours",2)
    ),
    merge = Table.Join(add_AVG, {"Col9", "Описатель"}, #"Renamed Columns", {"Наим.","Описание"},  1),
    #"Removed Columns" = Table.RemoveColumns(merge,{"Col9", "Описание", "Обл"}),
    add_percent = Table.AddColumn(#"Removed Columns", "Percent All", each Number.Round([Сумма]/#"calc_newALM",3)
    ),
    reorder = Table.ReorderColumns(add_percent,{"Col9", "Описатель", "Общее кол-во", 
    "Average", "Сумма", "Percent All", "Col6"}),
    result=reorder meta [ALM_TD5=KeptRows]
in
    result 

src = JOURNAL,
    filter_state= Table.SelectRows(src, each [Состояние] = "АКТ" or [Состояние] = "ACT"
    ),
    group_QtyD = Table.Group(filter_state, {"Col9", "Описатель", "Col6"}, 
    {{"Times Active", each Table.RowCount(Table.Distinct(_)), Int64.Type}, 
    { "Active Time", each List.Max ( [#"Дата/Время"] ) - List.Min ( [#"Дата/Время"] ), type duration }}
    ),
    sort_Qty = Table.Sort(group_QtyD,{{"Times Active", Order.Ascending}}
    ),
    sort_d = Table.Sort(sort_Qty,{{"Active Time", Order.Ascending}}
    ),
    filter = Table.SelectRows(sort_d, each true),
    Total_sec = Table.AddColumn(filter, "Total Seconds", each Duration.TotalSeconds([Active Time]), type number
    ),
    Div_value = Table.AddColumn(Total_sec, "AVG Active Time", each [Active Time] / [Times Active], type duration
    ),
    reorder = Table.ReorderColumns(Div_value,{"Col9", "Описатель", "Times Active", "AVG Active Time", "Active Time", "Col6", "Total Seconds"}),
    removed = Table.RemoveColumns(reorder,{"Total Seconds"}),
    KeptRows = Table.FirstN(removed,20)
in
    KeptRows 

let
    src = Value.Metadata(Total_Al_Qty)[ALM_TD5], 
    sort_sum = Table.Sort(src,{{"Общее кол-во", Order.Descending}}),
    calcs_total = List.Sum(sort_sum[#"Общее кол-во"]),
    KeptRows = Table.FirstN(sort_sum,10),
    calc_top10 = Number.Round(List.Sum(KeptRows[#"Общее кол-во"])/calcs_total,3)
in
    calc_top10