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 parti...
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

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.