Can I do select after group by?

[sabeer6870]

I have a data set like this:

TableXY:

JobId	type	start time	Endtime
1	A	9:00	11:00
1	B	9:30	12:00
1	C	10:00	10:30
2	A	9:15	11:15
2	B	9:30	12:00
2	C	9:45	10:30
2	D	11:00	11:45
3	A	6:00	6:48
3	B	7:00	7:30
3	C	6:35	6:40
3	D	11:15	11:40
3	E	10:45	11:45

I grouped it by id, it gives me something like this:

JobId	all_other_row
1	Table
2	Table
3	Table

Now I want to make another column where I can put the earliest starttime per job irrespective of the "type".

One option is to create a new column then do 

Record.Field(Table.Min([all_other_row], "starttime"), "starttime")

this will give me the min time, same for the max. But what if I want to select a row from the "all_other_rows" table by name ?

Something like, "select * from all_other_row" where type contains 'A' " ?

Can I use "Table.SelectRow" in someway to achieve this?

Is there a way I can filter rows from the table i get by doing "group by"?

[dax]

Hi sabeer6870, 

I am not sure the detailed requirement, If you want to filter specific record, you could use M code like below

 #"Filtered Rows" = Table.SelectRows(#"Expanded all", each ([type] = "A"))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5LDoAgDETvwppFy1e6U/EUhPtfQ0oRTHRBJplXZqYUhUqrvb1EAE0QWasWcHRgOzBvcLID4wvwBRMzs9BLVtMH/GaZkZXI+U9Unnu6OsmyoyQICOS26XNHFD8+SXZUBLJe7pef50ppWOSSNc6v7noD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JobId = _t, #"type" = _t, #"start time" = _t, Endtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JobId", Int64.Type}, {"type", type text}, {"start time", type time}, {"Endtime", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JobId"}, {{"max", each List.Max([Endtime]), type time}, {"min", each List.Min([start time]), type time}, {"all", each _, type table [JobId=number, type=text, start time=time, Endtime=time]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"type", "start time", "Endtime"}, {"type", "start time", "Endtime"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded all", each ([type] = "A"))
in
    #"Filtered Rows"

If this is not waht you want, please correct me and inform me your expected output, then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sabeer6870]

@dax  thanks for the reply. 

Lets say table might be something like this for jobID  =1:

type	starttime	endtime
some random text [type-A]
some other random text [type-B]
Unknown [type-C]

Can I create a new column from this by selecting the row and column of my choice ?

can I select the row and column from the above table which I got it by doing "Group By"?

something like, 

= Table.Column([all_other_rows], [type] contains "type-B")

[sabeer6870 · Accepted solution]

I got a workaround which works for my case. Instead of using "contains" to find the row, i can use "text.combine" to make the row name for matching. 

something like,

 Table.ToList(Table.SelectColumns(Table.SelectRows([all_other_rows], each [type] = Text.Combine({"Not so random string", "type-A"})), "type"))

but the ideal solution would be to filter rows by "if column value contains a particular string"

EDIT: seems like i can use contains got this from official doc;

Table.SelectRows(Table.FromRecords({ [CustomerID = 1, Name = "Bob", Phone = "123-4567"], [CustomerID = 2, Name = "Jim", Phone = "987-6543"] , [CustomerID = 3, Name = "Paul", Phone = "543-7890"] , [CustomerID = 4, Name = "Ringo", Phone = "232-1550"] }), each not Text.Contains([Name], "B"))