When combining 2 tables, how do I have Power BI use the oldest date for all lines in an order number

[plutara]

I have 2 sets of data - one for orders and one for invoicing - that I combine for a report I complete weekly.  The date for the orders is the date the order was received and the date for the invoice is the date the invoice was created.  Each order can have multiple lines - 1 for each item ordered (these are in two separate columns - order number in one and line number in another).  This results in duplicated order numbers in the order number column but each  line is still unique based on the line number in its' separate column.  The report I prepare combines the two tables so we can see the movement for each order/line within the month.  The report requires that all the orders have the original order date, not the invoiced date (if it has been invoiced).  I cannot figure out how to have Power BI change any date within the same order number to the oldests date and have to make the changes by hand - which, during week 1 isn't that many but, by week 3, gets pretty daunting.  

Order Example:

Order Number	Order Date	Order Line	Amount	Item
0123456	10/1/2023	1	10.00	Widget - 2x2
0123456	10/1/2023	2	20.00	Widget - 2x3
0123457	10/5/2023	1	5.00	Widget - 1x1
0123458	10/8/2023	1	7.50	Widget - 1x2

Invoice Example:

Order Number	Invoice Date	Order Line	Amount	Item
0123456	10/15/2023	1	10.00	Widget - 2x2
0123457	10/15/2023	1	5.00	Widget - 1x1
0123458	10/15/2023	1	7.50	Widget - 1x2

I am a complete novice to Power BI - the transform I created was a lot of googling and trial and error.  Any suggestions would be greatly appreciated!

[v-xinruzhu-msft · Accepted solution]

Hi @plutara 

You can put the following code to advanced editor in power query to add the min_orderdate column in order table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA0MjYxNVPSUTI00DfUNzIwMgaxwXw9AwMgHZ6Zkp5aoqCrYFRhpBSrg0uLEQhjajFG0mIO0WKKbIspmg7DCkMkHRYQHRbIOsz1TNF0AJ0VCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Order Date" = _t, #"Order Line" = _t, Amount = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Order Date", type date}, {"Order Line", Int64.Type}, {"Amount", type number}, {"Item", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Number"}, {{"Min", each List.Min([Order Date]), type nullable date}, {"Data", each _, type table [Order Number=nullable number, Order Date=nullable date, Order Line=nullable number, Amount=nullable number, Item=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Order Date", "Order Line", "Amount", "Item"}, {"Order Date", "Order Line", "Amount", "Item"})
in
    #"Expanded Data"

Then create a relationship between two tables in data modeling

Then you can use the min order date.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.