cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ghe7549
Helper III
Helper III

Instert Row based on Condition

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
7 REPLIES 7
parry2k
Super User III
Super User III

@ghe7549 can you provide sample data and expected output.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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  

@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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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.

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.

2020-05-19 12_56_01-20200519 - Select Rows based on List - Power Query Editor.png

See the PBIX file here.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors