Skip to main content

Power BI

New

Maintain column order in New Table if Calculated Columns are added to source table

Vote (1) Share
Topher60657's profile image

Topher60657 on 27 Sep 2018 01:13:41

This idea has to do with the New Table and “Union” function.
I have a data table loaded into Power BI Desktop. However, I want to utilize a subset of this data table in a new, separate table. Under Modeling, I select New Table and use the Filter function to define my new table – let’s call this Filter table. Inside the Filter table I add calculated columns. I repeat these steps to create multiple Filters tables, all with the same calculated columns. The Filter tables take on the same column order as the Data Table at the time the Filter tables are created. Next, I take all of my Filter tables and I combine them using the UNION function. Since all the Filter tables have the same structure, they are appended easily in a new table – let’s call this the Union Table.
If I have to go back to my original Data Table and add a new calculated column, this new column will be added to the very end (after Filter table calculated columns). This is not a problem if I don’t have to add anymore Filter tables, however, if I have to add a new Filter Table with calculated columns inserted, the new Data Table column will be before the Filter Table calculated columns. This prevents me from consolidating the new Filter table with in the Union table because the columns do not align.
To avoid this issue, can you for force the new Data Table calculated column to appear before any calculated columns that were added to Filter Tables before the Data table was updated?

Thank you!