Error happened while reading data from mysql to Power BI

[Yunus_Miah]

When connecting MySQL data to Power BI, the data loads successfully in Power Query. However, upon clicking 'Close & Apply,' an error message appears: 'An error occurred while reading data from MySQL to Power BI.

[Anonymous]

Hi @Yunus_Miah ,

1. You seem to have defined two order_type aliases.

2. One possible problem is that in some cases, the order_created_at, delivered_date, and reconciled_at fields may be non-date-type values (e.g., strings or numeric values), which can cause CAST operations to fail. In addition, if some of the values in these fields cannot be converted to a valid DATE type, then the DATE function will also fail.

You attempted to convert dms_order_id, sku_id, group_order_id, DB_id, dsr_id, retailer_id, and vaccount_id to the UNSIGNED type. If the original field is not an integer type or contains a value that cannot be converted to an unsigned integer (such as a negative or null value), this may result in the error.

You can try querying one field at a time to narrow down the problem.

You can also run the SQL query directly in a MySQL environment to see if it executes successfully or if there are more detailed error messages.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

Look for more specific error details in the error message provided by Power BI. This can often give clues as to what might be causing the issue, such as specific SQL errors or timeouts. 
as the power query presents the initial rows of data, the next data might leads to the error so if you share your code here I can help you more.

[Yunus_Miah]

The error message is "An error happened while reading data from the provider: 'Input string was not in a correct format."

SELECT

IF(order_created_at IS NULL OR order_created_at = '', DATE('2020-01-01'), CAST(order_created_at AS DATE)) AS order_created_at,
IF(delivered_date IS NULL OR delivered_date = '', DATE('2020-01-01'), CAST(delivered_date AS DATE)) AS delivered_date,
IF(reconciled_at IS NULL OR reconciled_at = '', DATE('2020-01-01'), CAST(reconciled_at AS DATE)) AS reconciled_at,
COALESCE(LP, 0.0) AS LP,
COALESCE(SP, 0.0) AS SP,
COALESCE(CAST(ROUND(order_value, 2) AS DECIMAL(10,2)), 0.0) AS order_value,
COALESCE(CAST(ROUND(order_value, 2) AS DECIMAL(10,2)), 0.0) AS order_value,
COALESCE(CAST(ROUND(delivered_value, 2) AS DECIMAL(10,2)), 0.0) AS delivered_value,
COALESCE(CAST(ROUND(return_value, 2) AS DECIMAL(10,2)), 0.0) AS return_value,
COALESCE(CAST(ROUND(exchange_value, 2) AS DECIMAL(10,2)), 0.0) AS exchange_value,
COALESCE(CAST(ROUND(damage_value, 2) AS DECIMAL(10,2)), 0.0) AS damage_value,
COALESCE(CAST(ROUND(free_claimable_value, 2) AS DECIMAL(10,2)), 0.0) AS free_claimable_value,
COALESCE(CAST(ROUND(free_non_claimable_value, 2) AS DECIMAL(10,2)), 0.0) AS free_non_claimable_value,
COALESCE(CAST(ROUND(sku_non_claimable_discount, 2) AS DECIMAL(10,2)), 0.0) AS sku_non_claimable_discount,
COALESCE(CAST(ROUND(nmv, 2) AS DECIMAL(10,2)), 0.0) AS nmv,
COALESCE(CAST(ROUND(cogs, 2) AS DECIMAL(10,2)), 0.0) AS cogs,
COALESCE(CAST(ROUND(sku_baki_collection, 2) AS DECIMAL(10,2)), 0.0) AS sku_baki_collection,
COALESCE(CAST(ROUND(sku_market_short, 2) AS DECIMAL(10,2)), 0.0) AS sku_market_short,
COALESCE(CAST(ROUND(sku_retailer_short, 2) AS DECIMAL(10,2)), 0.0) AS sku_retailer_short,
COALESCE(CAST(ROUND(sku_retailer_short_collection, 2) AS DECIMAL(10,2)), 0.0) AS sku_retailer_short_collection,
COALESCE(CAST(ROUND(sku_operational_discount, 2) AS DECIMAL(10,2)), 0.0) AS sku_operational_discount,
COALESCE(CAST(ROUND(sku_trade_discount, 2) AS DECIMAL(10,2)), 0.0) AS sku_trade_discount,
COALESCE(CAST(ROUND(sku_total_collected, 2) AS DECIMAL(10,2)), 0.0) AS sku_total_collected,
COALESCE(CAST(ROUND(sku_anchor_receivable_total, 2) AS DECIMAL(10,2)), 0.0) AS sku_anchor_receivable_total,
COALESCE(CAST(ROUND(sku_anchor_receivable_collection, 2) AS DECIMAL(10,2)), 0.0) AS sku_anchor_receivable_collection,
COALESCE(CAST(ROUND(non_claimable_discount_total, 2) AS DECIMAL(10,2)), 0.0) AS non_claimable_discount_total,
COALESCE(CAST(ROUND(claimable_discount_total, 2) AS DECIMAL(10,2)), 0.0) AS claimable_discount_total,
COALESCE(CAST(dms_order_id AS UNSIGNED), 0) AS dms_order_id,
COALESCE(CAST(sku_id AS UNSIGNED), 0) AS sku_id,
COALESCE(CAST(group_order_id AS UNSIGNED), 0) AS group_order_id,
COALESCE(CAST(DB_id AS UNSIGNED), 0) AS DB_id,
COALESCE(CAST(dsr_id AS UNSIGNED), 0) AS dsr_id,
COALESCE(CAST(retailer_id AS UNSIGNED), 0) AS retailer_id,
COALESCE(order_qty, 0) AS order_qty,
COALESCE(delivered_qty, 0) AS delivered_qty,
COALESCE(return_qty, 0) AS return_qty,
COALESCE(exchange_qty, 0) AS exchange_qty,
COALESCE(damage_qty, 0) AS damage_qty,
COALESCE(free_claimable_qty, 0) AS free_claimable_qty,
COALESCE(free_non_claimable_qty, 0) AS free_non_claimable_qty,
COALESCE(CAST(vaccount_id AS UNSIGNED), 0) AS vaccount_id,
COALESCE(TRIM(order_type), '') AS order_type,
COALESCE(TRIM(order_status), '') AS order_status,
COALESCE(TRIM(dsr_phone), '') AS dsr_phone,
COALESCE(TRIM(dsr_name), '') AS dsr_name,
COALESCE(TRIM(employee_id), '') AS employee_id,
COALESCE(TRIM(retailer_name), '') AS retailer_name,
COALESCE(TRIM(retailer_phone), '') AS order_type,
COALESCE(TRIM(category), '') AS category,
COALESCE(TRIM(product_name), '') AS product_name,
COALESCE(TRIM(sku), '') AS sku,
COALESCE(TRIM(anchor), '') AS anchor,
COALESCE(TRIM(DB_name), '') AS DB_name

FROM dwh_std_powerbi.dms_order_report;

[Omid_Motamedise]

this error might indicate an issue with the data type or format of the data being loaded or it might be because of COALESCE(CAST(ROUND(order_value, 2) AS DECIMAL(10,2)), 0.0) also.

[Yunus_Miah]

I've tried several methods, but I still can't get it to load after clicking 'Close & Apply.