Cross referencing two tables

[Scravers]

So I've been stuck on this for quite a while, but currently I have two tables. Company Dates and Employees.

Company Dates contains multiple rows, each containing one of four different Companies, and each row being identified by a dates

Each row in Employees has a employee name to identify the row, along with each employee having one of the four companies in their row.

What I cant seem to figure out is how to create an virtual column in Employees that will return a list of every date that links to that employees company.

For example:

Google has three rows identified by the following dates in the Company Dates table. 01/01/99, 02/02/99 and 03/03/99.

Steve is in the Employee table and his company is Google.

Steve now has a virtual column, that has a list of 01/01/99, 02/02/99, 03/03/99

Any help would be appreciated!

[SkrOYC]

I guess there is also a "Company" table, and it should have a [Related Company Dates] virtual column which has all of the rows from "Company Dates" table that are related to the current Company.

Then, from your "Employee" table just do a dereference expression to the company's related column.

Virtual Column of type List on your Employee table:

[RefToCompany].[Related Company Dates]

[Scravers]

No there isnt a Company table with all the rows of the dates that relate to each company. If I were to create a new table that has that, how would I go about collecting each row and putting it to a company? Would I have to use SELECT()?

[SkrOYC]

Your "Company Dates" table has a [Company] column? If it does, where that data comes from?

[Scravers]

It comes from a google form, thats entered by the company

[SkrOYC]

Yikes, so you can have unlimited Companies?

[Scravers]

Yes 😕

[JPAlpano]

Yes. You will have to do a select.

SELECT(Table[DateColumn], [Company]=[_THISROW].[COMPANY])