Load error caused by colon

[SharonB]

In one of our PowerBI datasets, we import an excel worksheet that has a text column called contract number. It has been working fine until this past week. Suddenly it is not importing. When I reviewed the error message, it says there is a binary error. I've identified the issue as two contract number rows where the contract number starts with a colon. When I remove the colon, the spreadsheet imports fine. I'm not sure why this suddenly started happening, the colons have been there all along. Does anyone have an idea for how to solve for this?

[SharonB]

Yes, this suggestion resolved the issue.  Another method that seemed to work was to use the Replace Value to replace ":" with "".  Thank you!

[SharonB]

Hi jaweher899.  Thank you for the quick reply.  I’m not able to remove the colons from the worksheet so I will try your solutions. 

[jaweher899 · Accepted solution]

The issue is likely caused by a mismatch between the data type of the column in Power BI and the actual data in the Excel worksheet. The colon (":") in the contract number is most likely causing Power BI to interpret the column as a different data type, leading to the binary error.

A solution to this issue would be to remove the colon from the contract number in the Excel worksheet or to modify the data type of the column in Power BI to match the actual data. This can be done by going to the Power BI Desktop, clicking on the column header, and selecting a new data type from the dropdown menu.

If you're unable to modify the data in the Excel worksheet, you could also try creating a calculated column in Power BI that removes the colon from the contract number before it's imported into the report. This can be done using the DAX formula, such as =SUBSTITUTE(<column name>, ":", "").

It's always important to review the data type of columns in your reports to ensure they match the actual data and prevent errors during the import process.