Forum Discussion

fbluemke's avatar
fbluemke
Icon for Advocate I rankAdvocate I
7 years ago
Solved

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!

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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"

     

    Hope this helps.

2 Replies

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

    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. 

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

    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"

     

    Hope this helps.