Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Check for earliest date in table for comparison using M results in cyclic error

In Power Query, I have a table which has a date column. I want to write a custom column that checks if the date is the minimum date for that field, then return true, else false.   if List.Contains(...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    To get the minimum date in a column, please refer to this syntax:

    AddCustom = Table.AddColumn(PreviousStep, "Custom", each List.Max(PreviousStep[a]))

     

    Then, wrap above step inside an if condition.

    = Table.AddColumn(#"Changed Type", "Custom.1", each if [Date]=List.Min(#"Changed Type"[Date]) then "true" else "false")

     

    Best regards,

    Yuliana Gu

     

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

To get the minimum date in a column, please refer to this syntax:

AddCustom = Table.AddColumn(PreviousStep, "Custom", each List.Max(PreviousStep[a]))

 

Then, wrap above step inside an if condition.

= Table.AddColumn(#"Changed Type", "Custom.1", each if [Date]=List.Min(#"Changed Type"[Date]) then "true" else "false")

 

Best regards,

Yuliana Gu

 

ratercero's avatar
ratercero
Icon for Helper III rankHelper III
8 years ago

Hello v-yulgu-msft,

 

what condition would I need to add in order to get the minimun date for each site?

 

Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    ratercero I ended up just bringing in a second reference to the table for another situation, grouping on the ID and value, then merging it in to make it work. I found that even if I could make more of it work in a single step that it ended up hurting readability for anyone else coming in after me.