Power Query - Transform all sheets into a common Format and then Append

[Anonymous]

Hi Team,

I have multiple excel sheets with data sample as below:

I have created a common format in Power query as this:

When i am adding all sheets from a folder, i want all the sheets to be transformed into the header format and then append.
If i am doing the transformations for current 2 sheets, how can i make sure that if i add other sheets into the folder in future that would transformed automatically into same format.

Thanks
Amit

[KT_Bsmart2gethe]

Hi @Anonymous ,

I have a video explaining how to append multiple files with different file formats.

https://www.youtube.com/watch?v=K9IB9bLxEXo

Regards

KT

[PurpleGate · Accepted solution]

Hi, 

I have had a similar task, my solution was the following:

All my excel files were in a Sharepoint folder.

1. Connect to folder system

2. Navigate to folder that contains all the excels

3.  Expand Content

4. Delete all the applied steps that appear when you expand content until you get back to step "Transform File" 

5. Expand Transform file

6. Make the headers exactly how you want it. 

Then, when you have new excels added into that folder, they automatically append themselves under the same headings.

And your Powerbi makes some fun helper queries for you automatically.

[Anonymous]

Hi @PurpleGate 

You are a Lifesaver.

I implemented your logic and that works like wonder.

Thanks 🙂
Amit