Importing Excel spreadsheet from SharePoint Site - Some cells are replaced with blanks

[AlexandreCop]

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.

[AlexandreCop · Accepted solution]

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...

[lbendlin]

Most likely the Excel column is formatted as number.   

What is the problem with the null/blank representation?

[AlexandreCop]

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?

[AlexandreCop]

Hi,

No, the values are typed in manually.

"What is the problem with the null/blank representation?" - I don't know what you mean by this?

[lbendlin]

I would think that representing "#N/A"  as  blank/null is appropriate.  Unless it literally is a text of "#"+"N"+"/"+"A"  ?

[AlexandreCop]

Hi,

In this column a blank value and the text value "N/A" are valid, and have a different meaning, hence why I need to show both within PowerBI.