Duplicating tables but have column differences

[Anonymous]

I have a table being pulled into Power Query with a date range filter and then transformations. I've duplicated this table so that 

Table1 does not refresh (historical data) and Table2 is new data.

Table1_hist contains data from 1/1/2022 - 6/30/2023 and Table2 (new) contains data from 7/1/2023 to 12/31/2023.  They have the exact same transformations after the date filter.  

Table1_hist

Table2 (new)

After both tables are loaded, the columns are out of order and I cannot UNION the tables. Why do the tables have a different column order and how do I fix this?

[danextian · Accepted solution]

Hi @Anonymous ,

It is a known fact that, once a query, has been loaded into othe data model the columns do not re-order even if you re-order them in Power Query (an idea has been posted to re-arrange the columns in data view but https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a272113a-0f44-41ac-a402-bf1066984cda).  New custom columns  even if you put them in the far left in Power Query will be added to the far right in the data view once the change has been applied. You might  have changes or added new custom columns in Power Query to your first table after you first loaded it. In that case, I would use SELECTCOLUMNS before creating a union between the two tables. Please see sample formula below.

UNION =
VAR __TBL1 =
    SELECTCOLUMNS ( Table1, "Name1", Table1[Column1], "Name2", Table1[Column2] )
VAR __TBL2 =
    SELECTCOLUMNS ( Table2, "Name1", Table2[Column1], "Name2", Table2[Column2] )
RETURN
    UNION ( __TBL1, __TBL2 )