locked
How to keep first 2 iterations of grouped data RRS feed

  • Question

  • I would like to use power query to only keep the first 2 iterations of the example data below based on the valid on date.

    Data would be grouped/sorted by Vendor, Material, and Plant in descending order by valid on date.  Then how could I remove any lines above 2 iterations.

    Would start with this:

    Vendor                   Material   Plnt     Valid on
    123 555 456    2/6/2017
    123 555 456  12/12/2016
    123 555 456   8/4/2016
    234 777 567   8/5/2016
    234 777 567   2/8/2016
    234 777 567   11/8/2015
    234 777 567   6/4/2015
    234 777 567   3/2/2015

    Would end with this

    Vendor  Material  Plnt    Valid on
    123 555 456   2/6/2017
    123 555 456  12/12/2016
    234 777 567   8/5/2016
    234 777 567   2/8/2016


    Tuesday, October 24, 2017 2:51 PM

Answers

  • Hey,

    I believe this is what you're searching for:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5BCsAgDATAr0jOQppoEv8i/s23+LKm2NJLrbDsYZnD1grECSKIiHcW9R49jM6oyAcZtPhtiNHjRJckFMyv4JR9NbPLqT1CNoKxbATRJLImOo/8iIR8i3YC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"                  Material" = _t, #"  Plnt" = _t, #"    Valid on" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"                  Material", Int64.Type}, {"  Plnt", Int64.Type}, {"    Valid on", type date}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"    Valid on", Order.Descending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Vendor", "                  Material", "  Plnt"}, {{"Count", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([Count],2)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"    Valid on"}, {"    Valid on"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"    Valid on", type date}})
    in
        #"Changed Type1"

    • Marked as answer by 9mike Tuesday, October 24, 2017 5:30 PM
    Tuesday, October 24, 2017 4:36 PM

All replies

  • Hey,

    I believe this is what you're searching for:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5BCsAgDATAr0jOQppoEv8i/s23+LKm2NJLrbDsYZnD1grECSKIiHcW9R49jM6oyAcZtPhtiNHjRJckFMyv4JR9NbPLqT1CNoKxbATRJLImOo/8iIR8i3YC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vendor = _t, #"                  Material" = _t, #"  Plnt" = _t, #"    Valid on" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"                  Material", Int64.Type}, {"  Plnt", Int64.Type}, {"    Valid on", type date}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"    Valid on", Order.Descending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Vendor", "                  Material", "  Plnt"}, {{"Count", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([Count],2)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"    Valid on"}, {"    Valid on"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"    Valid on", type date}})
    in
        #"Changed Type1"

    • Marked as answer by 9mike Tuesday, October 24, 2017 5:30 PM
    Tuesday, October 24, 2017 4:36 PM
  • Thanks Miguel.  Worked like a charm!
    Tuesday, October 24, 2017 5:31 PM