Create a new column in Power BI power query

Hi Anonymous - I am not sure I would recommend this. This may not perform well over large (hundreds of thousands) records, but is workable. See the formula in the Max Date step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyUIrViVZKAgoYIQskAwWMkQVAWkzRtZihazGHC8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Container Number" = _t, #"Order Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),
    #"Max Date" = 
        Table.AddColumn(
            #"Changed Type",
            "Max Date",
            each 
                let
                    varCurrentContainer = [Container Number]
                in
            Table.Max(
                Table.SelectRows(
                    #"Changed Type",
                    each [Container Number] = varCurrentContainer
                ),
                "Order Date"
            )[Order Date]
        )
in
    #"Max Date"

Based on my fake data in the first two columns, it returns the max date for each container.

Power Query isn't the best place to do scans of tables. 
Ideally, this would be in a measure, not a calculated column, but I don't know your requirements. Reporting the date in a visual can be done by a measure. If you need the date in a slicer or report filter, then it must go in a column.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 

If I've answered your question, please mark as the solution. If not, can you provide some sample data with expected output?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

Hi Anonymous ,

 

I think edhans's solution is working perfectly. I create a sample using "Group by" to get the max date. You also could have a try.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31DcyMDJQ0lFyBGJDI6VYHbCoBbKwsQFY2ETfCCbqBFIMEzU0RjHDDCZsjixsCjPZANkMY7iwKbKwpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [Date=nullable date, ID=nullable text, Amount=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Amount"}, {"All.Date", "All.Amount"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Max] = [All.Date] then "yes" else "no")
in
    #"Added Custom"