Multiple queries transformations VS one big query transformation

[rbech]

Hi Guys !

I have to collect, transform and visualise 14 folders with 1 xls sheet uploaded by days. Each sheet has approximatively 86K rows (timeseries "per second"). Unfortunately the columns are not really the same in every folders (so I can combine files only by folders). So I have initialy 14 requests in Power Query and I created a Datetime table for filtering datas. I've already cleared the data in each requests.

I have to pivot tables to get datetime, variable (=columns in the xls) and value columns. I will publish an app to share visualisations (based on measures) with the customer. (fyi I canot use dataflow in power service). 

My question is, what is the more efficient in Power Query :

1.Unpivot separatively every requests (tables) and then combine them in a synthetic table based on the datetime.
2. Combine every requests (tables) in a synthetic table based on the datetime and then unpivot the synthetic table.
3. Any other recommendation ?

Thank you for your help !

[AmiraBedh · Accepted solution]

For your scenario in Power Query, in my opinion the most efficient approach would likely be to combine the tables first and then unpivot the combined table since you want to reduce redundant transformations across multiple tables.

You can start by combining tables by folders using Table.Combine, performing initial transformations such as removing unnecessary columns on these combined tables.

Once the data is combined and cleaned, use Table.Unpivot to transform it into the desired format.