OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number..

[ksheth]

Hello,

I am encountering the error "OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number.." when trying to apply changes from the query editor.

I have several applied steps in my query (e.g., reodering columns, removing columns, duplicating columns, creating custom columns), and I have pinpointed that this error is occuring directly after applying my final step where I am removing duplicate rows. I cannot figure out WHY this particlar step of deduplicating rows is causing this error, as the column being deduplicated on is a Text column. And I am not encountering this error in any steps leading up to the deduplication (I checked each step one-by-one). Can anyone provide any ideas as to why this might be happening and how I can fix it?

Here are my steps from the Advanced Editor:

let
    Source = Folder.Files("FILE SOURCE"),
    #"FILE SOURCE" = Source{0}[Content],
    #"Imported CSV" = Csv.Document(#"FILE SOURCE",[Delimiter=",", Columns=202, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PIN", type text}, {"Date", type date}, {"Time", type time}, {"Zipcode", Int64.Type}, {"Age", Int64.Type}, {"Sex", type text}, {"Hospital", type text}, {"CRace_CEth_Combined_Broad", type text}, "ComboRegionName", "Age Group"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"ComboRegionName", "Age Group"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Zipcode", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Date", "Date2"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date2", type text}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type2", "Time", "Time2"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Time2", type text}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type3", "Age", "Age2"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column2",{{"Age2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type4", "Zipcode2", each if [Zipcode]=null then "A" else [Zipcode]),
    #"Changed Type6" = Table.TransformColumnTypes(#"Added Custom",{{"Zipcode2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type6", "DedupID", each [Hospital] & "" & [Date2] & "" & [Time2] & "" & [Zipcode2] & "" & [Age2] & "" & [Sex] & "" & [CRace_CEth_Combined_Broad]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"PIN"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Removed Duplicates",{{"DedupID", type text}}),
    #"Removed Duplicates1" = Table.Distinct(#"Changed Type5", {"DedupID"})
in
    #"Removed Duplicates1"

Thank you!

[v-kpoloju-msft]

Hi @ksheth,

May i know has your issue been resolved? If the response provided by the super user @AmiraBedh, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[AmiraBedh · Accepted solution]

I have a doubt with this part:

{"CRace_CEth_Combined_Broad", type text}, "ComboRegionName", "Age Group"

it may not able to correctly specify types for "ComboRegionName" and "Age Group", which might be leading Power BI to guess a default type (likely type number) for one or both columns.

So even though you later remove these columns, the error might occur before that removal if something earlier (like column type inference or a cached schema) tries to validate the whole table before applying transforms.

Try to modify the "Changed Type" step to properly define the types for all columns:

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
{"PIN", type text},
{"Date", type date},
{"Time", type time},
{"Zipcode", Int64.Type},
{"Age", Int64.Type},
{"Sex", type text},
{"Hospital", type text},
{"CRace_CEth_Combined_Broad", type text},
{"ComboRegionName", type text},
{"Age Group", type text}
}),

I think the error only appears after deduplication likely because that’s when Power BI tries to load and materialize the data into the model.
Deduplicating with Table.Distinct doesn’t itself coerce types but if the underlying data has unexpected values or nulls, the type transformations applied before or after could fail during the final load.

Some improvements for your code :

each if [Zipcode] = null or [Zipcode] = "" then "A" else [Zipcode]

or better:

each if [Zipcode] = null then "A" else Text.From([Zipcode])

Or even better:

each if [Zipcode] = null then "A" else [Zipcode], type text

[ksheth]

Hi Amira,

Thank you for your response. I ended up just deleting the step where PowerBI automatically Changed Types and instead manually assigned each variable's data type. That seems to have resolved my issue, but inherently it still seems weird to me that it was able to execute all the way through the first Remove Duplicate Rows step, but then had an issue in the second Remove Duplicate Rows step. Thank you for your suggestions on how to improve my code too, that was very helpful!

[v-kpoloju-msft]

Hi @ksheth,

Thanks for the update. I'm glad to hear that manually assigning data types resolved your issue. Power BI’s automatic type detection can sometimes introduce unexpected behaviour, especially when working with diverse datasets. Your approach of explicitly defining each column's type is a great best practice to prevent similar issues in the future.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.