Self-referencing Columns Won't Stay Put Upon Power Query Refresh

[Ctisa87]

I'm using a large google sheet as my source table and pulling the raw data into an excel spreadsheet via power query. From there, I have added multiple columns to the table that reference the columns from the source data. When I created the columns, I inserted them directly next to the columns they were referencing. However, when I refresh the query, my self-referencing columns are all out of order. Ideally, all self-referencing columns would remain next to the columns they are referencing. Any help would be greatly appreciated.  

[Ctisa87]

Thank you, Yalan. I have read that article before, but it seems to be about keeping collumn rows in sync. This is not my problem. I am asking strictly about the column headers. When I refresh the power query, my self referencing column headers (the custom ones that reference the query columns) change order. 

[Ctisa87]

Please see the example below. The first table is how I would like the column headers to remain organized. The second table is what happens when I refresh the query. The formulas still work and the values are correct, the columns themselves are just out of order. 

Job Time	Additional Service 1	Additional Service 1 Billout	Additional Service 2	Additional Service 2 Billout
3	N/A	0.00	N/A	0.00
4	Remote Job	$25	N/A	0.00

Additional Service 1 Billout	Additional Service 2	Job Time	Additional Service 2 Billout	Addtional Service 1
0.00	N/A	3	0.00	N/A
$25	N/A	4	0.00	Remote Job

[jbwtp]

Hi @Ctisa87,

There is a command in PQ Table.ReorderColumns() which deals with this.

Just manually (using your mouse) move the colunms into positions that you prefer them to be and they should fall into places in the output.

Cheers,

John 

[Ctisa87]

Does this solution work for columns that are not part of the power query? For instance, the columns that move are the ones I add in excel after the power query has already been performed.

[jbwtp]

Hi @Ctisa87,

I beleive so. In my testing the structure of the table (columns order), which I set in Excel did not change after data were updated in the origial table and refreshed. Below "blue" table is the source data, "green" table is an output of a simple query, which reads and returns the source data, Tx columns are manually added in Excel and are not part of the dataset returned by PQ: 

Cheres,

John

[v-yalanwu-msft]

Hi, @Ctisa87 ;
First of all, you can read the following post is helpful to you?

Solved: Manually adding column to Table loaded from Query ... - Microsoft Power BI Community
If it doesn't help, can you share more information and screenshots to elaborate about it? for better reproduction.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ctisa87]

Hi Yalan, please see my response above.