Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter only rows with the earliest date

Hello everyone,

 

I'm trying to filter a table on PowerQuery based on the date.

 

I have a list of several items, and these items might have been purchased on several dates, and I want to filter only the earliest line of each item (last purchase).

 

I'm all good doing that as calculated columns and stuff, but I was hoping to do it on PowerQuery to lighten the dataset. I'm struggling to find a solution to this.

 

Any help will be appreciated!


Thank you!

Need Help
Tutorial Requests
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You could try below M code to see whether it work or not

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDIwN9I31DpVgdiKARTNBQ3wguaIwQNDQFiyah6kcIGiEEjeGCSPqBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, po = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"po", Int64.Type}, {"date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"name", Order.Ascending}, {"date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"all", each _, type table [name=text, po=number, date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"po", "date", "index"}, {"po", "date", "index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([index] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index"})
in
    #"Removed Columns1"

     

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Just FYI, each line of the same item has a different Purchase Order number, so I can't use Group By because I need both the latest late and the PO number of that latest date.

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi Anonymous , 

      You could try below M code to see whether it work or not

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDIwN9I31DpVgdiKARTNBQ3wguaIwQNDQFiyah6kcIGiEEjeGCSPqBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, po = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"po", Int64.Type}, {"date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"name", Order.Ascending}, {"date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"all", each _, type table [name=text, po=number, date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"po", "date", "index"}, {"po", "date", "index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([index] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index"})
in
    #"Removed Columns1"

       

      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.