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
Anonymous
Not applicable

DataMart Change Table Structure

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.

"

9 REPLIES 9
kylefox101
Frequent Visitor

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. 

tmerridew
Regular Visitor

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.

v-yanjiang-msft
Community Support
Community Support

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

Anonymous
Not applicable

Hauffa's comment is correct! We don't have permissions to alter table ourselves and the documentation is not clear.

hauffa
Frequent Visitor

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).  

otravers
Community Champion
Community Champion

Can you try going in Transform Data in the datamart, doublecheck that the new column is showing in preview, then Save?

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
Anonymous
Not applicable

Hi -
Yes the table shows in Transform Data, it's there to be loaded but causes the issue shown above.

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.

Top Solution Authors
Top Kudoed Authors