Forum Discussion
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.
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.
11 Replies
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?
- 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!
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.
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
