Power Query - Nested IF statement - Between two values

[rythmic_flow]

Hello,

I need help writing an IF statement for Power Query using the custom column function.

Column1 has values ranging from 1-12:

1
2
3
4
5
6
7
etc.

I would like to create another column that sorts the values into the following:

0-2
3-5
6-8
9-10
10+

What formula could i enter here to obtain this result?

 

[JGordon11]

let
    Source = Table.FromColumns({List.Accumulate({1..20},{}, (s,c)=> s & {Number.RoundUp(Number.RandomBetween(0,12))})}),
    bins = {2,5,8,10},
    labels = {"0-2", "3-5", "6-8", "9-10", ">10"},
    Result = Table.AddColumn(Source, "Bin", each let c = List.Count(List.Select(bins, (x)=> [Column1]>x)) in labels{c} )
in
    Result

 

[GraH]

Just to showcase a way that's 100% UI.
It's possible to append the table that holds the bin definition and the lower limit of the value and use sorting on the value before filling down.
To restore the original sort, you can use an index column.

I've re-used the source step from @JGordon11. Those function constructions of yours are clever J.

let
    Source = Table.FromColumns({List.Accumulate({1..20},{}, (s,c)=> s & {Number.RoundUp(Number.RandomBetween(0,12))})}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Value"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", Bins}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Value", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Bin"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Index] <> null)),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})
in
    #"Removed Columns"