New Column Extracting Earlier Date from another colum

[Anonymous]

Hi, 

I need help wit a dataset I am working on. I have a column named Oder # where the orders will show duplicated because I have Different days that the Order was placed on Block. I need to organize it in a way that if Column Order # has Duplicates, the Column Day Order was placed on block shows only the EARLIER date .

Below is a more detailed example of what I need:

Any help would be greatly appreciated.

[Anonymous · Accepted solution]

Hi @Anonymous 

Please try this.

=Table.AddColumn(#"Changed Type","Custom",each let a=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[#"Order #"]=[#"Order #"])[Day Order Placed on Block])
in if a=[Day Order Placed on Block] then 1 else 0)

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.

[Ashish_Mathur]

Hi,

One can use the Table.Min function.  I can help further, i you share the download link of the PBI file.  Ensure that the raw data is in the file itself otherwise i will not be able to see any data in the Query Editor.

[Anonymous]

Hi @Anonymous 

You can create a blank query and put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcqxDcAwCETRXagtYQ7sxLMg779GnEhUueYXTz9ToFiKjpAmBn8ru6W4grNzDsamVhxjnuJjcHYd/EYvv+516uXz5/sB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Day Order Place on Block" = _t, #"Order Number" = _t, #"Order Item" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day Order Place on Block", type date}, {"Order Number", Int64.Type}, {"Order Item", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Number"}, {{"MinDate", each List.Min([Day Order Place on Block]), type nullable date}, {"Data", each _, type table [Day Order Place on Block=nullable date, Order Number=nullable number, Order Item=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Day Order Place on Block", "Order Item"}, {"Day Order Place on Block", "Order Item"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([MinDate] =[Day Order Place on Block])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MinDate"})
in
    #"Removed Columns"

Output

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.

[Anonymous]

HI Yolo, 

Thanks for the reply. I was able to create the new query as Instructed, however, I think I was not too clear on my initial question. I already have a query named:  Merged Orders Cycle Closed that contains that information. How can I apply this concept to this already existing Query? You can see that in the query that I have multiple lines with the same order number, however, different dates, so, I need my query to show only the 1 line with the information for the earlierst date.

Thank you.

[Anonymous]

Hi @Anonymous 

1.Create a custom column

let a=List.Min(Table.SelectRows(#"Changed Type"(last step name in your query),(x)=>x[#"Order #"]=[#"Order #"])[Day Order Placed on Block])
in if a=[Day Order Placed on Block] then 1 else 0

2.Then filter the custom column filter it equtal to 1

3.Then if you don't need the custom column, you can remove it after filtering.

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.

[Anonymous]

Hi Yolo, 

Thank you so much for the patience, but, I am still getting this error message.

Can you advise what am I doing wrong?

[Anonymous · Accepted solution]

Hi @Anonymous 

Please try this.

=Table.AddColumn(#"Changed Type","Custom",each let a=List.Min(Table.SelectRows(#"Changed Type",(x)=>x[#"Order #"]=[#"Order #"])[Day Order Placed on Block])
in if a=[Day Order Placed on Block] then 1 else 0)

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.

[Anonymous · Accepted solution]

Hi Yolo, 

It worked. Thank you very much for the help. I really appreciated it.