CODEWITHSUNDEEP
excelpowerquery
Maurizio
Maurizio

Reputation: 189

Power Query - Check if source value is in another list/table

I'm experimenting with PowerQuery and I got to a good point, but I'm stuck with something. I checked here and other places, but couldn't find anything that helped me solve the issue.

I have a Source in PQ I can use and transform. Once this Source (a .csv) is loaded in PQ editor, I would need to verify if a specific column value is in another table/list or not.

So I created a small table in another sheet and created another Source for it.

I am trying to create a new column and validate now with "each if" if the current main Source value in that column (that obviously varies, they are names) is part of the list/table that is the other Source. And in case it is, the added column value will be YES, otherwise NO.

A simple check that in Excel I would have done probably with vlookup.

I always referred here as list/table as i tried to have it created as List or table, without success.

Is anybody here able to help?

= Table.AddColumn(#"Promoted Headers", "Real_A", each if Table.ContainsAny(Source, EMEAL, {[Analyst]=[EMEA]}) then "EMEA" else "XX")

This is what I tried last. The "Source" is the .CSV I read already and [Analyst] is one column of it. [EMEA] is the name of the column in EMEAL (list).

The error I have:

Expression.Error: We cannot convert the value "Pippo" to type Record.
Details:
Value=Pippo
Type=Type

"Pippo" is the value in [Analyst] column, but as well one of the value in the EMEAL list... so, also here quite confused where the issue really is.

thanks!

Upvotes: 0

Views: 6318

Answers (1)

ImkeF
ImkeF

Reputation: 1588

The equivalent to this kind of VLOOKUP is a Merge-opertion like described here: http://www.myonlinetraininghub.com/excel-power-query-vlookup

Upvotes: 1

Related Questions