Forum Discussion

phchu1226's avatar
phchu1226
Helper IV
4 years ago
Solved

Error on MIN function while creating custom column

Hi all, I want to write a formula to extract the min. date from the column. However, the "MIN" function seems do not work..? Do anyone know how to achieve this? Thank you.  
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi phchu1226 

     

    I checked your file, use this code:

    = Table.AddColumn(SOP_PROD_OS1, "Custom", each if [S_DATE]=List.Min(SOP_PROD_OS1[#"S_DATE"]) then "Week0" else "Week1")

     

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

    Appreciate your Kudos🙏!!

  • VahidDM's avatar
    VahidDM
    4 years ago

    The last step in your file is "SOP_PROD_OS1", you can find it if you open the advance editor.
    It shows Navigation because your file is connected to Oracle DB.

     

    You can find the Last Date in your list, then find the seven days before the date, and write an if statement to put yes if the date is => than that date.

    At the end, filter on Yes on the new column.

     

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

    Appreciate your Kudos ✌️!!

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi phchu1226 

 

Try to use List.Min code rather than Min in your code.

 

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

Appreciate your Kudos✌️!!

 

phchu1226's avatar
phchu1226
Helper IV
4 years ago

Hi VahidDM ,

Tried List.Min, another error occur..no idea how to do this in power query..

 

 

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi phchu1226 

     

    Try this code:

    if [S_Date]=List.Min(#"Renamed Columns"[#"S_Date"]) then "Week0" else "Week1"

     

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

    Appreciate your Kudos✌️!!

    • phchu1226's avatar
      phchu1226
      Helper IV
      4 years ago

      Hi, VahidDM,

      I tried to paste the code above to the "custom column formula" and the below error occur..

      what means of this part?

       

       

       

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        Hi phchu1226 

         

        Change that #"Renamed Columns" in the code to the last step name, for instance if the last step is Changed Type, code should be :

         

        if [S_Date]=List.Min(#"Changed Type"[#"S_Date"]) then "Week0" else "Week1"

         

         

         

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

        Appreciate your Kudos✌️!!