Showing results for 
Search instead for 
Did you mean: 
Solution Specialist
Solution Specialist

Clean/Transform complex excel sheet in automated way


Is it possible to clean/transform complex excel sheet in power platform? And that should be in an automated way.


I know we can use power query in excel and power BI desktop to transform but that is manual.


We need to clean the sheet and convert to a standard format. One header row as columns and rest as data item rows.


Complex mean, column with multiple rows. In a single sheet will have multiple tables with empty rows in it.





Dual Super User
Dual Super User

Hi @Prakash4691,


Will the structure be consistent every time? You can look at Dataflows to bring data in on a scheduled interval. You setup all your transforms in Power Query once and then it will run automatically on a refresh interval to get new records. 


Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.



@ChrisPiasecki ,


No, it will not be consistent. Every month will receive 30 excel file. All files are in different format. Moreover, we need to validate the file before import like data type, format of the file etc.





Helpful resources

2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,515)