Forum Discussion
Create new table from specific column/data from two different existing tables
Hi, I want to create a new table to extract specific data that I have created in existing tables. There are 2 tables: [zhpla 2022] contains position info [Flexi] contains employee info Basic...
If the relationship between the tables is one-to-one or many-to-one then you can use RELATED. If it it is one-to-one then it doesn't matter which you use as the base for the SELECTCOLUMNS, if it is many to one then you would need to use the many side as the base, e.g.
New Table = SELECTCOLUMNS( 'Many side',
"col from many side", 'Many side'[col from many],
"col from one side", RELATED('One side'[col from one side])
)If the relationship is many-to-many, or there is no relationship, you will have to use LOOKUPVALUE, e.g.
New Table = SELECTCOLUMNS( 'Table A',
"col from A", 'Table A'[col from A],
"col from B", LOOKUPVALUE('Table B'[col from B], 'Table B'[column to search], 'Table A'[value to look for])
)
Hi, thanks for responding.
But im a bit confused with the lookup parameters here:
LOOKUPVALUE('Table B'[col from B], 'Table B'[column to search], 'Table A'[value to look for])what the difference between [col from B] and [column to search]
"Job Name", LOOKUPVALUE('zhpla 2022'[Job Name], 'zhpla 2022'[Position Code], 'Flexi'[Title vs Gender] = "Invalid")
I tried this but it doesnt work. How do I select the value with specific filter?
Can you give an example on how to get the value with specific filter usin lookup?
"Job Name", LOOKUPVALUE('zhpla 2022'[Job Name], 'Flexi'[Position Code], 'zhpla 2022'[Position Code], 'Flexi'[Title vs Gender], "Invalid")Still doesnt work.