Forum Discussion
Prevent Power BI from changing imported data
Greetings all, I've scoured the web trying to see if someone has had a similar problem and I haven't found anything. My problem is actually really simple. I have a table in Excel that I import into in Power BI. I don't do any manipluations to it, I just need it exactly in its current form. All is well in the import except for one column. The problem column in the Excel table has several numbers that are formated at text. See below:
Sample value in Excel. These numbers are formated as text.
These are the exact same items that were imported into Power BI. Despite the fact I turned off type detection.
Type detection is turned off:
Is there any way to prevent this? Those numbers are in a column that is used as a primary key. I really need them exactly the way Excel has them.
In my experience with this problem, I found that having it on or off didn't matter. What solved the problem for me though was to simply run TRIM() on all the values. Problem solved.
4 Replies
PJOS_1 , Power bi will not take excel formatting, convert to number, and change decimal place and check , it might be the same number
or
In power bi reduce decimal place
or
Open power query, Right click and open the Advance editor and find the column, and change datatype to text
refer: https://www.youtube.com/watch?v=h0yLtceT0i4
It turns out that Excel apparently is willing to lie to you. I've formated those cells as text yet if I run a simple "ISTEXT" function, several of them come back "FALSE". What a headache.....
Hi PJOS_1
How about turning on the Type Detection option for this file? Based on my test, if I check Type Detection, the data shows well in Power BI. However, when I uncheck Type Detection, the result is the same as yours. It seems Type Detection feature can help solve this problem.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.In my experience with this problem, I found that having it on or off didn't matter. What solved the problem for me though was to simply run TRIM() on all the values. Problem solved.
