Data Cleansing

Hello,

I am creating a dashboard on Power BI that reads data from many excel sheets available in a SharePoint site.

I understand that the data in the excel sheet should have a specific form/layouts, so the Power BI can recognize them (Data should be in columns, no merged cells, etc) .

The problem is the excel sheets that I use which created by colleagues or extracted form tools, have different forms/layouts that the Power BI can't recognize. (Such as Merged cells or horizontal columns)

What do you usually do in this case?

Create a new sheet that reflects the same data in the required layouts, then link the new sheet to the Power BI?

But sometimes this way doesn’t work!

Or, you have different suggestions?

Thanks in advance