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 am testing get data from folder and append. I was told that some automation can be performed here.
The goal is to append new file from excel every month to an exising file.
I do get data from folder > edit > filter what files I want (choose two files) > combine files (the two arrows on the top right).
Add columns and delete others, delete source.name, change the name to PSSales > apply& load.
Now I get data again the same way and I have the file as append2.
What to do to append it to PSSales Automatically! ?
If not, what join to use?
Thanks Giora
Solved! Go to Solution.
Hi @gtamir,
Just need to make sure all worksheets you want to combine share the same structure, then, place the new generated Excel file into the same folder, refresh the existing query PSSales in Power BI desktop, the new data will be appended automatically.
Best regards,
Yuliana Gu
I've done the same test and it is working fine without any issues.... Two files in the folder as below..
Created a simple table viz. and the output is as belo...
Now added another file in the source folder
Now refresh the data for the table
The table viz. will automatically reflect the new data...
Proud to be a PBI Community Champion
Hi @gtamir,
Just need to make sure all worksheets you want to combine share the same structure, then, place the new generated Excel file into the same folder, refresh the existing query PSSales in Power BI desktop, the new data will be appended automatically.
Best regards,
Yuliana Gu
I made another test without changing anything in the appended files (No ETL), just try to append the 3ed file.
I press Refresh Preview but the 3ed file is not appended.
Giora
I've done the same test and it is working fine without any issues.... Two files in the folder as below..
Created a simple table viz. and the output is as belo...
Now added another file in the source folder
Now refresh the data for the table
The table viz. will automatically reflect the new data...
Proud to be a PBI Community Champion
I see that you refresh from the table and not from the query.
Any way it doesn't work.
OK I found the problem.
I had 3 files in the folder. I filtered the load to 2 first one in order to load the 3ed one later by the refresh.
This filter remains of course and never loaded the 3ed file.
I'll make a few more tests and update.
Giora
@gtamir That’s Cool
Proud to be a PBI Community Champion
It doesn't happen.
The 3ed file has differnt name as every month I get another file. The structure is the same.
There is something I miss here. How PBI distinguish between the files? there are other files in the folder appart from the one I want to append.
Giora
Just have a look this scenario.... you may get some knowledge on your issue...
I have cereate two files and kept in one folder. uisng this folder i want to combine those two file. i follow below steps achive my goal.
--- Files should be having same column name
--- and file shold be ".CSV" extention
1. go to folder connection.
2. choose your folder.
3. then click on "edit"
4. Remove other columns, except first column.
5. click on combine files.
6. automatically show data like above screen.
9. create one more file in same folder.
10. in query editor click button "Refreh", automatically add data.
If it proper solution for your query, accept as answer. It may help to others...
Thanks
After step 6 (by the way I dont see steps 7 & 8), I want to do some ETL. Let's say add a calculated column and remove another and change header names.
Will this be applied after adding a file to the folder and REFRESH the Query?
Giora
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 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |