Loading Excel With =Value into PowerBI

[PowerBIHelp1234]

Hello,

I have a script that is saving down a grouping of excels. Unfortunately, the cells have =Value (meaning one cell might have "=324" and another may have "=4237") and because of this when I load into powerbi the values show as 0. Does anyone have some alternatives I could like at without having to open each one up and save them as values?

Thanks

[danextian]

Hi @PowerBIHelp1234 ,

You need to clean that column before changing the data type. You can right click the column, replace values, to replace: = with: null. This must be done prior to changing the first Changed Data Type (that is usually generated automatically) applied step.

[PowerBIHelp1234]

Thanks for the response, unfortunately that did not work and the values just appear as 0. Any other thoughts?

[danextian]

Hi @PowerBIHelp1234 ,

This is worth investigating. Can you please share a sample data?

[PowerBIHelp1234]

Its generally pretty simple below, not sure how to upload an excel but this should give the idea

[danextian]

Hi @PowerBIHelp1234 ,

You screenshot doesn't show how your data is described. There is no "=4237" or something like that. You can post a link Google, OneDrive or Dropbox. You can post your M code as well.

[PowerBIHelp1234]

So it appears it is more related to the fact that the files are in Protected View when saved down from the source. If I click enable edit and then save again it pulls through al the data correctly in power query. Any thoughts on how to surpass that in power query?

[danextian]

I downloaded an Excel from the internet with several formulas and  connected to it without clicking Enable Editing.  However, I cannot replicate your issue.  That aside and from experience, Enable Editing disappears if a file is saved in OneDrive..