Forum Discussion
Importing Excel spreadsheet from SharePoint Site - Some cells are replaced with blanks
Hi,
The only related post I found was this one: https://community.powerbi.com/t5/Service/Cell-value-quot-TBD-quot-being-imported-a-blank/m-p/219591
I am importing an Excel spreadsheet from a SharePoint site as a dataset, so I can use the scheduled refreshes.
I noticed that cells containing "/" or "\", e.g ."N/A", are replaced with a blank.
I can't figure out how to address this. I checked the spreadsheet, and the cells are formatted as text, so that shouldn't be an issue.
Thanks in advance.
Hi,
Some progress! It appears that if I try to connect to the spreadsheet from the web interface (with Get Data / Files / SharePoint - Team Sites, I guess the problem mentioned above.
I have tried again, from Power BI Desktop this time, using Get Data / Web and enter the direct link to the spreadsheet. The data is now as I expect it to be!
I'm not sure why the behaviour is different, but here we are...
8 Replies
- lbendlin
Super User
Most likely the Excel column is formatted as number.
What is the problem with the null/blank representation?
Hi,
I have double checked the column and the cells are definitely formatted as text.
This particular column can have the values Yes, No, N/A or - (when the column value has not been set).
Yes and No are showing fine, but both N/A and - are converted to blank.
Hope this clarifies.
- lbendlin
Are these cells produced through VLOOKUP?
What is the problem with the null/blank representation?
Hi,
Some progress! It appears that if I try to connect to the spreadsheet from the web interface (with Get Data / Files / SharePoint - Team Sites, I guess the problem mentioned above.
I have tried again, from Power BI Desktop this time, using Get Data / Web and enter the direct link to the spreadsheet. The data is now as I expect it to be!
I'm not sure why the behaviour is different, but here we are...
- lbendlin
Interesting find. Sadly the web connector is not the preferred option (as it locks you into a single file). But at least the issue ticket now can write itself easier...
