Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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)
Proud to be a Super User!
Paul on Linkedin.
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.
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.
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.
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)
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |