Hi I have two dates in my data (the dates are always changing) so I want to identifiy which is the MIN and which is the MAX in a NEW column I have Month4/1/20235/1/2023 I want a new column that will say for 4/1/2023 - 1 and for 5/1/2023, like this Month Order4/1/2023 15/1/2023 2 Using custom column, or a way to add a new column

Hi Anonymous You could use this = if [Month] = List.Max(#"Changed Type"[Month]) then 2 else 1 where this step is referencing the [Month] column in the previous step, which in this case is called #"Changed Type". In your case it may well be called soemthign else, you'll need to change it accordingly. Regards Phil

Hi, This M code works let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}), Custom1 = Table.AddRankColumn(#"Changed Type","Order",{"Month", Order.Ascending},[RankKind = RankKind.Competition]) in Custom1 Hope this helps.

IF Condition in Power Query using Custom Column to find MIN and MAX date

8 Replies

PhilipTreacy
Super User
3 years ago
Hi Anonymous

You could use this

= if [Month] = List.Max(#"Changed Type"[Month]) then 2 else 1

where this step is referencing the [Month] column in the previous step, which in this case is called #"Changed Type". In your case it may well be called soemthign else, you'll need to change it accordingly.

Regards

Phil
- Anonymous
  Not applicable
  3 years ago
  Hi I still have the problem
  - Anonymous
    Not applicable
    3 years ago
Ashish_Mathur
Super User
3 years ago
Hi,

This M code works

let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}), Custom1 = Table.AddRankColumn(#"Changed Type","Order",{"Month", Order.Ascending},[RankKind = RankKind.Competition]) in Custom1

Hope this helps.
- Anonymous
  Not applicable
  3 years ago
  In this case is working for you because you only have 2 columns but if you have more it will give you the row number where the next value is starting
  - Ashish_Mathur
    Super User
    3 years ago
    You might as well show a representative sample, explain the business context and show the expected result.