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
fbluemke
Advocate I
Advocate I

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
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
BhaveshPatel
Community Champion
Community Champion

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.

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.