cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Satch
Helper III
Helper III

Add a record into PowerBi every month from existing data

Hi,

 

I have a PowerBi dashboard, created from several csv files, which are refreshed once a day.

 

In 1 of the files every item has a stocklevel.

Unfortunately, the stocklevel is overwritten every day (Its the way that source database works, cannot change that)

So every refresh(every day) I have the current stocklevel.

 

Now I want to create a new table into PowerBi where everytime on 1st of the month, the total stocklevel of the last day of the previous month is written.

 

So I get a separate table, starting on coming Jun1 

 

Month     Value

May        150 << written on Jun1

Jun          160 << this writen on Jul1

Jul           145 << this written on Aug 1

 

etc

 

Can somebody pls. put me in the right direction?

 

Thanks.

 

 

1 ACCEPTED SOLUTION

I have limited experience regarding the 2 points you asked but I'll try to help in hope that someone else will second my opinion or just propose a better solution:

1. If you are using the service, you can subscribe to a report so that it will send you periodically the report by email. If you are using the server, you might need to develop a paginated report and a scheduled job. The scheduled job will run periodically to export the report using the report's URL and appended query strings to trigger the export functionality. Then the job will send an email with the report or just save it to disk.

2. Power BI doesn't write back to SQL but you can use Power Apps to develop an app that writes data to SQL.

 

Again, keep in mind that I might be wrong with my reponses above. Lets wait for someone else from the community to comment on this.

View solution in original post

3 REPLIES 3
gdarakji
Resolver III
Resolver III

This looks more of a data engineering problem than a data analysis one. My understanding of Power BI is that it doesn't act as a database in which you can store values. You need to work this out in the data source. I understand your system overwrites the stocklevel but you have the CSVs files which you can use to your advantage. Probably save yesterday's generated CSV file somewhere and generate a new one for today instead of replacing yesterday's CSV with today's version. I have no idea what environment you are working on but you should be able to write a simple script that will copy the CSVs to a different folder which can be used for as Power BI's data source. It might be handy if the script also renames the CSVs to the date they were generated on.

Hi,

 

you're right in the base that this is an data engineering thing.

 

But I have also read here and there that Power Bi is able to "write back in SQL"

I'm not using SQL but maybe there are some possibilities to achieve what I want by altering such a method...

 

So actually there are 2 questions:

1.How can PBI generate automatically a datarow (or Report) on every first month.

2.And how could I write that back in SQL.

Edit: when thinking about that last one: if it is possible to write back in SQL, it should be easy to add a record in a (Enter Data) table in the same dataset...right?

 

If there's some direction I can take, maybe I can solve the prob...:)

I have limited experience regarding the 2 points you asked but I'll try to help in hope that someone else will second my opinion or just propose a better solution:

1. If you are using the service, you can subscribe to a report so that it will send you periodically the report by email. If you are using the server, you might need to develop a paginated report and a scheduled job. The scheduled job will run periodically to export the report using the report's URL and appended query strings to trigger the export functionality. Then the job will send an email with the report or just save it to disk.

2. Power BI doesn't write back to SQL but you can use Power Apps to develop an app that writes data to SQL.

 

Again, keep in mind that I might be wrong with my reponses above. Lets wait for someone else from the community to comment on this.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!