Forum Discussion
When importing Excel Workbook having Superscript text in Header it does not show the column involved
When trying to import an Excel Workbook, using Get and Transform Data, having Superscript text in Header, it does not show the second column, having the Superscript text.
However when double clicking the same Excel Workbook it does show the column:
Superscript text example "[m³]" (for cubic meters)
yeah understood CJW1960 , not surprising but worth asking.
Is there no way to alter the way the XLS files are produced to stop the superscript characters being used? Or to export the data in a different format like CSV?
Phil
Sorry CJW1960 - didn't see you had attached it already. Yes, like PhilipTreacy I get the error. There are twopossible ways to fix it.
- Remove the superscript char
- Convert the workbook to XLSX. I confirmed saving as XLSX fixes the issue.
This is the second confirmed issue I've seen with XLS formatting and Microsoft will not fix. I reported this to them earlier this year and the product team looked at it and basically went "Yup. You are right, it fails, but we aren't going to spend any time coding around an Excel format that was EOL'd in 2007." And I fully understand.
The only suggestion they offered was to install the Access 2010 Engine Redistributable. Links and more info here. That code actually has some stuff for XLS files. However, it did not fix my issue, so no promises here.
Sounds good CJW1960 - I'm doing the same thing with a file I get 2x per month from an outside provider.
Can you mark one/more of these posts as the solution so everyone will know the thread is solved and if they have the same/similar issue will know that saving the file as XLSX is the workaround.
15 Replies
Hi edhans PhilipTreacy ,
Thanks for your help, as I am new to this community, can one of you tell me how to share/upload within this topic?
CheersHi CJW1960 - please share data using the links below. Images/screen caps you can just paste in the browser. For sharing files, use OneDrive, DropBox, etc. with a public share link.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
Can you share your workbook or the M code from your query CJW1960
I just tried to reproduce and it imports just fine. The 3 in my example is full sized of course for the column name.
The only thing I can think of that might be an issue is if you are using an XLS file format instead of XLSX. I know there are issues with formats in XLS workbooks that will cause Power Query connections to work incorrectly.
Hi PhilipTreacy ,
I could not reproduce either when copying an saving in an old Excel 5.0/95/97/2003 format. Also when editing the file(s) and saving in the same format made the column available again. Below a link to one of the xls involved:Kees
https://drive.google.com/file/d/1iRvkkt1Ad1xBJluMjsfvjzzD9zJvxxDJ/view?usp=sharing
Hi CJW1960
Yes, I got the problem with that file.
But if I convert the data in the file to a table (CTRL+T), PQ sees both columns. Is converting the data to a table an option for you?
Or opening and saving the file as XLSX?
Or just leaving the superscript character(s) out of the header?
Phil
Hi PhilipTreacy
Not really then I have to open every file after downloading 😞 or modding and saving.
Hoped there is a better solution. Tried opening the file with Notepad++ but that didn't give me any information but garbage
Thanks for your help, very appreciatedyeah understood CJW1960 , not surprising but worth asking.
Is there no way to alter the way the XLS files are produced to stop the superscript characters being used? Or to export the data in a different format like CSV?
Phil