Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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.

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Try a measure like

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
      Keezz
      Helper I
      2 years ago

      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
)