Using Table.SelectRows with condition from value in current row

[josemariogomes]

What i'm trying to do is the following:

. i have a table named #"Coordinates" which contains a contains 2 columns - [Coord NE Lat] and [Coord NE Long]

. i have another table named #"Unit Price" which contains 3 columns - [Unit Price], [Lat] and [Long]

. i'm trying to add a column to the table #"Coordinates" in which every row contains a list from the #"Unit Price"[Unit Price] column in which a condition is met

. what i'm doing to add this column to the #"Coordinates" table is:

          Table.AddColumn(#"Coordinates", "Selected List", each Table.SelectRows(#"Unit Price", each ([Lat] > [Coord NE Lat]) )[Unit Price] )

The problem is that in the Table.SelectRows(), since i'm executing this function with the table #"Unit Price", i can't reference the element in each row from the column #"Coordinates"[Coord NE Lat].

Can anyone help me with this?

Kind regards,

Jose Mario Gomes

[artemus]

The keyword each is shorthand for:

(_) =>

and [ColName] is shorthand for _[ColName]

As a result you can replace one of the each keyword with:

(row) =>

And reference a value in the row with:

row[MyColumn]

[Nathaniel_C]

Hi @josemariogomes ,
Sometimes I will merge the two tables, or copy them and merge. Then delete whatever is extra.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[josemariogomes]

Hi @Nathaniel_C

Thank you for the response.

I've tried to merge the two tables. The problem with this is that i can only set matching condictions, not estblish > or < relations.

I've managed to solve this with the following:

      Table.AddColumn(#"Coordinates", "Selected List", each Table.SelectRows(#"Unit Price", (NELat = [Coord NE Lat]) => each ([Lat] > NELat) )[Unit Price] )

But a new problem rises, which is the time it takes to calculate.

What i'm trying to do is what we can do with the functions like averageif() in Excel. For that, in PQ what i've done is for each row i create a list with the function above. Than i do the average of that list in every row.

The problem is that with Excel this is much faster then with Power Query, and i can't understand why.

Thank you,

Jose Mario Gomes

[dax]

Hi josemariogomes,

If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[artemus]

The PQ equivelent of average if is:

Table.AddColumn(PreviousStep, "New Column Name", each List.Average(List.Select(OtherTable[ColumnToAverage], (x) => [ColumnInCurrentTable] > x)))