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

 

[VBA learner ITG]

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

Hi Matt,

Hopefully, this website will give you a steer on additional steps you can add to correct the issue.

Column Reordering Disorder - Pitfall #4 in #PowerBI #PowerQuery - DataChant

Order is a crucial force in life, especially if you are a data analyst. But as we will see today, the misuse of order might cause an unexpected chaos. In today’s post, we will discuss the chaotic nature of column ordering in the Query Editor of Power BI and Power Query / Get & Transform in […]

datachant.com

 

[mdbrierley]

Hi Matt,

Hopefully, this website will give you a steer on additional steps you can add to correct the issue.

Column Reordering Disorder - Pitfall #4 in #PowerBI #PowerQuery - DataChant

Order is a crucial force in life, especially if you are a data analyst. But as we will see today, the misuse of order might cause an unexpected chaos. In today’s post, we will discuss the chaotic nature of column ordering in the Query Editor of Power BI and Power Query / Get & Transform in […]

datachant.com

Thanks very much, I'll read into this and come back to update this post in due course.
Much appreciated!

 

[mdbrierley]

Thanks very much, I'll read into this and come back to update this post in due course.
Much appreciated!

Hi Matt,

Hopefully, this website will give you a steer on additional steps you can add to correct the issue.

Column Reordering Disorder - Pitfall #4 in #PowerBI #PowerQuery - DataChant

Order is a crucial force in life, especially if you are a data analyst. But as we will see today, the misuse of order might cause an unexpected chaos. In today’s post, we will discuss the chaotic nature of column ordering in the Query Editor of Power BI and Power Query / Get & Transform in […]

datachant.com

Hi,

Thanks again for sending me the link.

I have now had a read through it. It's actually something I've seen before when trying to google the solution, but it doesnt (I dont think!) deal with the problem I'm experiencing, but something slightly different.
I think that article is dealing more with the "hard coding" that PQ does when you reference columns in the PQ editor. I've navigated that particular issue, but hit another one.

My problem is that in the query editor, the columns are all sorted exactly as they should be (success!), but when the report is periodically updated (exported) in excel, the new columns that have appeared in the query are sent to the end (far right) of the excel table and not where they should be.

anonymised example attached.
March data added and report refreshed.
pq looks fine
excel is wrong.

thanks
Matt

 

[RoryA]

Are you refreshing an existing table in Excel, or creating a new one?

 

[VBA learner ITG]

Hi Matt,

try the below steps

1) Diasble Query Load. Ensure Query is no longer showing in the Data Model.
2) Re-enter the Query Editor, Enable the Query Load and Refresh the Query before Closing and Applying Changes.
This will load the query into the data model with column re-ordering preferences intact.

Failing that have you checked the code to make sure that all the references are correct?

Also looking at the Excel table in your picture example are you adding new columns to the end of the data set is that correct or that the loaded output?



Hope this helps.

 

[mdbrierley]

Are you refreshing an existing table in Excel, or creating a new one?

Hi Matt,

try the below steps

1) Diasble Query Load. Ensure Query is no longer showing in the Data Model.
2) Re-enter the Query Editor, Enable the Query Load and Refresh the Query before Closing and Applying Changes.
This will load the query into the data model with column re-ordering preferences intact.

Failing that have you checked the code to make sure that all the references are correct?

Also looking at the Excel table in your picture example are you adding new columns to the end of the data set is that correct or that the loaded output?



Hope this helps.

Thanks again for your replies. I really appreciate the help!

I'm refreshing an existing excel table, that has YTD data and adding the latest month each time.

The data is downloaded as an individual CSV for each month, appended in PQ, pivoting the months targets and actuals to columns and then loading directly to an excel table.
So I wasn't loading any data into the data model in this case.

I still tried the rest of the steps above, but it didn't work unfortunately.
I loaded the next month in and it did the same, putting it at the end of the table in excel, but it looks fine in the PQ editor.

If I delete the table and completely re-load the query to a new table, it puts the column in the correct place.
Obviously, I don't want to have to do this every month though, particularly for two reasons:
- It's time consuming and I'm trying to make this a simple case of refreshing the report each month, without any manual work.
- I'm applying conditional formatting to the table and it breaks this, meaning I also have to re-do that.

I have an idea of how I might get around this problem, so I will try this and let you know how I get on.
I'm going to put some dummy account actuals in, with one case for each future month, then filter the dummy account out of the report before it's loaded.
That way, the actuals columns for all future months will already exist in the table when it's refreshed and it will simply update them when the next moths data is added....

Thanks,
Matt

 

[RoryA]

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

 

[mdbrierley]

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

Oh my god, that's it! How did I not realise that? It's kind of obvious when you point it out haha. What a dummy.

Thank you so much! ?

#Solved !

 

[RoryA]

Glad we could help.