locked
Power Pivot type mismatch RRS feed

  • Question

  • I try to convert a text data to number in power pivot, but when i try the system has the following error. when i use the normal excel I can did it.

    ============================
    Error Message:
    ============================

    The following system error occurred:  Type mismatch. 
    Datatype conversion failed for [Table: 'Table1', Column: 'Time', Value: '1>00'].

    ----------------------------
    The following system error occurred:  Type mismatch. 
    Datatype conversion failed for [Table: 'Table1', Column: 'Time', Value: '1>00'].


    ============================
    Call Stack:
    ============================

       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ProcessResults(XmlaResultCollection results)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo.DoExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
    ----------------------------
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
       at Microsoft.AnalysisServices.Common.SandboxEditor.DataTypeSelectionChanged(String newSelectedString, TableWidgetGridControl grid, List`1 selectedColumns)

    ============================

    Thursday, May 23, 2019 4:57 PM

Answers

  • The error message clearly says that the conversion failed for the value of '1>00' which is probably meant to be 1.00 since . and > share the same key on most english keyboard layouts. If you fix this value the conversion should work.

    It does not really matter how normal Excel handles this value as Excel does not have the concept of a data type per column, it can have different data types per cell, so it most likely leaves cells with this value as text.


    http://darren.gosbell.com - please mark correct answers

    Friday, May 24, 2019 6:24 AM
  • Datatype conversion failed for [Table: 'Table1', Column: 'Time', Value: '1>00'].


    By the error message I would say you have non-numeric value "1>00" in your source data, which are not convertable.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 24, 2019 6:44 AM

All replies

  • The error message clearly says that the conversion failed for the value of '1>00' which is probably meant to be 1.00 since . and > share the same key on most english keyboard layouts. If you fix this value the conversion should work.

    It does not really matter how normal Excel handles this value as Excel does not have the concept of a data type per column, it can have different data types per cell, so it most likely leaves cells with this value as text.


    http://darren.gosbell.com - please mark correct answers

    Friday, May 24, 2019 6:24 AM
  • Datatype conversion failed for [Table: 'Table1', Column: 'Time', Value: '1>00'].


    By the error message I would say you have non-numeric value "1>00" in your source data, which are not convertable.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 24, 2019 6:44 AM