Instert Row based on Condition

[ghe7549]

I have two tables that are tied by COID.  I have a list of Vacant posistions per COID and i need to add this row to the table that has the employees.

Table 1 Vacant Posistion:

Vacant

COID

Facility

Change

Vacancy Date

DFM Start Date

Table 2 Employees:

Division

COID

Facility

Employee Name

Job Desc

Pay

[parry2k]

@ghe7549 can you provide sample data and expected output.

[ghe7549]

@parry2k the second table below and highlighted green is what i need to add into table 1

Division		COID		Facility		Employee Name		Job Description		Yearly Pay
SC DIVISION		12345		Harbor Town		Tom Thomas		Supv Fac Mgmt/Safety/Sec		$57,138
SC DIVISION		12345		Harbor Town		Brad Sellers		Coordinator Support Svcs (CW)		$52,000
SC DIVISION		12345		Harbor Town		Morgan Evans		Engineering Craft Worker		$44,554
SC DIVISION		12345		Harbor Town		Sam Smith		Engineering Tech Specialist		$44,138
SC DIVISION		12345		Harbor Town		Vacant		Dir Fac Mgmt/Safety/Sec		xxxxxx
Vacant		COID		Facility		Change		Vacancy Date		DFM Start Date
Vacant		12345		Harbor Town		Left HCA		6/14/2019		12/20/18
Vacant		54635		Tri County		Left HCA		4/9/2018		9/17/18
Vacant		22448		Macon		Left HCA		4/20/2020		7/2/18
Vacant		34854		Table Top		Left HCA		3/20/2020

[parry2k]

@ghe7549 how the table 1 columns are mapped to table 2, you can use UNION DAX function or you can append queries in Power Query

[ghe7549]

@parry2k they are mapped using COID.  I feel that i am going to need to do a Power Query but not sure the best way to go about it.

[edhans]

What is COID? To add records from one table to another, use Table.Combine(), which is the APPEND menu item on the Home ribbon.

Otherwise, I'll need a few more specifics about the goal and what you are using.

[ghe7549]

@edhans I am trying to add a rows to the employee table when there is a vacanies in table 2.

COID is what they both are tied to.

[edhans]

Ok. I'm not sure exactly what you mean by "Add" as adding is appending the data, and for the most part those do not have the same columns. However....

In table 2 I used the following Table.SelectRows logic:

= Table.SelectRows(#"Filtered Rows", each List.Contains(#"Table 1"[Facility], [Facility]))

The first part of List.Contains() is a list of the values in the [Facility] column. The 2nd part is the [Facility] field in table 2. I had manually filtered out any Vacancy dates with null.

I then created a new table that combined table 1, and the filtered version of table 2. You cannot append table 2 directly to table 1 as that will create a circular reference.

So I get this table, which looks strange because the columns aren't the same.

See the PBIX file here.