power query cycle through data in another table using M Formulas

[Mikey_surreall]

HI All

I can't wrap my head round this. In Trasform data, add custome column .. m query.

I have table A, with values like 157, 975, 433 in Col A.

I have table B, with values like 150, 250, 450, 600, 1000 in Col B

What i'm trying to do is:

Add a column to table A that takes the value e.g. 157, then loops through table B Col B looking for if "157 >= table b[ColB] - 20 && 157 <= table b + 20" then return 150 from Table B Col B and put in the new column in Table A

so:

157 would return 150

975 would return 1000

and 433 would return 450

and null if nothing found

I hope that makes sense?

Any help would be greatly appreciated.

[Mikey_surreall]

You are a genius! Thank you so much for that, worked a treat.

List.select, gets all the occurences? And then list.first selects the first of that list?

Rgds

Mikey

[v-jingzhang]

Yes, you are correct! 

[v-jingzhang · Accepted solution]

Hi @Mikey_surreall 

You can add a custom column in Table A with below code. Notice that 975 would not return 1000 as "1000 - 975 = 25". 

let pColAValue = [Col A] in List.First(List.Select(#"Table B"[Col B], each pColAValue >= _ - 20 and pColAValue <= _ + 20))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.