How to make a simple ETL with Power Automate and Sharepoint ?

[CSA75FR]

Hi there,

I would like to know your idea about the use case I'd like to automate.

We receive through Sharepoint several excel sheets from different partners. By definition all sheet carry same kind of data formated the same way the only which differs from one file to another is the column ordering. I would need to combine all those file into a single one after having unified the column ordering of the source excel sheets.

How would you make that ?

Thank you 

[eric-cheng]

Hi @CSA75FR ,

How many rows of data would each sheet have?  Apart from combining the sheets into one, what else is required?  

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[CSA75FR]

Hi @eric-cheng,

Each sheet may have up to 100 rows. 

Operation expected between the source excel sheets and the target one is mostly about data mapping and calculation to fill fields in the target sheet which don't exist in the source.

I thought about the following approach :

1°) Importing each source sheet into a corresponding sharepoint list

2°) Creating a sharepoint list to receive the target content

3°) Running a SQL statement to populate the target list (which I don't know how to do)

or using an excel sheet linked to the source sharepoint list and running a vba code to produce the target content into a specific tab (in that case step 2 is not required)