Looking for an advice in a complicated Datasource

[sajarac]

Hello there.

I am looking for someone who could point me in the right direction:

I have a complex scenario where the data is change very often and I can not connect my powerapps to my data source directly for many reasons. So basically I have to receive an excel file once a month with all the updates, then I do some cleanup in the file. Change to table, set the columns format etc. Once everything is done Then  I copy / paste the data into an existing file. The file is connected to my power apps to get the values. the file is in my onedrive and I am using the excel business connector. So now you can imagine the issues with delegation warning, filtering, performance, etc.

Now, why I am doing this? because I need to add some lookups conditions. I need to check is a new entry is added and is not in my mirror table.

I need the values in my mirror table to get the latest updates from the source file.

and I need to add some extra values "columns" to my mirror records and keep it.

I have tried Excel, Share point and Dataverse with terrible results.

In the below diagrams you will see a small representation of my data.

Thanks in advance for any help.

Regards,

And one more question. If excel is the only one that can meet the requirements no matter what if I have set the column to date and getting this in my powerapp.

Source:

Powerapp:

thanks again.

[Drrickryp]

@sajarac 

I'm thinking about your main problem but converting the date from a number in Excel can be done using the following formula if you can't change it in Excel.

Text(
    DateAdd(Date(1900, 1, 1), ThisItem.Lease - 2, Days),
    "[$-en-US]dd/mm/yy")

What is the connector for your source table? (Excel, SharePoint, Dataverse, SQL)?

I'm thinking that it might be easier to do most of work combining tables in Excel using Power Query.  FYI there is a new Power Query forum and it may be the place to figure out your issue.  See this video to get started.  https://www.youtube.com/watch?v=6lBqYInBldk  ; This one may be even better for you https://www.youtube.com/watch?v=0aeZX1l4JT4 

[sajarac]

Hey @Drrickryp , thank you very much for looking into my issue. I have been dealing with this for weeks. I though it was easy but in fact it is a little tricky. For now I will implement your solution for the date issue. I will let you know. And the connector for my excel table is excel online. (Business). 

[Drrickryp]

Check out the second video. I think you can completely automate your issue in power query.