New columns added to end of table

[mdbrierley]

Hi,

I created a tracker for 2022 that has targets along side "actuals".

So I've been testing this by adding in data and refreshing and it's giving me some issues. Namely that even though it looks fine in the editor, when the table is refreshed in excel, any new month actuals get put at the end of the table, and not where they should be (to the right of the target column for that month.

So for example...

If I start with just Jan & Feb actuals data, it would look like this:

Account Name

Jan Target

Jan Actual

Feb Target

Feb Actual

Mar Target

Apr Target

May Target

And So On To EOY

But then when I add March and refresh:

Account Name

Jan Target

Jan Actual

Feb Target

Feb Actual

Mar Target

Apr Target

And So On To EOY

Mar Actual

I've tried playing with the table properties, but nothing seems to fix it.
I have to delete the table and re-create it to fix it, but then I have to fix the conditional formatting too.

I cant share the table because it has sensitive data, so hopefully you get the jist of the issue?

Any help would be really appreciated.

Thanks!
Matt

 

[Maaaart]

Right-click the table, choose External data properties and make sure the option 'Preserve column sort/filter/layout' is not checked.

I 've just signed up to Mr Excel just so I could thank you for posting this. I spent many hours trying to work out why Power Query was adding extra columns when I appended a table, this sorted my problem instantly.

I don't think I would have solved my issue without your pointer!

Cheers!

 

[RoryA]

Good to hear - though I'd like to think you can get more out of this forum in future.