Forum Discussion

StatsChick's avatar
StatsChick
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • StatsChick's avatar
    StatsChick
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • StatsChick's avatar
    StatsChick
    Frequent Visitor
    4 years ago

    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?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • StatsChick's avatar
        StatsChick
        Frequent Visitor
        4 years ago

        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?