Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
I am refreshing a DataMart and am receiving an error because I have added a new column to the table. How do I handle this?
This is the error:
"
Local Sql execution failed. Message=ALTER TABLE SWITCH statement failed because table 'xxx.storage.tablename' has 6 columns and table 'xxx.dms$transaction.temp$0155abbb0f504302886033b507371469:tablename' has 7 columns.
"
Just wanted to echo this issue.. it seems really odd that adding columns in my Dataflow does not translate to those columns being added to the Datamart.
Same issue here. Any changes to a source hoses the entire datamart. Rebuilding it over and over is a waste of money. It is advertised to allow a dataflow source, but it's not working.
I am also experiencing this error when my source has changed. I'm using a dataflow as my source as I'm still testing out datamarts but at the moment not being able to change what I need to include in my dataflow means I'm having to remove the table (via the transform data) from the datamart save, then add back in, and reset and relationships and other changes I've made.
Hi @Anonymous ,
In this official document can see, when use the Transact-SQL ALTER TABLE...SWITCH statement to quickly and efficiently transfer subsets of your data, there is a requirement for the source table and the target table:
Source and target tables must have the same column structure and order. The tables must have the same columns with the same names and the same data type, length, collation, precision, scale, nullability, and PRIMARY KEY constraints (if any), and also have the same settings for ANSI_NULLS and QUOTED IDENTIFIER. Additionally, the columns must be defined in the same order. The IDENTITY property is not considered.
So, based on this, your two tables don't have the same number of columns, caused the error.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yanjiang-msft - appreciate you responding, but I don't think you are understanding the question. Power BI creates the tables behind the scenes - we don't. (Imported) Tables will definitely change structure over time, and the datamart needs to account for this. Please pass this along to the product team. This same situation will happen in a regular database, but we don't have full control over the back-end db associated with the datamarts in Power BI, do we?
Thank you,
-hauffa
Hauffa's comment is correct! We don't have permissions to alter table ourselves and the documentation is not clear.
Hello, I'm having the same issue, and it comes after saving. The editor shows the new field just fine, but it is when trying to save that the error comes up. It appears to load the data (all green checkmarks), then after "wrapping things up" for a long time, it pops up the error, thereby canceling the Save operation. Any help would be appreciated, but I'm thinking this could be a Preview feature bug (along with the model layouts not saving and a couple others I've seen. I'm not bitter, just trying to send feedback so MS can get this working as intended).
Can you try going in Transform Data in the datamart, doublecheck that the new column is showing in preview, then Save?
Hi -
Yes the table shows in Transform Data, it's there to be loaded but causes the issue shown above.