Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

New Column Extracting Earlier Date from another colum

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    Hi Yolo, 

     

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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.