cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fbluemke Frequent Visitor
Frequent Visitor

Create table that shows latest transaction number, based on filtering transactions on ship date

Hello,


i have a use case that I thought would be simple but I am struggling to find a solution to.


I have a table that has transaction numebers, product details and date shipped.  What i want to do is create a new table from that table that will show only 1 transaction ID>Product row based on the ship date.

Table is such:

SO NUMBER

Customer

Product

Ship Date

Units Shipped

 

Output i want is

SO NUmber - should only be one record per customer & Product combination, this should be the SO that was last shipped for that product/customer.

Ship Date

Units Shipped for that SO product

 

Right now i have a calculated table with a uniqe key for teh product and customer and the last ship date based on that combination using "MAX Ship Date", now i need to add the SO number that corresponds to that latest ship date - and if there are multiple show just one (doesnt matter which one).

 

HELP!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create table that shows latest transaction number, based on filtering transactions on ship date

Hi,

 

This M code creates the green table from the Blue table.

 

Hope this helps.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO Number", type text}, {"Customer", type text}, {"Product", type text}, {"Ship Date", type date}, {"Units shipped", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SO Number", "Customer", "Product"}, {{"Last shipped date", each List.Max([Ship Date]), type datetime}, {"Shipped units", each _, type table}}),
    #"Expanded Shipped units" = Table.ExpandTableColumn(#"Grouped Rows", "Shipped units", {"Ship Date", "Units shipped"}, {"Ship Date", "Units shipped"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded Shipped units", {{"Ship Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Last shipped date]=[Ship Date] then "Keep" else "Ignore"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Last shipped date", "Custom"})
in
    #"Removed Columns"

Untitled.png

 

Hope this helps.

2 REPLIES 2
Super User
Super User

Re: Create table that shows latest transaction number, based on filtering transactions on ship date

Hi @fbluemke 

 

You can use FIRSTNONBLANK(Customer[SO],1) and LASTNONBLANK(Customer[SO],1) functions to add in your calculated table to get the corresponding record. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Super User
Super User

Re: Create table that shows latest transaction number, based on filtering transactions on ship date

Hi,

 

This M code creates the green table from the Blue table.

 

Hope this helps.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO Number", type text}, {"Customer", type text}, {"Product", type text}, {"Ship Date", type date}, {"Units shipped", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SO Number", "Customer", "Product"}, {{"Last shipped date", each List.Max([Ship Date]), type datetime}, {"Shipped units", each _, type table}}),
    #"Expanded Shipped units" = Table.ExpandTableColumn(#"Grouped Rows", "Shipped units", {"Ship Date", "Units shipped"}, {"Ship Date", "Units shipped"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded Shipped units", {{"Ship Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Last shipped date]=[Ship Date] then "Keep" else "Ignore"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Last shipped date", "Custom"})
in
    #"Removed Columns"

Untitled.png

 

Hope this helps.