Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Error : OLE DB or ODBC error:[Expression:Error] We cannot convert the value null to type Text

Hi

 

Can anyone please help decipher the below error and how to solve the problem will be much appreciated ğŸ˜€

 

let

Source = #"F_TransList(MSF900)",

  #"Detected Type Mismatches" = let

    tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),

    recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),

    fieldNames = Record.FieldNames(recordTypeFields),

    fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),

    pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})

in

    Table.TransformColumns(Source, pairs),

  #"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),

  #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"District", "Period", "Transaction No.", "Tran Type", "Jnl", "Work Order", "PO No.", "JNL_ITEM", "Invoice No.", "Project No", "CC", "EE", "Invoice Description", "Amount", "QUANTITY_ISS", "STOCK_CODE", "JnlNo_Jnlitem_AccCode", "Parent", "Cost Responsibility", "CC_Description", "EEParent", "D_SAP-EEParent_map"}),

  #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "District", "Period", "Transaction No.", "Tran Type", "Jnl", "Work Order", "PO No.", "JNL_ITEM", "Invoice No.", "Project No", "CC", "EE", "Invoice Description", "Amount", "QUANTITY_ISS", "STOCK_CODE", "JnlNo_Jnlitem_AccCode", "Parent", "Cost Responsibility", "CC_Description", "EEParent", "D_SAP-EEParent_map"})

in

  #"Reordered Columns"

 

 

 

4 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Anonymous 

    Thanks for reaching out to us. 

    This error is usually caused by a failure to coerce a column containing null to text format. 

    Please try to add the below expression into your M code, 

    if Text.Length([UserRole.Name]) = 0 and
         Text.Length([Sale.Revenue]) > 0 then

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Tang

     

    Appreciate your response. Just to clarify your recommendation, are you saying that i should create a Custom Column and use the below formula in it to retrieve text string that has value > 0 else return "-" or "0".

    Thank you ğŸ˜€

     

    if Text.Length([UserRole.Name]) = 0 and Text.Length([Sale.Revenue]) > 0 then

     

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi Anonymous 

      nope, I mean add to the advanced editor. You need to find the relevant statement in your code and add it in front of it, so that the length can be judged before the forced conversion, and the conversion will continue only if the length is greater than 0.

       

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Tang

         

        I'm not sure what you mean, i know the forced conversion to text happened in the last step in the following. How do i incorporate your solution? Please enlighten me ğŸ’¡, thank you

         

        let
        Source = Odbc.DataSource("dsn=ELLRPT", [HierarchicalNavigation=true]),
        ELLIPSE_Schema = Source{[Name="ELLIPSE",Kind="Schema"]}[Data],
        MSF900_Table = ELLIPSE_Schema{[Name="MSF900",Kind="Table"]}[Data],
        // Next row is to keep 14 columns that are required


        #"Removed Other Columns" = Table.SelectColumns(MSF900_Table,{"DSTRCT_CODE", "ACCOUNT_CODE", "FULL_PERIOD", "INV_ITEM_DESC", "JOURNAL_DESC", "MANJNL_VCHR","JNL_ITEM", "PO_NO", "TRAN_AMOUNT", "TRAN_TYPE", "WORK_ORDER", "EXT_INV_NO", "QUANTITY_ISS", "STOCK_CODE"}),

         

        At the moment i'm using the following codes to replace null and empty cells to solve the issue

         

        #"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text",null,"0",Replacer.ReplaceValue,{"District", "Period", "Tran Type", "Jnl", "Work Order", "PO No.", "Invoice No.", "JNL_ITEM", "CC", "EE", "Invoice Description", "STOCK_CODE", "JnlNo_Jnlitem", "Parent", "Cost Responsibility", "CC_Description"}),
        // Next row replace Empty Cells with "0"
        #"Replaced EmptyCells" = Table.ReplaceValue(#"Replaced Value1","","0",Replacer.ReplaceValue,{"Work Order", "Invoice No.", "STOCK_CODE"}),