Unpivoting goes wrong

[marsclone]

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

TEST FILE

[v-eachen-msft · Accepted solution]

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.

[marsclone]

Hi @v-eachen-msft 

With your advice, i made multiple queries and appended them.

This works.

Thank you!

Regards Marcel