Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Mikey_surreall
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
v-jingzhang
Community Support
Community Support

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))

vjingzhang_0-1676018807754.png

 

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

View solution in original post

3 REPLIES 3
Mikey_surreall
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

Yes, you are correct! 

v-jingzhang
Community Support
Community Support

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))

vjingzhang_0-1676018807754.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors