Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
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.
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |