Using column value as column reference

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