Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
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?
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):
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.
Best Regards
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:
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
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.
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.
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 )
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?
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:
Hope this helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |