Forum Discussion
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
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
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
Thank you for your great help!
As far as I understood you actualy have the results already in the reference table, and just looking it up for your main table through the Objects ID=Object ID. This is not the case in my data. The reference table does not know the actual value, just the column name in which to find it in the maintable. The reference table essentially is just this:
Object_ID Parameter_column 1 2 2 4 3 1 So in words, the reference table tells me that for my result column in the maintable i need to look up the value in column [2] for Object_ID=1 (results in value of 7), column [4] for Object_ID=2 (results in value of 11) and so on.
If I use your code it will just insert the number for the column I want to look up (the Parameter_column number from the reference table), instead of the actual value in this column in the main table.
To make things (maybe harder) sometimes its not just a number for the column but also a string (like 5c for example). It is noted like this in the reference table as well alteast.
Again, thanks for your help!
I think I did it now with the
=Table.AddColumn(Source, "Custom", each Text.From( Record.Field( _, [ReferenceColumn])))
formular.
If you know an easier way (for the CPU), feel free to let me know.
Still, thank you very much for you help and understanding!
