cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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

8.JPG

 

9.JPG

 

Step3:

Now, merge the basic table and this new table

10.JPG

Step4:

Expand  Lastest Date column

11.JPG

 

12.JPG

 

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

14.JPG

 

here is my sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

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.

latesttable.jpg

Highlighted
Community Support
Community Support

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

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

8.JPG

 

9.JPG

 

Step3:

Now, merge the basic table and this new table

10.JPG

Step4:

Expand  Lastest Date column

11.JPG

 

12.JPG

 

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

14.JPG

 

here is my sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Resolver I
Resolver I

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

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.

Highlighted
Resolver I
Resolver I

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

@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

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors