let
src = Table.SelectColumns(Table.SelectRows(Excel.CurrentWorkbook(), each [Name]="data"), "Content"),
getReadySpoolsColumns = {"1", "3", "4", "9", "10", "126", "12", "23", "28", "34", "39", "47", "115"},
pt1 = Table.TransformColumns(src, {"Content", each Table.SelectColumns(Table.PromoteHeaders(_), getReadySpoolsColumns)}),
expandContent = Table.ExpandTableColumn(pt1, "Content", Table.ColumnNames(pt1[Content]{0}), Table.ColumnNames(pt1[Content]{0})),
pt2 = Table.ReplaceValue(expandContent,"",null,Replacer.ReplaceValue, Table.ColumnNames(pt1[Content]{0})),
pt3 = Table.SelectRows(pt2, each [10] <> null and [10] <> ""),
pt4 = Table.TransformColumns(pt3,{{"3", Text.Lower, type text}, {"4", Text.Lower, type text}, {"9", Text.Lower, type text}, {"10", Text.Lower, type text}, {"126", Text.Lower, type text}, {"12", Text.Lower, type text}, {"28", Text.Lower, type text}, {"39", Text.Lower, type text}}),
pt5 = Table.CombineColumns(pt4,{"3", "4", "9"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"zls"),
pt6 = Table.TransformColumnTypes(pt5,{{"23", Int64.Type}, {"34", Int64.Type}, {"47", type date}}),
replErrors = Table.ReplaceErrorValues(pt6, {{"23", null}, {"34", null}}),
pt7 = Table.RemoveColumns(Table.SelectRows(replErrors, each not Text.Contains([10], "sup") and ([126]<>"ремонт" and [126]<>"tur. ремонт") and [115]<>"не получено"), {"115","126"}),
pt8 = Table.ReplaceValue(pt7, each [28], each if Text.Contains([28],"gm", Comparer.OrdinalIgnoreCase) then 1 else null,Replacer.ReplaceValue,{"28"}),
pt9 = Table.ReplaceValue(pt8, each [39], each if Text.Contains([39],"gm", Comparer.OrdinalIgnoreCase) then 1 else null,Replacer.ReplaceValue,{"39"}),
pt10 = Table.AddColumn(pt9, "ready", each
if [47]<>null
then "готов"
else
if (Text.Start([10],2)="fw" and (([23]<>null and [28]<>null and [34]<>null and [39]=null) or ([23]<>null and [28]=null and [34]<>null and [39]<>null) or ([23]<>null and [28]<>null and [34]<>null and [39]<>null)))
then "готов*"
else
if (Text.Start([10],2)="fw" and (([23]<>null and [28]<>null and [34]=null and [39]=null) or ([23]=null and [28]=null and [34]<>null and [39]<>null)))
then "готов"
else null),
pt11 = Table.AddColumn(pt10, "jointWithMaterial", each if [23]<>null or [34]<>null then 1 else 0),
groupData = Table.Group(pt11, {"zls"}, {{"data", each _, type table [zls=text, 10=text, 12=text, 23=nullable number, 28=nullable number, 34=nullable number, 39=nullable number, 47=nullable date, ready=nullable text, jointWithMaterial=number]}}),
pt12 = Table.AddColumn(groupData, "ready", each
if List.Count(List.RemoveNulls([data][ready]))>0
then if (List.Count(Table.SelectRows([data], each [jointWithMaterial]=1)[jointWithMaterial]) = List.Count(List.RemoveNulls(Table.SelectRows([data], each [jointWithMaterial]=1)[ready])))
then "WFO"
else null
else null),
pt13 = Table.Buffer(Table.SelectRows(Table.RemoveColumns(pt12,{"data"}), each [ready]<>null)),
pt14 = Table.SelectColumns(pt7, {"1", "zls", "10", "12"}),
pt15 = Table.AddColumn(pt14, "wfo1", each try pt13[ready]{List.PositionOf(pt13[zls],[zls])} otherwise null),
pt16 = Table.AddColumn(pt15, "wfo2", each try pt13[ready]{List.PositionOf(pt13[zls],[12])} otherwise null),
pt17 = Table.AddColumn(pt16, "wfo", each if [wfo1]<>null and [wfo2]<>null then "WFO" else null),
pt18 = Table.SelectRows(Table.SelectColumns(pt17, {"1","wfo"}), each [wfo]<>null)
in
pt18 |
