Power Query tranforms data with errors, but ODBC direct doesn't

[mjz123]

Hi Everyone,

I have a server with a Firebird Database we are pulling data into Power BI via ODBC > Azure Data Factory > Azure Blob > Power BI

I'm a bit new to this and I was told by a more experienced friend that ADF is a good solution for building a data lake, with other benefits like being able to connect to the data remotely, scheduling when data is retrieved and I've set up the connection and everything seemed to be working well, but then I hit a problem.

When I import data, it arrives as  a JSON file. I'll connect Power BI to the odbc "blob" on azure. I'll select the table I need and instead of Load, I select "Tranform Data" Then, when Power Query opens, I'll select the date of the row I want to import, and I'll select the cell that says "Binary". Next, I click Combine Files under the "Combine" Tab and my data will come up. 

The data has been fine, until I tried pulling up a specific Table with a Column that contains numbers and numbers with text. On Power Query, the column PITEMCODE has cells with 8 numbers and cells with 8 numbers + BOM added to the end. 

When I try to convert the column to TEXT from ANY, the errors are still there. 

I've tried separating columns, but the data with text keeps the errors. 

So I tried connecting Power BI via ODBC directly on the server. Data comes up perfectly fine with no errors when I directly import it into Power BI. So I think Power Query is trying to do something fancy and making the data type all screwy. That's my technical expert opinion 🙂 

I'd love any advice on how to make my data come out in the proper format while keeping the Azure Data Factory route instead of using Power BI directly connected to the server via ODBC. (also any advice on why ADF is better or worse for my situation would be much appreciated)

Thanks All! 

Matt

[v-jingzhang · Accepted solution]

Hi @mjz123 

From the screenshot, the column has been changed into Number type first, so it has these errors. When you try to change it into other data types from this step, you are changing it from Number type to another type, so this error will not disappear.

To resolve it, go to the step this error occurs initially (usually it is a step called "Changed types" or "Changed types x") from Applied Steps pane. In the formula bar above the table, you will find the "PITEMCODE" column is changed into Int64.Type or "type number" mistakenly. This is the cause of the error. You can replace it with "type text" manually to eliminate the errors.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.