Generate round to nearest 2 interval (text) from 2 values

[ooptennoort]

Dear experts,
I am trying to convert an excel formula into M for PQ but I think it cannot be done. I need to generate an interval (of 2's) from 2 values.

First	Second	Great result	Ideal Result
2.5​	12.5​	6-8	2-4;4-6;6-8;8-10;10-12;12-14
4.2​	13.2​	8-10	4-6;6-8;8-10;10-12;12-14
8​	1​	4-6	0-2;2-4;4-6;6-8
0.1​	4.9​	2-4	0-2;2-4;4-6
116​	117​	116-118	116-118
34​	34​	34-36	34-36
32.3​	32.3​	32-34	32-34
5​	13​	8-10	4-6;6-8;8-10;10-12;12-14

The 3rd column would be great. (Did not even attempt the 4th column.)
This was my excel formula:
=IF([@Second]=0,MROUND([@First],2)&"-"&(MROUND([@First],2)+2),MROUND(MAX(0,AVERAGE([@First],[@Second])-1),2)&"-"&MROUND((AVERAGE([@First],[@Second])+1),2))

This is what I produced so far (when I ran into errors, esp. because of &):
= Table.AddColumn(Add2Interval, "2Interval", each if [Second]=0 then Number.ToText(RoundingMode.ToEven([First]/2)*2)
else if [Second]=[First] then [First]
else Number.ToText(RoundingMode.ToEven([Second]/2)*2-1)&"-"&Number.ToText(RoundingMode.ToEven([Second]/2)*2+1), type text)

Anyone?

 

[jasonb75]

To be fair, you did ask for M code at the start so it's not really a solution anyway and no need to mark it as one. To be fair, M code is a bit out of my league, I did start looking at it yesterday but got nowhere.

I don't know of any active forum members that are fluent in M code, the only one that was has been banned from the forum for some reason.

 

[ooptennoort]

So I'll leave it unsolved and hope this post serves as a lure to attract someone with M skills Thanks again (so far )!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.TransformColumnTypes(Source,{{"First", type number}, {"Second", type number}}),
    tbl2 = Table.AddColumn(tbl1,"Lower", each if _[First]< _[Second] then Number.RoundDown(_[First]) else Number.RoundDown(_[Second])),
    tbl3 = Table.TransformColumns(tbl2, {"Lower", each if Number.IsEven(_) then _ else _ -1}),
    tbl4 = Table.AddColumn(tbl3,"Upper", each if _[First]< _[Second] then Number.RoundUp(_[Second]) else Number.RoundUp(_[First])),
    tbl5 = Table.TransformColumns(tbl4, {"Upper", each if Number.IsEven(_) then _ else _ +1}),
    tbl6 = Table.AddColumn(tbl5, "Ideal", each List.Generate(() => _[Lower], (x)=> x<_[Upper] or x=_[Lower], (x)=> x+2, (x)=> Number.ToText(x) & "-" & Number.ToText(x+2))),
    tbl7 = Table.RemoveColumns(tbl6,{"Lower", "Upper"}),
    Result = Table.TransformColumns(tbl7, {"Ideal", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
    Result

Book1
	A	B	C	D	E	F	G
1	First	Second		First	Second	Ideal
2	2.5	12.5		2.5	12.5	2-4;4-6;6-8;8-10;10-12;12-14
3	4.2	13.2		4.2	13.2	4-6;6-8;8-10;10-12;12-14
4	8	1		8	1	0-2;2-4;4-6;6-8
5	0.1	4.9		0.1	4.9	0-2;2-4;4-6
6	116	117		116	117	116-118
7	34	34		34	34	34-36
8	32.3	32.3		32.3	32.3	32-34
9	5	13		5	13	4-6;6-8;8-10;10-12;12-14
10
Sheet1

 

[JGordon11]

One step was unnecessary. Here's a slightly shorter version:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl1 = Table.TransformColumnTypes(Source,{{"First", type number}, {"Second", type number}}),
    tbl2 = Table.AddColumn(tbl1,"Lower", each if _[First]< _[Second] then Number.RoundDown(_[First]) else Number.RoundDown(_[Second])),
    tbl3 = Table.TransformColumns(tbl2, {"Lower", each if Number.IsEven(_) then _ else _ -1}),
    tbl4 = Table.AddColumn(tbl3,"Upper", each if _[First]< _[Second] then Number.RoundUp(_[Second]) else Number.RoundUp(_[First])),
    tbl5 = Table.AddColumn(tbl4, "Ideal", each List.Generate(() => _[Lower], (x)=> x<_[Upper] or x=_[Lower], (x)=> x+2, (x)=> Number.ToText(x) & "-" & Number.ToText(x+2))),
    tbl6 = Table.RemoveColumns(tbl5,{"Lower", "Upper"}),
    Result = Table.TransformColumns(tbl6, {"Ideal", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
    Result

 

[ooptennoort]

Wwwwoww! Its that list.generate that is the esoteric part I noticed you also check which of the two is the larger one (swapping these does not change the ascending order of the intervals)! Well done! Thank you!!!!!

(Now I am beginning to wonder what Great result (my excel formula in M) would look like. Should you want to give it a try... by all means I would still make use of it (just less than the Ideal result (wow)!)

 

[JGordon11]

List.Generate is a good function to learn. There are several tutorials on it from a google search.

The tbl5 step below adds the Great column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    /* Function ShortCuts */ NRD = Number.RoundDown, NRU = Number.RoundUp, NTT = Number.ToText, NIE = Number.IsEven, TAC = Table.AddColumn,
    tbl1 = Table.TransformColumnTypes(Source,{{"First", type number}, {"Second", type number}}),
    tbl2 = TAC(tbl1,"Lower", each if _[First]< _[Second] then NRD(_[First]) else NRD(_[Second])),
    tbl3 = Table.TransformColumns(tbl2, {"Lower", each if NIE(_) then _ else _ -1}),
    tbl4 = TAC(tbl3,"Upper", each if _[First]< _[Second] then NRU(_[Second]) else NRU(_[First])),
    tbl5 = TAC(tbl4,"Great", each let n = NRD((_[First]+_[Second])/2) in if NIE(n) then NTT(n) & "-" & NTT(n+2) else NTT(n-1) & "-" & NTT(n+1)),
    tbl6 = TAC(tbl5, "Ideal", each List.Generate(() => _[Lower], (x)=> x<_[Upper] or x=_[Lower], (x)=> x+2, (x)=> NTT(x) & "-" & NTT(x+2))),
    tbl7 = Table.RemoveColumns(tbl6,{"Lower", "Upper"}),
    Result = Table.TransformColumns(tbl7, {"Ideal", each Text.Combine(_, ";"), type text})
in
    Result

 

[ooptennoort]

What!??!!! Wow!!!! This is amazing. So different from what I was initially thinking it would look like (the Great result method)! I sort of recognise a let um function in a let function using function shortcuts. Are these variables (I may have seem some of this advanced M before)?
Wow. Thank you!!! Will study this (Puls & Escobar's new book is out, finally... not that I had time to read their 1st e.d. ;p but still)! Thanks again. Besides it helping me very much, and it showing this forum does definitely have an M expert, it underlines that Power Query can compete with VBA!