Is it possible to have a new PB table that holds each file's summary data, even if it gets removed?

[Devtr]

Hello PB teachers.

I get files for the orders for a particular month randomly. It has a naming rule. That way I tell Power Query what month-year is for and when it was submitted. For example:

Orders_01-2023_12-31-2022

01-2023 is the month and year for the orders received.

12-31-2022 is the report date.

These files have three simple columns. Order date, quantity, and amount.

order_date	qty	amt
1/1/2023	50	1500
1/10/2023	75	1900
1/15/2023	35	1200
1/25/2023	10	250

It will look like this after adding two more columns:

I get the order files randomly. We want to keep 2 to 4 of the latest files in the folder for the PB. And delete the old files.

My goal is to keep track of the pace of the orders!

Is it possible to have a new PB table that holds each file's summary data, even if it gets removed?

And this should start on the first day of the month and end on the last day of the month each month.

The summary data can be:

report_date	orders_total	qty_total	amt_total
1/1/2023	7	315	8950
1/3/2023	8	360	10650
1/4/2023	10	420	12200
1/5/2023	13	550	16100
1/8/2023	14	545	16000
1/11/2023	16	585	17150

I have attached the link here to download the order-submitted Excel files from 12-31-2022 to 01-11-2023 with the Pbix file.

There is an Excel file for the desired table as well.

Thank you so much.

@amitchandak 

@Ashish_Mathur 

@FreemanZ 

[Ashish_Mathur]

Hi,

i do not think that is possible.  If you want the functionality to add files, then remove files will also update the table on refresh.

[Padycosmos]

I hope this video helps:

https://www.youtube.com/watch?v=y7ucCjtADsg

[Devtr]

Hi @Padycosmos 

How does this work after the data files are removed? Because I want to keep all the summary data.

Thank you for your reply.

[Padycosmos]

In that case ,y ou can create a New table using the Summarize() dax function. https://learn.microsoft.com/en-us/dax/summarize-function-dax

[Devtr]

What happens when I remove some data files and add new ones?

Will it change the data in that table?

Thanks.

[Padycosmos]

Sorry, I have not tried adding and removing files and so am unable to say.

[Devtr]

I added a table with Summarize function:

Tbl_Order_Pace = 
SUMMARIZE('Orders',Orders[report_date],
    "Total_Orders",COUNT(Orders[order_date]),
    "Qty_Total",SUM(Orders[qty]),
    "Amt_Total",SUM(Orders[amt]))

But after removing files and adding new ones, it removed the data related to those deleted files as well. 

I don't know what to do.

[Padycosmos]

The only option I can think of is to export the summarized table as a csv file .