Forum Discussion
power query cycle through data in another table using M Formulas
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.
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.
3 Replies
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.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
Yes, you are correct!