Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.