Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danb
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
v-lili6-msft
Community Support
Community Support

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
v-lili6-msft
Community Support
Community Support

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.

@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

 

jdbuchanan71
Super User
Super User

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.