Forum Discussion

Hambach's avatar
Hambach
Frequent Visitor
4 years ago
Solved

Using column value as column reference

Hello everyone: hopefully someone can help me with this (probably for you super obvious) problem.   I have 2 tables: 1. with my master data and a lots of parameter columns identified by their ID ...
Need Help
  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    4 years ago

    Hi Hambach ,

     

    The missing puzzle is the reference table. Therefore, I mocked up one to show you how the initial provided works.

     

    Reference Table (mock up)

    Query Name: ReferenceTbl

     

    Main table (provided sample)

    Query Name: MainTbl

    Code:

    Table.AddColumn(#"Renamed Columns", "Customn Column (the result)", each Table.SelectRows(ReferenceTbl, (x)=> x[Object_ID] = [Object_ID_MainTbl] and x[Object] = [Object_MainTbl])[ReferenceColumn]{0})

     

    ReferenceTbl = Reference Table

    x[Object_ID] = [Object_ID_MainTbl] = 1st criteria (x[Object_ID] means each cell under [Object_ID] column from reference table = the value from [Object_ID_MainTbl] from main table.

    x[Object] = [Object_MainTbl] = 2nd criteria (x[Object] means each cell under [Object] column from reference table = the value from [ObjectMainTbl] from main table.

    [ReferenceColumn] = whatever column you want from the reference table.

     

    *You can add more criteria

     

    The above code returned a single column, whereas the code below returns a table, and you can expand multiple columns.

    Table.AddColumn(#"Renamed Columns", "Customn Column (the result)", each Table.SelectRows(ReferenceTbl, (x)=> x[Object_ID] = [Object_ID_MainTbl] and x[Object] = [Object_MainTbl]))

     

    The codes are similar, we dropped "[ReferenceColumn]{0}" from the single column code.

     

    I hope it makes sense and helps.

     

    Regards

    KT

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Impactful Individual
4 years ago

Hi Hambach ,

 

If you are referencing from another table, the formula for the output table can be:

(Please replace the highlighted text with the correct table name)

Table.SelectRows(ReferenceTableName, (x)=> x[ReferenceTableColumnName] = [#"Object ID"] and x[ReferenceTableColumnName] = [Object] )[TheColumnNameFromTheReferenceTableThatYouWantToReturn]

 

Above formula may return you as a list is multiple value match. Then you can add List.Max(AboveFormula) or List.Min(AboveFormula) or List.Sum(AboveFormula) or List.Count(AboveFormula) to get the value you want.

 

Regards

KT

Hambach's avatar
Hambach
Frequent Visitor
4 years ago

Sorry if I was unclear or just didn't understand you. In the End I still want to use the full table I posted above, just with the Correct values in the Output Column, so just instead of 2,4 and 1, I will now have 7, 11 and 9 in the rows. Is there not some sort of easy formular I can use in a custom column to just use this value as a the column ID to be referred to and to look up the value in the same row? 

 

Since there will probably be multiple "Output" Columns (Output1, Output2) columns.