Data Conversion Issue from MySQL to Power BI: Displaying Incorrect Values

[hrgm]

Hello everyone,

I hope you're all doing well. I'm currently facing challenges while trying to load data from MySQL.

The data columns in MySQL are of type Tinyint(1), containing values ranging from 0 to 15. When I import them into BI, I converted them to integer types. However, they are only displaying as (0, 1), not showing the correct values. Both MySQL (as of January 2023) and Power BI (as of July 2023) have been updated to the latest versions.

I appreciate your assistance. Thank you.

[amitchandak]

@hrgm , Please check there any other step where data type is other than int

[hrgm]

Thank you for your prompt response.

I'm not sure if I'm following your instructions correctly, but I've checked the following points:

1. When previewing the data within the Query, the column was originally in a true/false type. After conversion, it has become integers (0/1). There are no error indications, and the data profile below the column name is all green, indicating low data variance.

2. The conversion step for the relevant column from MySQL only involves converting it to an integer type.

3. I've also tried reimporting the data, but the issue remains.

---

Q1: Are your instructions suggesting that I confirm the original data within the SQL?

I've directly examined the SQL, and the numbers seem to range from 0 to 15, which should be correct. Additionally, exporting the data to a CSV format confirms that the values indeed range from 0 to 15.

(On a side note, I am able to correctly load the same data in Tableau.)

Q2: Thank you for the video recommendation. I plan to watch it soon. Since it appears to be an extensive 11-hour video, could you please guide me on where to focus for relevance to this issue (such as timestamps or chapters)?

Thank you in advance.

[danextian]

Hi @hrgm ,

I think I understand what happened. Power Query automatically changed the data type for that column to logical/boolean but, because it was the wrong type, you changed it to integer. If you add another step instead of modifying the first changed type step, you'll end up with either 1 or 0.  If this is the case, go to Power Query and then to the applied step that changed the data type to logical. In t he formula bar, find the column name and replace the type from type logical to Int64.Type (case-sensitive) similar to the screeenshot below.

[hrgm]

Thank you very much for your assistance.

I tried converting to integer right after the NAVIGATION step because I was converting after advancing through the steps. However, it only resulted in 0 or 1.

By the way, I have also tried other options like text or those not present in the choices like Int32.Type, following internet information, but the issue hasn't improved.

Actual Query:

let Source = MySQL.Database("local***", "INPUT_***", [ReturnSingleDatabase=true]), INPUT_***data = Source{[Schema="INPUT_***",Item="***data"]}[Data],

ChangedType2 = Table.TransformColumnTypes(INPUT_***data,{{"***_flg", Int64.Type}}), FilteredRows2 = Table.SelectRows(ChangedType2, each true),

　・・・・Names have been transformed. 

Are there any other possibilities? Thank you for your help.