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

[Anonymous]

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 

Month

4/1/2023

5/1/2023

I want a new column that will say for 4/1/2023 - 1 and for 5/1/2023, like this 

Month         Order

4/1/2023      1

5/1/2023      2

Using custom column, or a way to add a new column 

[Ashish_Mathur]

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]

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]

You might as well show a representative sample, explain the business context and show the expected result.

[PhilipTreacy]

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]

Hi I still have the problem 

[Anonymous]

[Anonymous]

[PhilipTreacy]

@Anonymous 

Download example PBIX file

You haven't put the 1 after the else

It should be 

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

and this only works when the previous step is named #"Changed Type"

If you look at my example file (linked above) you'll see this works.

FYI - One of the reasons this is taking so long to solve is because you only gave us a snippet of the actual data in your original post and haven't provided any actual data to work with so we are guessing as to what yor data looks like and what steps you have in your query.

It's always best to provide a good sample of your data in a file or table - not images.

Phil