Power Query Look Up Value From Another Table Based On Two Criteria

[Anonymous]

Hello. I am not that expereinced with Power Query so need some help please.

I have a deails table that has over 5M rows with multiple rows using the same key. I have already referenced that table to create a summary table and merged it with another table for added information.

Now I want to lookup a value from the details table based on criteria.

In the image below, I am trying to populate the column AAAA Answer in the summary table based on

IF "Summary Table" [Category] = "People"

THEN return the value in "Details Table"[Answer] for the rows that have "AAAA" in the column "Details Table"[Question]

Thanks in advance.

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUSpIzS/ISQUyTJRidaKVTM3MLUCiOYnJCEFLA0MjTKXGJqZmqEpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Category = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Category", type text}, {"Count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Category]="people" 
then
Table.SelectRows(
Table.SelectRows( DETAILSTABLE, each [Question] = "AAAA" ),
((x)=>x[Key]=[Key]))[Answer]{0}
else " ")
in
    #"Added Custom"

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Keezz]

A bit cleaner without the nested Table.SelectRows:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
   each 
     if [Category]="people" then
        let
           search_key = [Key]
        in 
           Table.SelectRows( DETAILSTABLE, each [Question] = "AAAA" and [Key]=search_key))[Answer]{0}
      else 
         null
)

Retruning a space is not a good practice, use null instead. If nulls mess up queries down the road, use an empty string "".

and if you are sure there is always only 1 answer retrunred:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
   each 
     if [Category]="people" then
        let
           search_key = [Key]
        in 
           DETAILSTABLE{[Question = "AAAA", Key = search_key]}[Answer]
      else 
         null
)

[amitchandak]

@Anonymous , Try a measure like

calculate(Max(Table[Answer]), filter(Table, Table[Question] ="AAAA"))

[Anonymous]

Thanks @amitchandak but I am not looking for a measure. I need it as a column in the table, but I do not want to use a calculated column becuase it will impact performance of the report. This is why I am looking for a solution in the query editor. Any thoughts?