Unanswered

Get an if function to step through a second table

(1) Share

Report

Posted on by nzfletch

Hi all. I'm going a bit crazy with this so I'm really hoping someone can help. I have two tables and I'm trying to run an if statement that uses both.

I have a table of values in Table2 and the source data in Table1. I want the if statement to run through on every row of Table1 and check each row of Table2 to return a value if the conditions are met. The else is just to show any nonstandard cases.

I'm either missing something about using a list, or it can't be done. Below is what I have in the query but I just get an error stating that the >= can't be used with a list. I'm sure there's a simple answer, and that answer might be that it can't be done and I have to join the tables but I have to do this with a very long list and 5 different tables so I'm trying to avoid joining them.

Please help!?

#"new column" = Table.AddColumn(
#"previous step",
"New Column",
each
if (#"previous step"[Col1] >= Table2[Min] and #"previous step"[Col1] < Table2[Max])
then Table2[Value]
else 55
)

Categories:

Power Query

I have the same question (0)

All responses (2)

Answers (0)

Sort by

teylyn1 362 on at

Like (1)

Report
Copy link

Link copied!

Hi,

I think what you're after is an approximate match, but that is not easily done in Power Query. Mynda Treacy has a blog post and a video on a similar scenario, using Excel tables as input for Power Query. Take a look at
https://www.myonlinetraininghub.com/power-query-approximate-match-vlookup

It is a series of steps that include a full outer merge and additional steps to fill in the blanks.

Was this reply helpful? Yes No
nzfletch 5 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the reply. That solution involves merging/joining the tables which is what I was trying to avoid. Looks like I might just have to do that though.

Was this reply helpful? Yes No