Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mjz123
Regular Visitor

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

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. 

mjz123_0-1686345927159.png

 

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

 

mjz123_2-1686346136864.png

 

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

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

vjingzhang_0-1686551559663.png

 

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

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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.

vjingzhang_0-1686551559663.png

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors