Power Query Editor Decimal Issue on Text Field

[JoyCornerstone]

I am bringing in an excel spreadsheet with job numbers. The job numbers in Excel are of type "Text."  Samples include:

2102461

2103423.01

2103423.02

2112638

2112640

On the Power Query editor they are set to come in as Text  - but when they come in, they come in as

2102461

2103423.0099999998

2103423.0099999998

etc.

I have 5 tables with this data and I want to join a relationship with the data - but am wary that since some of them are coming in incorrectly, they might not match up. How can I resolve this?

Thanks ahead of time for your ideas.

[v-yuezhe-msft · Accepted solution]

@JoyCornerstone,

Please ensure that you have set the data type of the numbers to Text correctly in Excel. I make a test using your sample data by directly coping and pasting them to Excel as below.



When I disable Type of detection option in Power BI Desktop, the data come in as follows.



When I enable the type detection option in Power BI Desktop, the job numbers will be automatically changed to decimal number in Power BI Desktop query editor. I then delete the change type step in Query Editor, the job numbers are as below.



Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@JoyCornerstone,

Please ensure that you have set the data type of the numbers to Text correctly in Excel. I make a test using your sample data by directly coping and pasting them to Excel as below.



When I disable Type of detection option in Power BI Desktop, the data come in as follows.



When I enable the type detection option in Power BI Desktop, the job numbers will be automatically changed to decimal number in Power BI Desktop query editor. I then delete the change type step in Query Editor, the job numbers are as below.



Regards,
Lydia

[JoyCornerstone]

Thanks. I can see how that will fix it.  Unfortunately I built a bunch of visualizations before I noticed this, and while I can see this fix work in a new PowerBI file, it doesn't seem to work in my old file- even if I bring it in as a new data source.  Frustrating.....but thank you.

[v-yuezhe-msft]

@JoyCornerstone,

Could you please share the original excel and PBIX file for me to test? You can share the files via Private message.

Regards,
Lydia

[JoyCornerstone · Accepted solution]

Just wanted to leave this here for others how might find it later.  It took some work, but I was able to change my Excel columns to Text using the TEXT function in Excel.  

Once you have linked them into PowerBi, the only way to get them to really be Text is to use the TEXT function in Excel. Then they will all read into the PowerBI successfully.

[JoyCornerstone]

Thanks @v-yuezhe-msft but it would be faster for me to just re-do it then clear out the private data our client would require before I could send it over.  

Thanks for your help though!  Joy