New column in Excel is automatically unpivoted when refreshing data

[LostintheBIu]

Hello,

I have a PowerBI report that takes data from an Excel file. Since my Excel file has year columns named: 2020, 2021, 2022 etc, when making the connection to PowerBI I unpivoted these columns such that I only have one "Year" Column.

Today I added a new column in my excel file, and I noticed that it was automatically unpivotted, which is not what I need. The new column named "match required" is not mixed with my Year column which breaks my whole report. My intuition is that it has something to do with how I unpiovoted my data in the first place, but I do not really understand how I can fix it. 

I also found this post: https://community.powerbi.com/t5/Desktop/Automatically-Unpivoted-Columns/m-p/205346#M90846 and the problem seems to be simillar. It is yet, unclear, what steps I should follow to have my "Match required" column appear just as a normal column.

Any idea how I can fix this?

Thanks!

[Anonymous]

@LostintheBIu  I tried replicating your issue and yes it did work fine for me...Can you share a sample data of your excel file ...i would like to see the newest header"Matched required" where you have added in excel? 

[LostintheBIu]

Hi, thanks for trying!

I tried adding the new column in different places in excel and I always get the same result. Unfortunately, I cannot share any data.

[v-deddai1-msft]

Hi @LostintheBIu ，

It also works well based on my test. You should select the columns (Use CTRL to multiselect ) you want to unpivot and use "unpivot only selected columns":

By default, if you click 'Unpivot Columns', when new columns are added to the original datasource, this new column will also be unpivoted.If the unpivoted result is not what you want,you can remove the step of "unpivoted column",then select the columns you need and choose "unpivot".For steps,pls see below:

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

Best Regards,

Dedmon Dai

[Anonymous]

@LostintheBIu  can you share the sample data atleast which will give an idea what is happening?