Forum Discussion

danb's avatar
danb
Icon for Resolver I rankResolver I
7 years ago
Solved

Edit Queries - Create a new column to identify latest date based on criteria

Hello experts, 

I have a table of inventory data that is compiling over time.  I would like to add a column in Edit Queries that will layer in an identifyer that will tag the latest date for a particular Distributor/Item's inventory date and position. I would then filter on that new column to exclude all of the old data. 

 

Here is a mockup of the data

DistributorItem NameItem #Inventory DateQuantity
San DiegoApples10102587/22/201974
San DiegoApples10102587/21/201961
San DiegoApples10102587/20/201920
San DiegoApples10102587/19/201994
San DiegoApples10102587/18/201991
San DiegoBananas8765427/20/201941
San DiegoBananas8765427/18/201975
San DiegoBananas8765427/17/201924
San DiegoBananas8765426/30/201813
DenverOranges465997/21/201978
DenverOranges465997/19/201999
DenverOranges465997/18/201928
DenverBananas8765426/30/201959
DenverBananas8765426/29/201973
DenverBananas8765426/28/201986
DenverBananas87654212/31/20189
DenverApples10102584/30/201999
DenverApples10102586/30/201951
DenverApples10102581/22/2019100
Kansas CityApples101025812/25/201898
Kansas CityApples10102586/5/201931
Kansas CityOranges465994/30/201969
Kansas CityOranges465998/7/201894
Kansas CityBananas87654212/12/201852
Kansas CityBananas87654212/15/201887
Kansas CityBananas87654211/20/201772

 

And here is the desired solution (filtered on the tag)

DistributorItem NameItem #Inventory DateQuantityLatest?
DenverOranges465997/21/201978Latest
DenverBananas8765426/30/201959Latest
DenverApples10102586/30/201951Latest
Kansas CityOranges465994/30/201969Latest
Kansas CityBananas87654212/15/201887Latest
Kansas CityApples10102586/5/201931Latest
San DiegoBananas8765427/20/201941Latest
San DiegoApples10102587/22/201974Latest

 

Thanks in advance!

 

Dan

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, danb 

    You could try this way as below:

    Step1:

    Duplicate the basic table.

    Step2:

    Use Group By function in edit queries as below for duplicate table

     

     

    Step3:

    Now, merge the basic table and this new table

    Step4:

    Expand  Lastest Date column

     

     

    Now you could add a custom column for filter as below:

     

    here is my sample pbix file, please try it.

     

    Best Regards,

    Lin

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello danb 

    The new query below assumes your original table is called 'Table'

    let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Distributor", "Item #"}, {{"Inventory Date", each List.Max([Inventory Date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Distributor", "Item #", "Inventory Date"}, Table, {"Distributor", "Item #", "Inventory Date"}, "Table", JoinKind.Inner),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Item Name", "Quantity"}, {"Item Name", "Quantity"})
in
    #"Expanded Table"

    It uses group by and max then joins back to your original to get the QTY and name.

    • danb's avatar
      danb
      Icon for Resolver I rankResolver I
      7 years ago

      Hello jdbuchanan71  - thanks for your response. I do have a question however as where do i enter in the code? I tried dropping it into the Advanced Editor section however I got an error.

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, danb 

    You could try this way as below:

    Step1:

    Duplicate the basic table.

    Step2:

    Use Group By function in edit queries as below for duplicate table

     

     

    Step3:

    Now, merge the basic table and this new table

    Step4:

    Expand  Lastest Date column

     

     

    Now you could add a custom column for filter as below:

     

    here is my sample pbix file, please try it.

     

    Best Regards,

    Lin

    • danb's avatar
      danb
      Icon for Resolver I rankResolver I
      7 years ago

      v-lili6-msft  - this works great and you actually showed me how to simplify the approach. I originally was thinking I needed some type of flag column to filter out certain rows however simply grouping the data in the original table gets me to what i need. Many thanks again!

       

      Dan