locked
Column lookup to return value RRS feed

  • Question

  • Hey all,

    Novice user here :)

    I'm trying to get my head round what I'm trying to achieve.  Below is a crude mockup. In the "Cat" Columns there should be only 1 result and the other "Cat" columns should be null.  I need Excel Power query to check "Main" and then pull the information from the corresponding category.  Ideally it would delete the other values from the false categories. 

    I've tried exploring custom columns as some sort of VLookup but I'm hopelessly lost.

    Main  | Cat 1 | Cat 2 | Cat 3 |

    Cat 1 | Result| null   | null    |

    Cat 1 | Result|Result|null     |

    Cat 2 |null     |Result|result  |

    Cat 3 |null     |result |         |

    Tuesday, May 16, 2017 10:21 AM

Answers

  • Hi macpaulos. Yes, you can definitely do this. Just add a custom column, and in the custom column dialog, paste the following formula:

    Record.Field(_, [Main])

    You can then delete the Cat 1/2/3 columns if you like.

    Ehren


    Wednesday, May 17, 2017 6:49 PM

All replies

  • Does the following example code provide the results you want? (you will need to change the table name from "Table2" to the name of your Excel table)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Main", type text}, {"Cat 1", type text}, {"Cat 2", type text}, {"Cat 3", type text}}),
        ReplacedValues = Table.FromRecords(
                          Table.TransformRows(
                              ChangedType, 
                              (row) => 
                              Record.TransformFields(
                                  row, 
                                  {
                                     {Record.FieldNames(row){1}, each if row[Main] = Record.FieldNames(row){1} then "Result" else null},
                                     {Record.FieldNames(row){2}, each if row[Main] = Record.FieldNames(row){2} then "Result" else null},
                                     {Record.FieldNames(row){3}, each if row[Main] = Record.FieldNames(row){3} then "Result" else null}
                                  }
                              )
                           )
                       )
    in
        ReplacedValues

    Tuesday, May 16, 2017 6:22 PM
  • Hi macpaulos. Yes, you can definitely do this. Just add a custom column, and in the custom column dialog, paste the following formula:

    Record.Field(_, [Main])

    You can then delete the Cat 1/2/3 columns if you like.

    Ehren


    Wednesday, May 17, 2017 6:49 PM