Maximum value of a column and group by ranges in PowerQuery

[Syndicate_Admin]

Hi, I put in context:

I have a table with several columns: On the one hand an ID, two columns that not to be very explicit are integers and a fourth that makes me the sum of the two previous columns.

What I have to do is take the maximum value of the last column mentioned and make 4 ranges with it, for this I have made a couple of divisions without more, dividing that maximum by 4 and making the groups from 0 to each division (for example, if I have 100 maximum value then 0-25, 26-50, 51-75 and 76-100 would be my groups. So depending on the value that each ID has for the TOTAL columa it would be in one of those groups.

For this I have made the following code:

But for each part I get the same error: "A cyclical reference was found during the evaluation." and I wondered what is wrong. The first, if the maximum I comment is achieved like this and second if the code is correct.

Thanks a lot!

[Ahmedx]

plse try this

[
    f =List.Max(#"Changed Type"[Column1]),
    d= 4,
    c = Number.Round( f/d,0),
    to = 
     if [Column1]<= c then "Group 1" else if 
        [Column1] <= c*2 then "Group 2" else if 
       [Column1] <= c*3 then  "Group 3" else "Group 4"][to]

[amitchandak]

@Syndicate_Admin , I tried a similar code in power query.

Please paste the same in the blank query and check

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlWK1YlWMoJSZmDKxABMmUIoQwMIbQ5UEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each let 
_min =List.Min(#"Changed Type"[Column1]),
_max =List.Max(#"Changed Type"[Column1]),
_range = (_max-_min-1)/4,
_formula = if [Column1] >= _min and [Column1] < _min + _range then "Group 1"  
else if [Column1] >= _min+ _range and [Column1] < _min + 2* _range then "Group 2" 
else if [Column1] >= _min +2* _range and [Column1] < _min + 3*_range then "Group 3" 
else if [Column1] >= _min + 3* _range  and [Column1] <=  _max then "Group 4" else ""
in  
_formula)
in
    #"Added Custom"

If you need refer another table refer

https://www.youtube.com/watch?v=InjrmY-LJdA&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=117