Extract data from excel based on max date and filter

[Nagin]

Hi,

I am quite new to power automate, and reaching out to the community if anyone could provide a solution to my query.

The automation that I am trying to develop in PAD is that I receive a weekly email containing an excel attachment, from which I require a hidden sheet where the data is stored.

The datasheet has several columns, with the two keys columns ‘date’ and ‘provider’, I need to only extract data that relates to Provider 1 and insert this into a master sheet and only need to extract rows where the date is greater than the max date for the provider in master file, so in this example I would only extract the rows with a date greater than 18/03/23 for provider 1 from the data sheet and insert it into the master file.

Data Sheet

Date	Provider
18/03/23	Provider 1
18/03/23	Provider 2
19/03/23	Provider 1
20/03/23	Provider 1
21/03/23	Provider 3
22/03/23	Provider 1

Master file

Date	Provider
14/03/23	Provider 1
15/03/23	Provider 1
16/03/23	Provider 1
17/03/23	Provider 1
18/03/23	Provider 1
18/03/23	Provider 4

I have currently set up the flow below, where I have managed to unhide the sheet and query the datasheet, in my flow I have saved a copy of the extract which I don’t necessarily need. The issue I am having is trying to find a solution to only extract the ‘query result’ data where the date is greater than the max date for that provider within my master file

Any help on this would be much appreciated

Thanks

Nagin

[Nived_Nambiar]

Hi @Nagin 

Just to know ,

is the query result is the output data from master file for a specific provider ?

[Nagin]

Hi @Nived_Nambiar 

The query result is from the email extract that I receive, where I have extracted the specific provider, and looking for a solution where I extract only the rows that are greater than the max date for that provider within the master file and then update the master file with the query result

Thanks

Nagin

[Nived_Nambiar]

@Nagin 

What i would suggest is 

once you have the query data for that provider from attachment, navigate to master excel use that provider to filter [ I would suggest to use sql approach for master datatable as it would be faster] and then sort it using dates in descending order. Then pick up the top row Date column to get the date value, That data value would be latest. Then go to query result got from email attachment, filter with the date value obtained and then add the result to datatable (master).

This way you can give a try 🙂

[Nagin]

Hi @Nived_Nambiar 

Thank you for you help, I have filtered the email file and sorted by DESC and and filtered the master file and sorted by DESC and created a variable for the master file to extract the last date for that provider which is '10/03/2023'.

How do I now write a variable to filter the 'emaildata' where the column for date is greater than 'lastdatemaster' and then add this to the 'masterdata'

Thanks

Nagin

[Nived_Nambiar]

I hope you can rely on SQL queries to filter emaildata again based on that date.

To add the data, you can use write data to excel worksheet action

Hope this helps 🙂

[Nagin]

Hi @Nived_Nambiar 

Would I able to reference my 'lastdatemaster' variable within sql statement for my 'emaildata'

e.g SELECT * FROM [MasterData$)

Where date>(%lastdatemaster%)

Thanks

Nagin

[Nived_Nambiar]

@Nagin 

Just try this way as well if above one does not work 

SELECT * FROM [MasterData$)

Where date>('%lastdatemaster%')

[Nagin]

Hi @Nived_Nambiar 

Thanks it has nearly worked, I have got an error in my SQL statement for Data Type mismatch. The data column in both the email extract and master file are set as as date. However I can notice that when creating the 'lastdatemaster' variable it has left a trailing timestamp and assuming this could be the issue, is there way to remove the datestamp when creating the variable

[Nived_Nambiar]

If it is datatype mismatch, 

would you try with the below sql query

SELECT * FROM [MasterData$)

Where date>(%lastdatemaster%)

Regarding your question on changing date format, you can use convert text to datetime to convert to desired format

[Nagin]

Hi @Nived_Nambiar 

I have tried to convert text to datetime however still have a mismatch, could there be any other reasons why there would be a mismatch

Thanks

Nagin

[Nived_Nambiar]

@Nagin 

After some tryouts, i am able to determine the date comparision

check the below screenshots 

here %dat1% variable is like this

Result i got 

Hope it helps 🙂

[Nagin]

Hi @Nived_Nambiar 

Thanks that worked, however now my 'emaildata' data result only brings back the column headers and no rows of data. Any idea why this is happening

Thanks

Nagin

[Nived_Nambiar]

Do one thing ,

Instead of provider as filtering , see whether it works by date only 

Just to see whwther it is due to the provider ?

[Nagin]

@Nived_Nambiar 

Still the same issue with no rows being shown, thanks for your help.

[Nived_Nambiar]

Are you using formatted date ?

[Nagin]

Hi @Nived_Nambiar 

Yes, both excel files (emaildata & masterdata), the period field is set to date format (dd/mm/yyyy)

[Nived_Nambiar]

@Nagin 

could you try the format which i have used dd-MM-yyyy hh:mm:ss 

[Nagin]

@Nived_Nambiar 

Tried and still no bringing through any rows.

Thanks for your help on this issue, I carry on trying different formats for the date