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,
I have multiple CSV files(nearly 50 files) of different formats in one folder. Instaed of importing one file at a time, I want to import one folder at once.
After importing I want to modify each csv file seperately, i.e. I must use applied steps on every csv file seperately from this folder. What could be the possible methods to do this?
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous
So you want to know how to create a template file that will process each CSV file individually ?OK, follow these steps:
1. Create a new PBIX file
2. Get Data -> More -> Folder and enter the folder path/name
3. Click the Transform Data button on the Folder Import dialog box
4. In the Power Query Editor, right click on your new query and Duplicate it.Do this as many times as you need - once for each CSV.
5. You shoudl now have lots (50?) different queries.
6. You will need to now go through each query one by one and select the file you want and create the transformation steps
7. Presumably you want append these files togetheronce you'ev transformed them? If so click on Append Queries then Append Queries as New and select all the tables (queries) you want to append together.
8. At thsi point your data should be ready to load so click Close & Apply. PBI Desktop will apply all your queries and load the data into the Data Model.
9. You can now save the file as a template. File -> Save As -> give the file a name and choose Save as type -> Power BI Template
Now every time you open that template file it will load the CSV files from that folder and carry out the transformations you created.
Save your PBI Desktop file in case you need to modify those queries and modify the template.
Regards
Phil
Proud to be a Super User!
Hi @Anonymous
So you want to know how to create a template file that will process each CSV file individually ?OK, follow these steps:
1. Create a new PBIX file
2. Get Data -> More -> Folder and enter the folder path/name
3. Click the Transform Data button on the Folder Import dialog box
4. In the Power Query Editor, right click on your new query and Duplicate it.Do this as many times as you need - once for each CSV.
5. You shoudl now have lots (50?) different queries.
6. You will need to now go through each query one by one and select the file you want and create the transformation steps
7. Presumably you want append these files togetheronce you'ev transformed them? If so click on Append Queries then Append Queries as New and select all the tables (queries) you want to append together.
8. At thsi point your data should be ready to load so click Close & Apply. PBI Desktop will apply all your queries and load the data into the Data Model.
9. You can now save the file as a template. File -> Save As -> give the file a name and choose Save as type -> Power BI Template
Now every time you open that template file it will load the CSV files from that folder and carry out the transformations you created.
Save your PBI Desktop file in case you need to modify those queries and modify the template.
Regards
Phil
Proud to be a Super User!
HI @Anonymous
If you have 50 files of different formats then you need to carry out 50 different transformations.
If you try importing by folder then you need to Duplicate the query 50 times after loading the folder contents - giving you 50 different queries - and then isolate each file in its own query and apply the appropriate transformations.
It would be better practice to get the CSV files in a consistent format and reduce the number of queries/transformations you need to do.
Regards
Phil
Proud to be a Super User!
Thank you @PhilipTreacy for the suggestion.
Actually I am going to create template i.e. (.pbit file) by using this folder. And I want to edit these files individually here in power bi Desktop because whenever someone is using this template, the steps should repeat for other data as well. If I try to bring csv file beforehand in some format and then using it in Power Bi is lot more extra work. Hence I prefer importing folder in Power Bi and applied steps on each file separately. Please let me know solution for it. Thanks!
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.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |