• i have a monthly report, which has a record of all tasks i have at hand on some system i use

• every month i get new tasks, but also i finish some tasks

• the report is an uptodate report, every month when generated, it captures all tasks i have still in progress, it does not show completed tasks, if i completed a task on the system it will not show in the report

• I want to create static base table to view all tasks in progress and completed as well

• i also want to use power aps to read and write from this table ,which i can do , i am struggling to know how to source the data to cater to my needs

what i want is

• the table to be updated with the delta every month, so new tasks from the following month are added
• the table to eliminate duplicates, if a task is reported last month and still ongoing , it will show in this month report again,i don’t want 2 entries for it,just one , and if possible perhaps a column showing that the task was reported last month, this month and even the month afterwards if it shows up in the report
• the table to be static, i want to use power aps to write against each task in a column saying it is completed or pending or whatever , so its not a read only table , i need to write in to it as well
  -i also prefer if things are as seamless as possible, kinda hoping to just upload the monthly report to a share point and everything else is automated

i realize i am asking for a lot , but i just need to know if anyone went through something similar and know the best way to go about putting together something like that

its basically a table that needs to be fed updates every month , and needs to remain static because its not only for viewing but writing as well

• i created a power app reading from the base table in a work book on share point

• the power app writes into the table in the Status field, allowing me to assign statuses to the tasks

• i then uploaded this month report which has all inprogess tasks to another sharepoint

• i then used power query in the base table to fetch this month report from the other share point

• i intend to do this for next month report as well ,by using the combine all excel files on sharepoint option , then clearing duplicates in power query to make sure i only have one entry for every task that is in progress

but then obviously i realized this doesnt work , because in every query i run i will overwrite any changes i did from the power app, so any task assigned a status in the status column will go blank after refreshing the query

also using this method means by the end of the year it will be quite a big query to run to get all the data

i say tasks which sounds a few , but they are actually about a thousand every month, so thats 10k in a year and will keep growing