let
from = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="adres"]}[Content]{0}[adres]))[Data]{0},
rem = Table.Skip(from,4),
prom = Table.PromoteHeaders(rem),
filtr = Table.SelectRows(prom, each ([1] <> null)),
group = Table.Group(filtr, {"4"}, {{"data", each _, type table}}),
select = Table.TransformColumns(group,{{"data", each Table.SelectColumns(_,{"1","7","8","9","10","12","18"})}}),
group2 = Table.TransformColumns(select, {{"data",each Table.Group(_,{"8","9"}, {{"data2",each _, type table}})}}),
expand = Table.ExpandTableColumn(group2, "data", {"8", "9", "data2"}, {"8", "9", "станции"}),
typ = Table.TransformColumns(expand,{{"8", each DateTime.ToText(_,"yy\/MM hh:mm")}}),
add = Table.AddColumn(typ,"вагоны",each Table.Column([станции],"1")),
add2 = Table.AddColumn(add,"вес", each if List.Sum(Table.Column([станции],"10"))<>0 then "груж" else "порож"),
stanc = Table.TransformColumns(add2, {{
"станции", each Text.Combine(
List.Distinct(List.Combine({Table.Column(_,"18"),
Table.Column(_,"12"),
Table.Column(_,"7")
})
)
,"/")
}}
),
add3 = Table.AddColumn(stanc,"вагонов", each "+" & Text.From(List.Count([вагоны])) & "в"),
expand2 = Table.ExpandListColumn(add3, "вагоны"),
text = Table.AddColumn(expand2,"информация",each [4] & " " & [вагонов] & " " & [вес] & " " & [станции] & " " & [9] & " " & [8]),
select2 = Table.SelectColumns(text,{"4","информация","вагоны"}),
to = Table.RenameColumns(select2,{{"4", "поезд"}})
in
to |
