none
Expression.Error: We cannot convert the value 2468999999 to type text RRS feed

  • Question

  • Hi,

    I have a simple query that appends 2 queries and has a column that contains 10 digit numbers. A preview of the query loads in the editor with no issues. When I try to load the query to the data model I get the error "Expression.Error: We cannot convert the value 2468999999 to type text". I have tried changing the column to type text in the power query editor but still get the error.

    I was also getting the error in one of the queries that is appended when I tried to sort by another column, until I removed that step.

    I do not understand why a conversion to text is happening, can anyone help please?

    Edit to add: I appear to have found a solution. The source data is a folder of Excel files. In one of these files the problem column was formatted as Number, in the others it was formatted as text. I changed the format to text in that file and the error no longer appears. 

    However, I still do not understand why the error was appearing and why using the power query editor to explicitly change the data type to either integer or text did not work?

    Thanks

    Alex


    • Edited by AJR1385 Friday, August 30, 2019 9:51 AM
    Friday, August 30, 2019 7:59 AM

Answers

  • It's difficult to say exactly what's going on without seeing your file, but I would definitely recommend you convert the column in the Transform File query, rather than downstream.

    Ehren

    • Marked as answer by AJR1385 Thursday, September 5, 2019 3:32 PM
    Wednesday, September 4, 2019 4:37 PM
    Owner

All replies

  • if your figure is actually a text, it is possible  it has some "space" in it like "  2468999999".

    You can easily fix it in powerquery editor with the Text.Trim  function:

    Just select your column and use the GUI :


    Then "  2468999999" will become "2468999999" which can be tranformed to a Number




    • Edited by anthony34 Friday, August 30, 2019 12:10 PM
    Friday, August 30, 2019 11:56 AM
  • I checked that, there was definitely no leading or trailing space. Thanks for the screenshot though.
    Friday, August 30, 2019 12:53 PM
  • Hi there. It sounds like the root cause of this error was number values in a text column. This will definitely cause errors when you load to the data model. Converting the column, across all the input files, to text should have solved the error. But perhaps the point where you did the text conversion was not affecting the problematic file?

    Ehren

    Tuesday, September 3, 2019 8:18 PM
    Owner
  • Hi Ehren. There error would also appear in the power query editor when I tried to sort by the date column, prior to loading to the data model.

    To give a little more background, the files were exported to Excel from Microsoft SQL Server Report Builder, and the column was mostly blank with some rows containing 10 digit numbers. A small number of those rows also had the string "draft" e.g. 2468999999draft. All of the files had the "Number formatted as text error" other than the file which was causing the problem, in it the column was formatted as number, despite also having the "draft" string on some rows.

    I did Get Data from folder and combined the files. I changed the Transform file to select the first sheet,  (=Source{0}[Data],) as each sheet had a different name. I tried to convert the type of the column at different points in the main query to no avail. I don't think I tried to convert type in the Transform file though.

    I'm still very new to Power Query so any insight would be much appreciated.

    Thanks 

    Wednesday, September 4, 2019 3:59 PM
  • It's difficult to say exactly what's going on without seeing your file, but I would definitely recommend you convert the column in the Transform File query, rather than downstream.

    Ehren

    • Marked as answer by AJR1385 Thursday, September 5, 2019 3:32 PM
    Wednesday, September 4, 2019 4:37 PM
    Owner
  • OK thanks for the help.
    Thursday, September 5, 2019 3:32 PM