Daily and Weekly Sales Activity Updates

[djr001]

Hi.

I have several questions:

1. I receive daily email excel attachments containing new sales report information each night via an email distribution list. About 10 sales reports each night which are all from different cost centers at different locations. To point out the obvious, each day comes with new and upated sales numbers for that day's activity. I created a PBI query set and the visuals that follow. I have been copying the M-script into each new PBI file created so that I dont have to re-create each query step for each report. I dont want the data to be added to the existing (previous day's) report, I want to replace it since each day's report is stand alone. Is there a better way to do this and even better automatically?
   • I want to update the query/visuals with the new data set each night and republish but every cost center has multiple different people that send the excel file and they all name it whatever they feel like for that day. There is no consistency on how the files are named. 
   • If I setup a flow procedure to have the email attachment sent to a folder in onedrive, is there a way to automatically delete the previous file or do I have to manualy complete this step each night? I am assuming if I don't delete the file, it will just keep adding to the existing report. That might not be a problem if I knew how to only report that day's activity without it being mixed in with the previous day's activity. How do I do that? The cumulative is fine but my owners only want to receive a notification for the day's activity. Eventually of course a weekly and monthly recap is expected as well but that would be in a separate report.
2. There is another broader, weekly Friday excel report also emailed via a distribution list containing inventory status of all remaining consumables, demographics data, and various other information used for the PBI visuals. In this case the same file is continuosly updated by adding to the existing previous week's data. I guess it's sort of the same scenario as above where the report needs needs to overwrite the previous week's report so that it doesnt duplicate. 
   • Can you setup a query step that automatically replaces all of the data with the new report without adding cumulatively since each weekly report will already contain the old and new information housed in one table?
   • Since each transaction is dated, I can still use a timeline visual based off of that date so I dont think it matters if it is overwritten. 
3. For the daily report referenced, is there a way to show day over day change if I am processing this new report each night through the query steps? Such as how many sales so far, and how many from yesterday? The report itself does not track anything other than the day's activity. For the daily sales report, it starts on Saturday and ends on Friday as the weekly cycle. 
4. I just need someone to walk me through the logistics of these kind of common reporting procedures. I am more or less starting all of this from scratch because there was no one else that handled it this way other than just receiving emails daily and weekly and everyone opened up the excel sheets to consume whatever information they wanted. This is all new to me and while the logistics might be easy for some, I could be completely missing the easy solution and better way of handling the logistics. 

Any help would be appreciated.

Thank you 

[v-shex-msft · Accepted solution]

 Hi @djr001

#1.

You can add current date filter on power query to only display current date records, but power bi not contains any feature to auto split and export data from power bi.

Power Query Current Date Filter

1.1. If these excel files same stored with same structure in different files, you can move them to one folder and try to get data from that folder. After get data, you can simple expand and merge them to one table.

Importing data from folder

1.2. I' not familiar with flow procedure, you can submit this to related forum to get further support.

OneDrive document

#2.

After add dynamic date filter, it will only display specific period based on current date. You not need to replace report manually.

#3.

Please refer to below link to know how to get diff from DOD period.

DAX Query on (Day Over Day)

#4.

After create and publish power bi reports, you can try to use power bi subscribe feature or microsoft flow to control and send email of these reports at particular date period.

Subscribe to a Power BI report or dashboard

Microsoft Flow and Power BI

Regards,
Xiaoxin Sheng

[djr001]

Hi.

I have several questions:

I receive daily email excel attachments containing new sales report information each night via an email distribution list. About 10 sales reports each night which are all from different cost centers at different locations. To point out the obvious, each day comes with new and updated sales numbers for that day's activity. I created a PBI query set and the visuals that follow. I have been copying the M-script into each new PBI file created so that I don’t have to re-create each query step for each report. I don’t want the data to be added to the existing (previous day's) report, I want to replace it since each day's report is stand alone. Is there a better way to do this and even better automatically?

I want to update the query/visuals with the new data set each night and republish but every cost center has multiple different people that send the excel file and they all name it whatever they feel like for that day. There is no consistency on how the files are named.

