Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
plutara
New Member

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

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 NumberOrder DateOrder LineAmountItem
012345610/1/2023110.00Widget - 2x2
012345610/1/2023220.00Widget - 2x3
012345710/5/202315.00Widget - 1x1
012345810/8/202317.50Widget - 1x2

 

Invoice Example:

Order NumberInvoice DateOrder LineAmountItem
012345610/15/2023110.00Widget - 2x2
012345710/15/202315.00Widget - 1x1
012345810/15/202317.50Widget - 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!

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1697593994992.png

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.

 

View solution in original post

1 REPLY 1
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1697593994992.png

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors