Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jonbox
Helper II
Helper II

Add two columns from other tables

Hi,

 

I'm looking to add two columns from two different tables into one table with the data aligning by some form of key e.g. the project name.

When i try to append the tables i get a table similar to below:

 

Project nameActual PredictionActual Spend
Extration£200 
Refurbishment£400 
Department Upgrade£300 
Building Work£350 
Renewal Fund£790 
  £190
  £400
  £320
  £359
  £800

 

The actual spend from the other table doesnt align with the actual prediction table. How can i get this to work?

 

Thanks

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @jonbox ,

First of all, I hope you figure out Merge and Append. Merge is an addition on columns, Append is an addition on  rows.

If the two tables has at least one column has matching values( means that such as in your sample, there is a column of Project name in the Actual table, and there is also a column of Project name in the Prediction table), then you can merge the two tables with the matching column, for the two columns, only rows with exactly the same value will be aligned, or will be staggered.

It means for example this is your Actual table.

Project name Actual Prediction
Extration £200
Refurbishment £400
Department Upgrade £300
Building Work £350
Renewal Fund £790

This is your Prediction table.

Project name Actual Spend
Extration £190
A £400
B £320
C £359
D £800

Merge with the Project name column, here to see all the data, select Full Outer join kind.

vkalyjmsft_3-1651651678709.png

Then click the expand button in the upper right corner, select columns you needed.

vkalyjmsft_2-1651651495354.png

As in the two Project name columns, there is only one value "Extration" is the same, so after merge, only the row where the Extration is located in the two tables will be aligned.

vkalyjmsft_4-1651651827520.png

 

For Append Queries, It only cares if the column names are the same, no matter what the value is.

vkalyjmsft_5-1651651961372.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

11 REPLIES 11
v-yanjiang-msft
Community Support
Community Support

Hi @jonbox ,

First of all, I hope you figure out Merge and Append. Merge is an addition on columns, Append is an addition on  rows.

If the two tables has at least one column has matching values( means that such as in your sample, there is a column of Project name in the Actual table, and there is also a column of Project name in the Prediction table), then you can merge the two tables with the matching column, for the two columns, only rows with exactly the same value will be aligned, or will be staggered.

It means for example this is your Actual table.

Project name Actual Prediction
Extration £200
Refurbishment £400
Department Upgrade £300
Building Work £350
Renewal Fund £790

This is your Prediction table.

Project name Actual Spend
Extration £190
A £400
B £320
C £359
D £800

Merge with the Project name column, here to see all the data, select Full Outer join kind.

vkalyjmsft_3-1651651678709.png

Then click the expand button in the upper right corner, select columns you needed.

vkalyjmsft_2-1651651495354.png

As in the two Project name columns, there is only one value "Extration" is the same, so after merge, only the row where the Extration is located in the two tables will be aligned.

vkalyjmsft_4-1651651827520.png

 

For Append Queries, It only cares if the column names are the same, no matter what the value is.

vkalyjmsft_5-1651651961372.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

ddpl
Solution Sage
Solution Sage

Hi,

 

Step1: First you need to add index coloumn to each table.as per below

 

VipulGadhiya_0-1651571140801.png

 

Then simply merge table and you will get

 

VipulGadhiya_1-1651571218598.png

 

Hi Vipul,

 

Thanks for the response. Does the index have to match up with the project names across each table in order for this to work correctly? since there is around 100,000 columns in both tables and not completely mirrored. Will this still work?

 

Thanks

Yes I think its working becuase there is not existence of  a common column in each table. 

Hi,

 

Can i use append instead of merge? 

 

jonbox_0-1651580464985.png

With appending i get this issue.

Nope, Append can't worked in these case.

Ok thanks.

 

one final question then; does the index across both tables have to correlate to the same project name?

 

e.g. Index in actual spend table for project "Refurbishment" is 1, but index in actual prediction table for project "System refresh" is 1. does this matter?

Nope,then its not worked,there should be a unique number for each project in each table.

lbendlin
Super User
Super User

You will want to use a merge operation rather than an append.

Hi,

 

When i use the merge operation i justs get this:

 

jonbox_0-1651566210216.png

 

 

it simply links to the other table rather than populating correctly.

Nearly there. Click the button next to the last column header.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.