cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

need help in Lookup function

Hi All,

I have data like below table 

Screenshot_7.png

I want to create "Is Predicted" Filed based on the below condition by using M query 

  • If "Actual value" is present in "Possible value" then yes else No

Screenshot_8.png

 

Sample data:

IDActual ValuePossible ValueIs Predicted
ID1ABYes
ID1AAYes
ID1ACYes
ID2EBNo
ID2EANo
ID2ECNo
ID3CBYes
ID3CAYes
ID3CCYes
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: need help in Lookup function

@vengadeshpalani 

 

As a custom column, you can use

"Changed Type" is just the previous step's name. You have to change it according to your last step in the Query Editor

 

=let myID=[ID] in
let mylist=Table.SelectRows(#"Changed Type",each [ID]=myID)[Possible Value]
in
if List.Contains(mylist,[Actual Value]) then "Yes" else "No"

PQy.png

View solution in original post

3 REPLIES 3
Highlighted
v-cherch-msft Super Contributor
Super Contributor

Re: need help in Lookup function

Hi @vengadeshpalani 

You may create a calculated column with DAX as below:

Column = 
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Possible Value] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
RETURN
    IF ( 'Table'[Actual Value] IN a, "Yes", "No" )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: need help in Lookup function

Thanks, 

 

Is it possible in M query editor? 

Super User
Super User

Re: need help in Lookup function

@vengadeshpalani 

 

As a custom column, you can use

"Changed Type" is just the previous step's name. You have to change it according to your last step in the Query Editor

 

=let myID=[ID] in
let mylist=Table.SelectRows(#"Changed Type",each [ID]=myID)[Possible Value]
in
if List.Contains(mylist,[Actual Value]) then "Yes" else "No"

PQy.png

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 205 members 2,335 guests
Please welcome our newest community members: