Automatically transform and append data

[Amarinho777]

Hello,

In my company we have multiple projects which produce one excel per quarter which contains financial data. The Excels are stored on a Microsoft Azure powered Data Lake.

I have created a set of 7 queries that can fetch and transform the data from one specific sheet of one Excel file into 7 different clean tables that i can then use to create visuals.

However this was done with one file from one project. I need to create an environment where every time a new Excel is added to the dataset in the data lake, Power Bi should be able to fetch the raw data from the specific sheet of the Excel file, make it go through the queries then extract the 7 tables I need and automatically append them to 7 Master Tables containing the history of those tables.

It is doable manually but I won't be able to handle this process once I have many projects uploading many Excels requiring 7 tables each. Hence, any suggestion is welcome.

Thanks

[v-xiaosun-msft · Accepted solution]

Hi @Amarinho777 ,

If we have multiple files that have the same schema, we can combine them into a single logical table by using Power Query. 

Select Content and select Home > Combine Files. Or you can just select the Combine Files icon next to Content.

Please reference the following document.

Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.