Weird table creation for DAX gurus

[KHorseman]

I am really beating my head against a wall on this one. Here's the scenario:

In this table I have 2 fields. Call them "Interviewer" and "Contractor". Interviewer is a field with user's full names. Contractor...was designed by an idiot. It's a picklist of first names of users, plus one extra name that isn't a user*. Contractor is sometimes blank when Interviewer is not. Interviewer is never blank. When it is not blank, Contractor usually matches the first name of Interviewer, but not always. Sometimes it's a different person's name and sometimes it's that one extra name. It looks something like this:

Interviewer	Contractor
Stephen Fry	Stephen
Tim McInnerny	Rik
Rowan Atkinson	Rowan
Stephen Fry	Stephen
Hugh Laurie	Hugh
Rowan Atkinson	Rowan
Tony Robinson	Tony
Tim McInnerny	Rowan
Tony Robinson	Tony
Tim McInnerny	Tim
Hugh Laurie
Stephen Fry	Stephen
Hugh Laurie	Hugh
Stephen Fry
Miranda Richardson	Miranda
Rowan Atkinson	Rowan
Rowan Atkinson	Rik
Stephen Fry	Stephen
Tony Robinson	Rowan
Tony Robinson	Rik
Rowan Atkinson
Tim McInnerny	Tim
Rowan Atkinson	Rowan
Miranda Richardson	Miranda
Miranda Richardson	Miranda
Tony Robinson	Tony
Tim McInnerny	Rik
Tony Robinson	Tony
Rowan Atkinson	Rik
Rowan Atkinson
Tim McInnerny	Tim
Miranda Richardson	Miranda
Rowan Atkinson	Rowan
Rowan Atkinson	Rowan
Miranda Richardson	Miranda
Hugh Laurie	Hugh
Rowan Atkinson	Rowan
Tim McInnerny	Tim
Rowan Atkinson	Rowan
Tony Robinson
Tony Robinson	Tony
Tony Robinson	Tony
Tim McInnerny	Tim
Tim McInnerny	Rik
Rowan Atkinson	Rowan
Tim McInnerny
Rowan Atkinson	Rowan

I need to create a second table that gives me all the contractor full names in one column with the matching interviewer first name, plus that one extra name. So the end product would look like:

Interviewer	Contractor
Rowan Atkinson	Rowan
Stephen Fry	Stephen
Hugh Laurie	Hugh
Tony Robinson	Tony
Tim McInnerny	Tim
Miranda Richardson	Miranda
	Rik

I can't do this in the query editor for reasons I won't get into. I can't get there with anything as simple as SUMMARIZE because of the missmatch rows and the fact that there is never a row with a blank Contractor and "Rik" under Interviewer. I am at a loss.

@OwenAuger @Greg_Deckler @MattAllington@Seth_C_Bauer @Anonymous save me!

*This is a little white lie. There are actually multiple names like this, but to keep my example simple I'm claiming there's just 1. Also the roster of names in both columns is constantly changing so it's not as if I can hard-code some name values into a filter formula.

[Vvelarde · Accepted solution]

@KHorseman

I think i got it.

In your main table add 2 columns:

Contractor equal to Interviewer =
IF (
    LEFT ( Table1[Interviewer], FIND ( " "; Table1[Interviewer] ) - 1 )
        = Table1[Contractor],
    1,
    0
)

ExistContractorinallIntervierwersColumn =
COUNTROWS (
    FILTER (
        Table1,
        Table1[Contractor equal to Interviewer] <> 0
            && Table1[Contractor] = EARLIER ( Table1[Contractor] )
    )
)

And a New Table:

TheTable =
UNION (
    SUMMARIZE (
        FILTER ( Table1, Table1[Contractor equal to Interviewer] = 1 ),
        Table1[Contractor],
        Table1[Interviewer]
    ),
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[Contractor equal to Interviewer] = 0
                && Table1[Contractor] <> BLANK ()
                && Table1[ExistContractorinallIntervierwersColumn] = BLANK ()
        ),
        Table1[Contractor],
        "Interviewer", BLANK ()
    )
)

* I just a Fred to testing.

[KHorseman · Accepted solution]

OK I think I've got this figured out now. @Vvelarde I pretty much just compressed your solution into a single godawful table formula. It ain't pretty, but it does the job.

UNION(
	SUMMARIZE(
		FILTER(
			ADDCOLUMNS(
				VALUES(TableName[Contractor]),
				"IntvCheck",
				VAR conname = FIRSTNONBLANK(TableName[Contractor], 1)
				RETURN CALCULATE(
					COUNTROWS(
						FILTER(
							ALL(TableName),
							NOT(ISBLANK(TableName[Interviewer])) &&
							LEFT(
								TableName[Interviewer],
								FIND(" ", TableName[Interviewer]) - 1
							) = conname
						)
					)
				),
				"Interviewer",
				BLANK()
			),
			[IntvCheck] = 0 &&
			NOT(ISBLANK(TableName[Contractor]))
		),
		[Interviewer], [Contractor]
	),
	ADDCOLUMNS(
		SUMMARIZE(
			FILTER(
				TableName,
				NOT(ISBLANK(TableName[Interviewer]))
			),
			TableName[Interviewer]
		),
		"Contractor",
		LEFT(
			[Interviewer],
			FIND(" ", [Interviewer]) - 1
		)
	)
)

That might be the most duct-tape DAX formula I've ever seen.