If I setup a flow procedure to have the email attachment sent to a folder in onedrive, is there a way to automatically delete the previous file or do I have to manually complete this step each night? I am assuming if I do not delete the file, it will just keep adding to the existing report. That might not be a problem if I knew how to only report that day's activity without it being mixed in with the previous day's activity. How do I do that? The cumulative is fine but my owners only want to receive a notification for the day's activity. Eventually of course a weekly and monthly recap is expected as well but that would be in a separate report.

There is another broader, weekly Friday excel report also emailed via a distribution list containing inventory status of all remaining consumables, demographics data, and various other information used for the PBI visuals. In this case the same file is continuously updated by adding to the existing previous week's data. I guess it's sort of the same scenario as above where the report needs to overwrite the previous week's report so that it doesn’t duplicate.

Is there a query step that automatically replaces all of the data with the new report without adding cumulatively since each weekly report will already contain the old and new information housed in one table?

Since each transaction is dated, I can still use a timeline visual based off that date so I don’t think it matters if it is overwritten.

For the daily report referenced, is there a way to show day over day change if I am processing this new report each night through the query steps? Such as how many sales so far, and how many from yesterday? The report itself does not track anything other than the day's activity. For the daily sales report, it starts on Saturday and ends on Friday as the weekly cycle.

I just need someone to walk me through the logistics of these kind of common reporting procedures. I am more or less starting all of this from scratch because there was no one else that handled it this way other than just receiving emails daily and weekly and everyone opened up the excel sheets to consume whatever information they wanted. This is all new to me and while the logistics might be easy for some, I could be completely missing the easy solution and better way of handling the logistics.

Any help would be appreciated.

Thank you

[djr001]

Hi.

I have several questions:

I receive daily email excel attachments containing new sales report information each night via an email distribution list. About 10 sales reports each night which are all from different cost centers at different locations. To point out the obvious, each day comes with new and updated sales numbers for that day's activity. I created a PBI query set and the visuals that follow. I have been copying the M-script into each new PBI file created so that I don’t have to re-create each query step for each report. I don’t want the data to be added to the existing (previous day's) report, I want to replace it since each day's report is stand alone. Is there a better way to do this and even better automatically?

I want to update the query/visuals with the new data set each night and republish but every cost center has multiple different people that send the excel file and they all name it whatever they feel like for that day. There is no consistency on how the files are named.

If I setup a flow procedure to have the email attachment sent to a folder in onedrive, is there a way to automatically delete the previous file or do I have to manually complete this step each night? I am assuming if I do not delete the file, it will just keep adding to the existing report. That might not be a problem if I knew how to only report that day's activity without it being mixed in with the previous day's activity. How do I do that? The cumulative is fine but my owners only want to receive a notification for the day's activity. Eventually of course a weekly and monthly recap is expected as well but that would be in a separate report.

There is another broader, weekly Friday excel report also emailed via a distribution list containing inventory status of all remaining consumables, demographics data, and various other information used for the PBI visuals. In this case the same file is continuously updated by adding to the existing previous week's data. I guess it's sort of the same scenario as above where the report needs to overwrite the previous week's report so that it doesn’t duplicate.

Is there a query step that automatically replaces all of the data with the new report without adding cumulatively since each weekly report will already contain the old and new information housed in one table?

Since each transaction is dated, I can still use a timeline visual based off that date so I don’t think it matters if it is overwritten.

For the daily report referenced, is there a way to show day over day change if I am processing this new report each night through the query steps? Such as how many sales so far, and how many from yesterday? The report itself does not track anything other than the day's activity. For the daily sales report, it starts on Saturday and ends on Friday as the weekly cycle.

I just need someone to walk me through the logistics of these kind of common reporting procedures. I am more or less starting all of this from scratch because there was no one else that handled it this way other than just receiving emails daily and weekly and everyone opened up the excel sheets to consume whatever information they wanted. This is all new to me and while the logistics might be easy for some, I could be completely missing the easy solution and better way of handling the logistics.

Any help would be appreciated.

Thank you