cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deboec
Frequent Visitor

Fill blanks in table with values from other table

I have 2 Excel files with one table each.

One table contains data with >500 rows and specific columns. 

There are blank values in one importanct column for me in this table which need to be filled.

 

I have another Excel file with a table that contains the missing data / blanks of the first table.

This second table has the identical structure as the first table but only with the data that is incomplete in table 1.

 

Can I merge / append the two table so that I have the structure and rows of table 1 but filled with the missing data that comes from table 2?

 

Thank you

 

Sample file:

table 1:

hr_data.png

table 2:

hr_data_addition.png

 

1 ACCEPTED SOLUTION
ERD
Solution Sage
Solution Sage

Hi @deboec ,

As far as I understand, you need to get Project column value and it can be looked up by Id column.

1. Merge queries in your first table, use Id columns in both tables, Left Outer join kind.

2. Enpand the needed column (Project).

3. Does your second table contain ONLY missing values from the first one?

If so, you can Merge these columns (choose 2 columns: initial Project column & merged Project column), click Transfrom > Merge Columns.

If not, click Add Column > Custom Column and 

if [Project] = "" then [T2.Project] else [Project]

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

1 REPLY 1
ERD
Solution Sage
Solution Sage

Hi @deboec ,

As far as I understand, you need to get Project column value and it can be looked up by Id column.

1. Merge queries in your first table, use Id columns in both tables, Left Outer join kind.

2. Enpand the needed column (Project).

3. Does your second table contain ONLY missing values from the first one?

If so, you can Merge these columns (choose 2 columns: initial Project column & merged Project column), click Transfrom > Merge Columns.

If not, click Add Column > Custom Column and 

if [Project] = "" then [T2.Project] else [Project]

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors