Need custom column to return value based on comparison of dates and other columns

[Milejdi8]

I've been struggling with this for some time and I'm not even sure this is possible. 
I have a query with the list of orders, as in table example below.

I am trying to create a custom column based on the following logic:
If the order is first in the period (smallest date for the combination of customer&product), it is Original order (doesn't matter if its delivered or not). 
If the first order is not delivered, the next one is duplicate... and so on, until (including) the first order with actual delivery.
Each order after the order with delivery value is Original. 

Logic is: customer orders; if this order is not delivered than he'll repeat the order few more times until delivery is done (not the perfect system but that's how it works). I want to sum ordered volumes, but I'd like to skip duplications due to reordering.

I know how to do this in excel which makes it double frustrating that I can't figure it out in Power Query.

Thanks!

[Knighthawk · Accepted solution]

Hi Milejdi8,

I have provided code below that I believe will provide the results that you are looking for (of course you will need to adjust the "Source" to match your data table):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Product", type text}, {"Ordered", Int64.Type}, {"Delivered", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Conditional Column",JoinKind.LeftOuter),
    #"Expanded Added Conditional Column" = Table.ExpandTableColumn(#"Merged Queries", "Added Conditional Column", {"Delivered"}, {"Delivered.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Conditional Column",{{"Index", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Index] = 0 or ([Delivered] = null and [Delivered.1] <> null)  then "Original Order" else "duplicate"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Date", "Customer", "Product", "Ordered", "Delivered", "Custom"})
in
    #"Removed Other Columns"

I hope this helps!  🙂

[Milejdi8]

That's it! I had to tweak it a bit, because of course my table had 10-15 more columns, and I had to take into account first order when the customer / product changes (Index is not 0, so it's a first order)... But I learned a lot about Index columns (never knew all the possibilities) so it opened a whole new area of exploring for me 🙂

[Knighthawk]

I am very happy to hear that this solution worked for you.  As reference in case it helps, and since I recently learned the process also, Power Query uses zero-based numbering, so zero is considered the first row, one is considered the second row and so on.  So, the main intent behind the indexing here is for three aspects:

-  Track the original and new "Delivered" columns for use in the logic

-  Compare the two index columns to capture the value of the previous row, and also for use in part of the logic

-  Reorganize the data back into its original order after merging the query with itself

I hope that this information is helpful.  It may be something that you are already aware of, but since it was something new to me as I learned about M code recently, it was explained to me in a similar way and thought I should pass along the knowledge to those that might benefit as I did.  Should you have any questions about any of the steps I took, please feel free to ask any time.

Good luck with your project! 🙂 

[AUDISU]

@Milejdi8 

Please try this.

https://stackoverflow.com/questions/72670150/weighted-average-cost-in-power-query

same scenario, but different columns.

Thank you.

[Milejdi8]

Thank you! I checked, but I am afraid it's still beyond my knowledge; I'll keep it as a refference though, to check it in a few months.