Forum Discussion
Need custom column to return value based on comparison of dates and other columns
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! 🙂
Helper IHi 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! 🙂
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! 🙂
