Excel Data missing when there is a formula in the source.

[CRS1]

I have an excel file that is generated by a saas reporting utility that I am able to ingest fairly easily in one query. My issue is in the source xls file, there are round functions applied to every cell containing the data I need. When loaded into power query, they show blank values. My work around now is to open the source, select all, and copy values. This prevents me from automating the process fully, as I'd like to just set the source folder and let the query handle my data conversion, or develop a power automate solution to automate completely. Is there a better way to load data in this circumstance? 

[Anand24]

Hi @CRS1 ,
Are you getting blank for all the rows or just some values? Are there any error fields?

It would be much better if you simply take the excel without ROUND function and then apply the rounding in Power Query or DAX itself.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[CRS1]

Blank for all values that had the round function applied, my issue is that these rounding functions are generated in the source, and PQ doesn't recognize the values unless I open the source file, select all, and paste values. I'd like to avoid that step to get to my next level of automation (hands off refresh to generate the clean table)

[v-cgao-msft]

Hi @CRS1 ,

If possible, try saving the Excel file as a CSV file before loading it into Power Query. CSV files contain only values and not formulas, so this may help Power Query recognize your data.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum