Separate text in column, keep it linked to a unique value to add it as a filter in direct query mode

[Susan_Colyn]

Hi there,

I have a column called Work Performed By which has all the names of our engineers who work on a certain job.  The job will have a work code.  I would like to separate the names but keep a link / relationship to the work code.  The users would like to filter on an engineer's name to see which job they completed.

Please let me know if it is possible to do this in direct query mode and how.

Here is an example of the data

Work Performed by	Work Code	Hours
Joe Soap, Eddie Nixon	S002622126	10
Peter Smith, Ken Hardy, Steven Green	S00258369	80

Thank you,

Susan

[Greg_Deckler]

@Susan_Colyn Split the Work Performed by column in Power Query and then unpivot the resulting columns.

[Susan_Colyn]

Dear Greg,

This is a direct query though, and does not have data in the power query.