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
StatsChick
Frequent Visitor

PowerBI Reads Text as INT

Hi,

  I'm loading data that has an ID number which, severval rows down, has a "-A" in it. PowerBI keeps returning an error: "Failed to convert ID to INT." I keep thinking that using TRANSFORM would pre-convert the data, but, no matter what I do, I get the error.

 

Is this a pre-load command? And if so, how do I do that? 

 

Thanks.

1 ACCEPTED SOLUTION

Thanks for these links. I found them, too. They both confirm my finding that Power BI does not let me convert field types as they are read. I have to go back into my original server and re-sort the data so that Power BI sees its proper type. Taking off the "auto detect type" flag didn't work, and I don't have access to tell Power BI to read 1,000 rows to identify type. So I'm stuck reworking the original data.

View solution in original post

11 REPLIES 11
v-yiruan-msft
Community Support
Community Support

Hi @StatsChick ,

Please navigate to Power Query Editor page first, click the "Advanced Editor" option in Home ribbon. Then find the applied step "Changed Type", change the data type of column ID from Number to Text just as below screenshot.

yingyinr_0-1630922131360.png

If the above method is not working, please provide some screenshot with data and error message(exclude sensitive info) or your sample pbix file if it is convenient. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. This got me pretty far, but I'm still getting an error. I'm sure it's a syntax thing. What do you think?

 

StatsChick_0-1630944986764.png

 

Hi @StatsChick ,

Based on the screenshot you provided, it appears that there is a syntax error in the last step of the applied steps(please check the part I circled in red in the image below ). May I ask if you are trying to change the data type of the columns in the table for the last step? If so, you could change your applied codes as the one in below table(the part with red font is updated or new added one):

yingyinr_0-1631006870599.png

let
Source = Odbc.DataSource("dsn=CData Power BI CSV", [HierarchicalNavigation = true]),
CData_Database = Source{[Name = "CData", Kind = "Database"]}[Data],
CSV_Schema = CData_Database{[Name = "CSV", Kind = "Schema"]}[Data],
#"All constituents for Power BI.CSV_Table" = CSV_Schema{[Name = "All xxxx", Kind = "Table"]}[Data],
#"Changed type" = Table.TransformColumnTypes(
#"All constituents for Power BI.CSV_Table",
{{"CnBio_Key_Indicator", type text}, {"CnBio_ID", type text}, {"CnBio_Gender", type text}} 
)   //Please add the other columns here to complete it
in
#"Changed type"

In addition, you can use Power Query Formatter to check whether the syntax of applied codes are correct or not.

yingyinr_1-1631007311846.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much for taking all this time with me. I am trying to get Power BI to read that particular column as text. So the syntax that you suggest works until I click "close and apply", and then I get this error:

StatsChick_0-1631025946050.png

So, is there a way that I can tell Power BI to read a particular column as text instead of assuming that it's an integer?

Hi @StatsChick ,

I found two cases similar to your problem, please check the solution in the following links. Hope they also can help you solve the problem.

Failed to save modifications to the server. Error returned: ' OLE DB or ODBC error:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error

If the above methods still can't solve your problem, please share with us your sample pbix file and screenshot of the original data source data (excluding sensitive information).

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for these links. I found them, too. They both confirm my finding that Power BI does not let me convert field types as they are read. I have to go back into my original server and re-sort the data so that Power BI sees its proper type. Taking off the "auto detect type" flag didn't work, and I don't have access to tell Power BI to read 1,000 rows to identify type. So I'm stuck reworking the original data.

StatsChick
Frequent Visitor

I'm sorry for being dense, but I need to keep the column. I seem to be missing where you put that statement. Thanks for getting back to me so quickly.

Sorry, you have to share the screenshots, as I already provided the instructions.

 

I updated my previous reply. you need to remove the column name in the data type conversion step.

sevenhills
Super User
Super User

Power BI always try to convert the data types by probing the first 100 or 200 rows. (built-in intelligence)

Check your M query and alter it if this is the case. (Advanced editor will help for faster )

sevenhills_0-1630696252694.png

 

sevenhills_1-1630696266760.png

 

Remove the column transformation in the changed type. 

 

hope this helps!

Thank you for this hint. Do I put this command in the Advanced Options tab before I open the query, or as part of the load query?

  • It is part of the M query for loading and transformation.
  • I pasted the pics to get you an idea.

 

Power BI, when you bring the data, it works like this: when you connect to the source, it will gather all the info and try to bring the fields, then it converts to appropriate data types (i.e., Say, for timebeing, only if the fields are not text). 

 

Your fix can be one of the following:

  • Say, in the data conversion step,
    • if you have only one column that is being converted, then you can remove the full data type conversion step
    • if you have more than one column that are being converted, then you can manually remove those columns that are not needed in the data conversion step

 

Hope this helps!

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.