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

Power Query
1

Gooday,

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”

Thank you in advance

2

Hi,

From above error msg it hard to what it is causing error the power bi created a table for you which only conation those row which conation error you can look on the error and can fix the issue if its data mismatch you need to check data type of your column.

Thanks

3

Hi @WanTeck , did the response provided by the @Anurag help solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!