Forum Discussion
Unpivoting goes wrong
Hello,
I have the following situation.
I have a folder, with different files. Each file contains numbers from an company. These numbers are shown in different columns per year. The first files had the years 2017 till 2013.
Everything goes fine, when i select the first two columns and select 'unpivot other columns'.
| Company | P&L | 31-12-2017 | 31-12-2016 | 31-12-2015 | 31-12-2014 | 31-12-2013 | |
| File1 | A | Revenue | 105 | 110 | 105 | 100 | 95 |
| File2 | B | Revenue | 99 | 98 | 97 | 96 | 95 |
transforms in
| Company | P&L | Date | Amount |
| A | Revenue | 31-12-2017 | 105 |
| A | Revenue | 31-12-2016 | 110 |
| A | Revenue | 31-12-2015 | 105 |
| A | Revenue | 31-12-2014 | 100 |
| A | Revenue | 31-12-2013 | 95 |
| B | Revenue | 31-12-2017 | 99 |
| B | Revenue | 31-12-2016 | 98 |
| B | Revenue | 31-12-2015 | 97 |
| B | Revenue | 31-12-2014 | 96 |
| B | Revenue | 31-12-2013 | 95 |
But the problem occurs when i add a new file to the folder, where the years are from 2018 till 2014, then it transforms from
| Company | P&L | 31-12-2018 | 31-12-2017 | 31-12-2016 | 31-12-2015 | 31-12-2014 | |
| File3 | C | Revenue | 140 | 135 | 130 | 125 | 120 |
to
| Company | P&L | Date | Amount |
| A | Revenue | 31-12-2017 | 105 |
| A | Revenue | 31-12-2016 | 110 |
| A | Revenue | 31-12-2015 | 105 |
| A | Revenue | 31-12-2014 | 100 |
| A | Revenue | 31-12-2013 | 95 |
| B | Revenue | 31-12-2017 | 99 |
| B | Revenue | 31-12-2016 | 98 |
| B | Revenue | 31-12-2015 | 97 |
| B | Revenue | 31-12-2014 | 96 |
| B | Revenue | 31-12-2013 | 95 |
| C | Revenue | 31-12-2017 | 140 |
| C | Revenue | 31-12-2016 | 135 |
| C | Revenue | 31-12-2015 | 130 |
| C | Revenue | 31-12-2014 | 125 |
| C | Revenue | 31-12-2013 | 120 |
The years from company C are seen as the years 2017 till 2013, where it must be 2018 till 2014!
Is there a solution for this, or inherently working like this?
I've added a testfile, thank you in advance.
Regards Marcel
Hi marsclone ,
Since the column of the first file is set to be the standard at the time of import, the default column name is returned when the column name is inconsistent. So you need to re-import the files with inconsistent column names. Then use “Append columns” to get a collection of two tables.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi marsclone ,
Since the column of the first file is set to be the standard at the time of import, the default column name is returned when the column name is inconsistent. So you need to re-import the files with inconsistent column names. Then use “Append columns” to get a collection of two tables.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
With your advice, i made multiple queries and appended them.
This works.
Thank you!
Regards Marcel
