Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johnweet
Helper I
Helper I

Transformed table loses column order

I have three different spreadsheets that I am combining in a Union table.  The spreadsheets share some common data, however, the column names are different and the column orders are different.  I am using Power Query to transform the data.  I am taking each table, deleting the columns I don't need, renaming those that are different then re-ordering the columns so all 3 tables are the same.

 

The issue I have is that I see the column order in PowerQuery and they are all identical.  When I "close and apply" and view the tables normally the column orders have moved and they are all different.  This means the union table has mismatched data in each column.

 

If they were reverting to their original position in the table then maybe I could understand it, but they don't. It's a totally new order.

 

I have tried sorting the columns in 3 different ways and I always get the same results.

 

1) Drag and drop the columns to the right order and apply

2) Choose Columns>Choose Columns>sort>name

3) add the following code. 

 

= Table.ReorderColumns(#"Replaced Value1", List.Sort(Table.ColumnNames(#"Replaced Value1"), Order.Ascending))

 

Any suggestions?

1 ACCEPTED SOLUTION

@Johnweet 

Thanks for the images. I see what you mean now. 

Is there a specific reason you need all tables to show the columns in the same order?

BTW, you might consider actually appending the region tables (China EMEA, Americas) into one single table in Power Query (which will make life easier further down the road when using measures, creating visuals etc)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

Can you post a depiction of the before and after? If the table-append function does not respect column integrity, it is probably because the name in one of the columns does not match the corresponding columns in other tables (a space could be the culprit). Also make sure that the data type is the same, though it shouldn't affect the appending of tables.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






This image shows all 3 tables in Power Query.  You can see that all three have the same headings and the same data type.

 

Picture1.png

This image shows one of the tables. Top view is in Power Query. You can see the column ordering.  The second view is from table view. You can see the columns have shifted.

Picture2.png

@Johnweet 

Thanks for the images. I see what you mean now. 

Is there a specific reason you need all tables to show the columns in the same order?

BTW, you might consider actually appending the region tables (China EMEA, Americas) into one single table in Power Query (which will make life easier further down the road when using measures, creating visuals etc)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






That's got it.  learned something new.  Somebody else recommended a Union table. When I use append it brings them together nicely.

 

Thanks  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.