Issue using EXCEPT on 2 tables, because order of columns changed from QueryEditor to TableView

[Fric368829]

Hi,

Due to a migration, I want to compair the content of 2 tables from different servers/locations by using the function EXCEPT:

https://docs.microsoft.com/en-us/dax/except-function-dax

According to the explanation, the order of the columns within the table must have the same order. So I checked in the Query editor that both tables have the same order. Also the columns do have the same format. But when I switch to the Table view (click on icon "Data" on the left), the order has been changed. I refreshed manually the report twice. Still this difference. The syscreated timestamp has moved to the first column in one table, while in the other table teh accountID is still the first column. So strange that the order only changes for one table. This generates the error:"Function 'EXCEPT' does not support joining a column of type Date with a column of type Text."

Also there is this blog:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=874d87b1-...

My question is how can I compair those 2 tables? Some insight in my current thoughts:

- is there a way to adjust the column order in the Table view or in the EXCEPT() function?

And if not, then some other options could potential be:

- compairing only column by column by using the EXCEPT() function per column is not an option, because i want to check a lot of columns

- compairing in excel for small tables is an option, but some tables are really large

-I'm also thinking of union both tables in powerBI and then removing double records, but that isn't a stable solution for the different tables.

Please be so kind to share a solution or suggestions on this topic.

Thank you in advance,

Catherine

[v-lionel-msft · Accepted solution]

Hi @Fric368829 ,

In fact, you need import the table in "Edit Queries" first and then change the order of the columns.

My test is as follows:

1. Import my table in "Edit Queries":

2. Modify the column order

3. Close and apply and view in data view:

As you can see, I got the same column order in data view.

If you import data directly into the data view, and then transform the table in “Edit Queries”, you can't change the column order.

Best regards,
Lionel Chen

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

[Fric368829]

Thank you both for anwering so quick!

I tested the solution of Lionel: indeed due to a change in the order in the SELECT statement in my query, the order in the table view remained the same after this change. E.g.:

SELECT syscreated, accountID FROM A    ->  SELECT accountID, syscreated FROM A 

The order in the table view remained syscreated, accountID and did not changed into accountID, syscreated

What I did was to take a fresh new pbix file and then load the query with the correct order in it. Then the order was loaded in the same order in the table view. 

I still need to check the SELECTCOLUMNS solution. Reason I did not start with it, is that I thought this would double the size of the data and therefor for large tables will influence the performance. But I still need to check my assumption.

[Greg_Deckler]

Try wrapping your tables in a SELECTCOLUMNS, that way you can select all of the columns in the same order.