Forum Discussion

sabeer6870's avatar
sabeer6870
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Can I do select after group by?

I have a data set like this:

TableXY:

JobIdtypestart timeEndtime
1A9:0011:00
1B9:3012:00
1C10:0010:30
2A9:1511:15
2B9:3012:00
2C9:4510:30
2D11:0011:45
3A6:006:48
3B7:007:30
3C6:356:40
3D11:1511:40
3E10:4511:45

 

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

JobIdall_other_row
1Table
2Table
3Table

 

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"?

Need Help
Tips and Tricks
  • sabeer6870's avatar
    sabeer6870
    6 years ago

    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"))

3 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      sabeer6870
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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's avatar
        sabeer6870
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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"))