Forum Discussion
Sometimes Power BI ignores data when refreshing inputs from Excel sheets that have formatted cells
That's all sound advice, but I'm using an app that exports Excel sheets which, although not complicated, have some formatting in them. There are also over a hundred of them, so as you can imagine it's tiresome stripping out the formatting manually. I did have something in place to take them out automatically, but since this was not 100% reliable it couldn't be trusted (& I need this to be 100% reliable)
Hi Griff -If your organization uses Microsoft 365, you could consider Power Automate to process these Excel files in the cloud. Power Automate has actions for reading and writing Excel data, which could reformat the file contents by moving data to a new Excel sheet without any formatting, although this is more complex to set up.
This PowerShell solution should provide the automation and reliability you need to avoid manual intervention while ensuring clean, formatting-free files for Power BI. Let me know if you need further customization with this approach!
Hope this information helps.
