cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Append daily Excel data into existing Query

Hi,

 

I have a dashboard created on PBI Pro using dataquery derived from an excel master file. 

 

Now i am looking for an automated process, where in my daily sales data file  (excel) could be appended to the master file and then discard the Daily sales data file. This process should refresh every morning as soon as the daily sales data file is available.

 

Appreciate a detailed idea to acheive the above.

 

Thanks in advance !

 

 

 

1 ACCEPTED SOLUTION

@Prabunathan,

If you discard the files in the folder, I am afraid that you will get errors in the report when you refresh the dataset in power BI Service.

If you concern about the amount of the files, consider to add new data in your original Excel file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

@Prabunathan,

In your scenario, you can put all the files in a single folder, then use the Folder connector in Power BI Desktop to connect to the folder and load data. 

After you create report in Power BI Desktop and publish the report in Power BI Service, set schedule refresh for your dataset. This way, when you add new files to the folder, all the new data will be appened into the existing query.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thank you for your suggestion and yes that works fine. 

 

Kindly advise if the data refresh would consume more time due to the fact that there will be a build up several data files in the folder ( say after a period of 60 days there would be 60 Plus files to collate the data from each time I refresh the data ) . 

 

Is there a possibility that, i append the latest file to the data query and discard the file each day. Can this be automated placing the data file in a one drive folder.

 

Thanks again inadvance.

 

Prabu

 

 

@Prabunathan,

If you discard the files in the folder, I am afraid that you will get errors in the report when you refresh the dataset in power BI Service.

If you concern about the amount of the files, consider to add new data in your original Excel file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks for this reply.  I too am concerned about the performance of the buildup of append files over time for this same scenario.  Related to the option of exporting the data and adding it to the master file - I want to do this, but I'm having issues extracting all of the transformed data from edit queries, as it will only allow me to export 1000 rows from my transformed data query to Excel.  

 

As well, I have also tried adding a table visual, adding all the columns I want, and exporting the transformed data from the front end.  This also has a significant limitation and is not allowing me to export all rows. 


Any suggestions?  I need to grow my dataset using append similar to the original poster, but am running into roadblocks with extracting all of the transformed data. 

 

Hi,

 

I have similar concern, AnyOne from Microsoft Community suggest a solution Please?

 

Thanks in Advance,

 

Regards

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors