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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tafkat
Frequent Visitor

Datamart is not refreshing because of changed datatype in Dataflow

Hi All,

 

I created a datamart with 1 source (a dataflow) to see how it works 🙂 . After changing the datatype in the dataflow my datamart is not refreshing anymore. Its ginving the following error:

 

Local Sql execution failed. Message=ALTER TABLE SWITCH statement failed because column ... has data type bigint in source table which is different from its type bit in target table. 

 

When I delete the table from the datamart and add it back again tha datamart is refreshing correctly.

But I dont want to do this everytime sominthing is changing in the dataflow. 

 

Thanks for the help! 

11 REPLIES 11
otravers
Community Champion
Community Champion

It worked the first time.

------------------------------------------------
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
vanessafvg
Super User
Super User

have you refreshed the metadata of the dataflow when you make this change ie go into the dataflow and do a refresh of the data in the preview window.  if the metadata changes you need to update it.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg,

 

Its still not working 😞 I did the following steps:

1 Changed the datatype in the dataflow.

2 Did a full refresh of the dataflow

3 Went towards the datamart, when I open the table its still showing the old datatype

Tafkat_1-1657187485839.png

 

4 In the datamart I clicked on the edit button of the changed table when it opens its showing the new datatype.

Tafkat_2-1657187701488.png

5. I refreshed the data in the edit window and saved the power Query

6. Then I get the following message:

  

Tafkat_0-1657187173642.png

 

 

otravers
Community Champion
Community Champion

I tried to replicate this:

 

- Like you at stage 3, I saw that the datamart table had not reflected the dataflow datatype change from int to logical.

- However when I edited then saved the table in the datamart, the refresh completed successfully and the data type is now true/false in the datamart too.

 

I've run into many transient issues with datamarts so far, do you run into the same error even after repeatedly trying the edit/save dance in the datamart?

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

Hi @otravers,

As you said you run into many transient issues with datamarts. Do I need to report this as a bug or something? Im quite new in this community 🙂

 

Thanks for your help!

otravers
Community Champion
Community Champion

Since datamarts are in preview, and the purpose of the preview phase is to catch such bugs, I assume Microsoft is tracking these error conditions in the service already.

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

Hi @otravers ,

 

After clicking Edit table (after stage 3) the true/false data type is showing up. I didnt changed anything in the edit mode I only clicked on the save button. and then im getting the following message:

 

Tafkat_0-1657265373900.png

Did you do the edit and save dance a couple of times or did it succeed already the first time?

Thanks for your time! 

can you post the fulll errror message?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




InternalError

 

ErrorMessage

 

Local Sql execution failed. Message=ALTER TABLE SWITCH statement failed because column … has data type bigint in source table …. which is different from its type bit in target table …

 

HttpStatusCode 500

thats definitely a mismatch, what are the datatypes of those columns?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg,

 

Data Flow

The datatype in the dataflow changed from 123 Whole number towards Boolean data type.

 

Datamart

The datatype in the datamart is still 123 Whole number

 

Edit screen Datamart

datatype in the Edit table datamart is changed towards Boolean data type.

 

After saving the edit table in datamart its giving the below error:

Tafkat_1-1657266295083.png

 

 

When I delete the table from the datamart and add the table back in again then the problem is solved. But this is not working for the future (with 1 test table its not a problem 🙂 )

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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