Forum Discussion
Power Query - Transform all sheets into a common Format and then Append
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
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.
3 Replies
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.
Hi PurpleGate
You are a Lifesaver.
I implemented your logic and that works like wonder.
Thanks 🙂
Amit
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
