New column with max values from table 2 based on conditions in columns in table 1 dinamically

[Anonymous]

Hi everyone,
I am a recent user (I am not a programmer) of Power Query with Excel as a tool for data manipulation. Now I am stuck with a step which has taken my several days of google searching with no success. As a result of that search I found you in the Power BI community and I believe you may help me based on your successful background in the community. Please let me introduce you my case:
I have two query tables, table1 has several fields a shown in figure 1. I am trying to add a new column whose values depend of the other columns dynamically. Each cell in this column is the max value in other table’s column, named “Value”. For that purpose, I first filter Table2, based on four conditions. Then I get the max value by using List.Max (See formula below). For filtering table2 I have used static values, however, I’d wish to use values contained in each row of table1’s columns. It can be said that Table 1 is a subset of Table 2, where I managed to create a StartDate Column to get a data range. Then a Max value between two dates from Table 2's column “Value” can be obtained. 

Table 1

(Condition 1)	(Condition 2)	(Condition 3)	(Condition 4)	Result
Column1	Column2	StartDate	EndDate	MaxMinValue
Data1	A	19/03/2020	19/03/2020	3.4195
Data1	B	20/03/2020	09/04/2020	3.4195
Data1	B	16/01/2020	20/03/2020	3.4195
Data1	C	21/11/2019	16/01/2020	3.4195
Data1	C	18/11/2019	21/11/2019	3.4195
Data1	C	15/10/2019	18/11/2019	3.4195
Data2	D	08/03/2019	15/10/2019	3.4195

Formula to add a new column:

Table.AddColumn(Source, "MaxMinValue", each List.Max(Table.SelectRows(Table2, each [Column1]="Data1" and [Column2] = "A" and [Date] > #date(2020,2,26) and [Date] <= #date(2020, 4,9))[Close]))

Table 2

Column1	Column2	Date	Value
Data1	A	09/04/2020	20
Data1	A	08/04/2020	19.695
Data1	A	07/04/2020	19.845
Data1	B	06/04/2020	18.17
Data1	B	03/04/2020	16.9
Data1	C	02/04/2020	17.195
Data1	C	01/04/2020	16.985
Data2	D	31/03/2020	17.815
Data2	D	30/03/2020	16.29

I would appreciate your valuable help since all my tries have failed. Thank you in advance.
Regards
Julian, @Stachu , @ImkeF , @Greg_Deckler 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Check steps below:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-juanli-msft very much for your valuable help.

Regards

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[Anonymous]

Sure. There it goes. Thank you

[ImkeF]

Hi @Anonymous 

reading through your request, that sounds like a circular dependency, but maybe I'm wrong.

Please provide a spreadsheet sample with the desired logic. Especially providing the values of the desired new column in Table1.

[Anonymous]

Hi. I found a solution.

Thank you for your interest in replying my request.

Julian