let
data = Csv.Document (
File.Contents ( "C:\Users\kovalev.i\Downloads\TRENDN17\TRENDN17.csv" ),
[ Delimiter = ",", Columns = 6, Encoding = 1251, QuoteStyle = QuoteStyle.None ]
),
promHeads = Table.PromoteHeaders ( data, [ PromoteAllScalars = true ] ),
typed = Table.TransformColumnTypes (
promHeads,
{
{ "Trend 1 Time", type datetime },
{ "Trend 2 Time", type datetime },
{ "Trend 3 Time", type datetime },
{ "Trend 1 ValueY", type number },
{ "Trend 2 ValueY", type number },
{ "Trend 3 ValueY", type number }
}
),
addInd = Table.AddColumn ( typed, "process ind", each [Trend 1 ValueY] <> 0 or [Trend 2 ValueY] <> 0 or [Trend 3 ValueY] <> 0, type logical ),
group = Table.Group (
addInd,
{ "process ind" },
{
{ "Дата", ( t ) => Date.From ( List.Min ( t[Trend 1 Time] ) ), type date },
{ "Trend 1 start", ( t ) => Time.From ( List.Min ( Table.SelectRows ( t, ( r ) => r[Trend 1 ValueY] <> 0 )[Trend 1 Time] ) ), type time },
{ "Trend 1 end", ( t ) => Time.From ( List.Max ( Table.SelectRows ( t, ( r ) => r[Trend 1 ValueY] <> 0 )[Trend 1 Time] ) ), type time },
{ "Trend 2 start", ( t ) => Time.From ( List.Min ( Table.SelectRows ( t, ( r ) => r[Trend 2 ValueY] <> 0 )[Trend 2 Time] ) ), type time },
{ "Trend 2 end", ( t ) => Time.From ( List.Max ( Table.SelectRows ( t, ( r ) => r[Trend 2 ValueY] <> 0 )[Trend 2 Time] ) ), type time },
{ "Trend 3 start", ( t ) => Time.From ( List.Min ( Table.SelectRows ( t, ( r ) => r[Trend 3 ValueY] <> 0 )[Trend 3 Time] ) ), type time },
{ "Trend 3 end", ( t ) => Time.From ( List.Max ( Table.SelectRows ( t, ( r ) => r[Trend 3 ValueY] <> 0 )[Trend 3 Time] ) ), type time }
},
GroupKind.Local
),
filter = Table.SelectRows ( group, each ( [process ind] = true ) ),
delInd = Table.RemoveColumns ( filter, { "process ind" } )
in
delInd |