Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 name | Actual Prediction | Actual 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
Solved! Go to Solution.
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.
Then click the expand button in the upper right corner, select columns you needed.
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.
For Append Queries, It only cares if the column names are the same, no matter what the value is.
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.
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.
Then click the expand button in the upper right corner, select columns you needed.
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.
For Append Queries, It only cares if the column names are the same, no matter what the value is.
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.
Hi,
Step1: First you need to add index coloumn to each table.as per below
Then simply merge table and you will get
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?
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.
You will want to use a merge operation rather than an append.
Hi,
When i use the merge operation i justs get this:
it simply links to the other table rather than populating correctly.
Nearly there. Click the button next to the last column header.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |