Min, Max function in power query

[thomas819]

Hello all,
I would like ask you for a help.

This table is very short demonstration of real table which is over 50 000 rows long.

Real table is created in power query from multiple txt files and it is connection only.


This is second table which I need to complete. This table is also connection only.
I need to find out minimum and maximum value base on followinf conditions.

For Case1 range days 5 -9 including, Case2 range days 8 - 17 and so on.
This is the result which I am loking for:

Solution in excel is pretty easy, but I need create a solution in power query.
Excel solution for
Case1 Min

=MIN(INDIRECT(ADDRESS(G3;2)):INDIRECT(ADDRESS(H3;2)))

Case1 Max

=MAX(INDIRECT(ADDRESS(G3;3)):INDIRECT(ADDRESS(H3;3)))

Unfortunatelly I can't create List.Max function which accept indirect(address():indirect(adrress() as input.
Can you please help me?

 

[JGordon11]

This code is pretty fast because it takes advantage of the Day column starting at 1 and incrementing by one each row. If your actual table Day column is the same (i.e., starts at 1 and increments by 1) then this will work. If not, then a modified and slower code would be required.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst1 = List.Buffer(List.Zip(List.Skip(Table.ToColumns(Source)))),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.AddColumn(tbl2, "Rcd", each 
            let 
                from = [From],
                to = [To],
                lr = List.Zip(List.Range(lst1, from-1, to - from +1)),
                min = List.Min(lr{0}),
                max = List.Max(lr{1})
            in   
                [Minimum = min, Maximum = max]
    ),
    Result = Table.ExpandRecordColumn(tbl3, "Rcd", {"Minimum", "Maximum"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table1				Table2			Query Output
2
3	Day	Minimum	Maximum		From	To		From	To	Minimum	Maximum
4	1	2	13		6	7		6	7	9	17
5	2	4	7		4	11		4	11	1	17
6	3	10	13		10	11		10	11	6	12
7	4	6	14		9	13		9	13	1	13
8	5	6	9		16	20		16	20	2	12
9	6	9	16
10	7	10	17
11	8	6	11
12	9	1	13
13	10	7	12
14	11	6	12
15	12	3	11
16	13	5	7
17	14	10	20
18	15	4	5
19	16	5	12
20	17	6	10
21	18	3	7
22	19	2	5
23	20	2	11
24	21	1	7
25	22	2	13
26
Sheet1

 

[thomas819]

This code is pretty fast because it takes advantage of the Day column starting at 1 and incrementing by one each row. If your actual table Day column is the same (i.e., starts at 1 and increments by 1) then this will work. If not, then a modified and slower code would be required.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst1 = List.Buffer(List.Zip(List.Skip(Table.ToColumns(Source)))),
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.AddColumn(tbl2, "Rcd", each
            let
                from = [From],
                to = [To],
                lr = List.Zip(List.Range(lst1, from-1, to - from +1)),
                min = List.Min(lr{0}),
                max = List.Max(lr{1})
            in  
                [Minimum = min, Maximum = max]
    ),
    Result = Table.ExpandRecordColumn(tbl3, "Rcd", {"Minimum", "Maximum"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table1				Table2			Query Output
2
3	Day	Minimum	Maximum		From	To		From	To	Minimum	Maximum
4	1	2	13		6	7		6	7	9	17
5	2	4	7		4	11		4	11	1	17
6	3	10	13		10	11		10	11	6	12
7	4	6	14		9	13		9	13	1	13
8	5	6	9		16	20		16	20	2	12
9	6	9	16
10	7	10	17
11	8	6	11
12	9	1	13
13	10	7	12
14	11	6	12
15	12	3	11
16	13	5	7
17	14	10	20
18	15	4	5
19	16	5	12
20	17	6	10
21	18	3	7
22	19	2	5
23	20	2	11
24	21	1	7
25	22	2	13
26
Sheet1

Hi JGordon,
you blow my mind away

Thank you very much for your answer.

However... Can you please tweak your code a little bit?
Table1
column Day is "M1 Index" in my table (9th column) position can vary
column Minimum is "Low" in my table (6th column) position can vary
column Maximum is "High" in my table (5th column) position can vary

Table2
column From is "M1 Index" in my table (4th column) position can vary
column To is "exit.M1 index" in my table (12th column) position can vary

 

[thomas819]

Hi JGordon,
you blow my mind away

Thank you very much for your answer.

However... Can you please tweak your code a little bit?
Table1
column Day is "M1 Index" in my table (9th column) position can vary
column Minimum is "Low" in my table (6th column) position can vary
column Maximum is "High" in my table (5th column) position can vary

Table2
column From is "M1 Index" in my table (4th column) position can vary
column To is "exit.M1 index" in my table (12th column) position can vary

Hi, I understand your code and I changed it accordingly.

Thank you once more.