cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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.

1 ACCEPTED SOLUTION
Community Support

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 3
New Member

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

Community Support

Yes, you are correct!

Community Support

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.