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

[Anonymous]

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"

[Anonymous]

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]

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]

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"}),

[v-xiaotang]

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.