Using column value as column reference

[Hambach]

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 

and 2. reference table with the Object_ID and their respective Parameter ID that should be used.

I have been following this tutorial: 

https://www.youtube.com/watch?v=U97r2_O8IyI&ab_channel=MyOnlineTrainingHub

So far so good, but now I have in my Output column the ID of the column that should be used, instead of the value in this column.

Object_ID	Object	1	2	3	4	Output
1	A	3	7	9	8	2
2	B	5	9	4	11	4
3	C	9	4	3	4	1

So the output should read for Object A = 7, Object B = 11 and  Object C = 9.

Looking forward to your help!

[KT_Bsmart2gethe]

Hi @Hambach,

It will be much easier If you have the sample data with sensitive information removed and the expected outcome. 

I can then provide you step by step to achieve the outcome you want.

Regards

KT 

[Hambach]

Thank you for your quick response. Unfortunately the whole table (1k+ rows, 200 columns) is sensitive data. So I would prefer to stay with the example if that is ok.

I thought this was a quicker fix than it seems to be. Right now me table is the following with the referencing part from another table already done (I renamed "Output" column in this example since it might be confusing):

Object_ID	Object	1	2	3	4	Referenced from other table	Customn Column (the result)
1	A	3	7	9	8	2
2	B	5	9	4	11	4
3	C	9	4	3	4	1

My hope was that I just could use a simple custom column formula in the final column that would lookup the value in the respective column. So some sort of = [ [ Referenced from other table ] ]. So using the value of the column as the new selected column

Expected Outcome:

Object_ID	Object	1	2	3	4	Referenced from other table	Customn Column (the result)
1	A	3	7	9	8	2	7
2	B	5	9	4	11	4	11
3	C	9	4	3	4	1	9

The "referenced from other table" column I could just delete afterwards I guess...

Thank you for your help

[KT_Bsmart2gethe]

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

[KT_Bsmart2gethe · Accepted solution]

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

[Hambach]

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!

[Hambach]

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!

[Hambach]

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.