Answered

Extract Excel Table, Filter by Date, Email Table

(0) Share

Report

Posted on by BenderMatt

Hello,

I'm attempting to do something that seems easy, but I'm having difficulty. I'm attempting to List Rows Present in a Table, the table containing:

I'm attempting to filter the output to only display rows that display "DateShipped" within the past 7 day. Then write an HTML table and display it in an email.

I have been able to display the entire table, but the number of ways I have tried to filter by the date has been unsuccessful. I have tried converting the date format with addDays('1899-12-30', int(item()['DateShipped']), 'MM/dd/yyyy').

Can anyone recommend the proper steps to achieve this?

I have the same question (0)

All responses (6)

Answers (1)

v-litu-msft on at

Like (0)

Report
Re: Extract Excel Table, Filter by Date, Email Table

Hi @BenderMatt,

Currently, the date in Excel table has no need to convert from a number to a date, you could use the column column in PowerAutomate directly, please refer to this blog:

Excel Date changes in Power Automate - Power Platform Community (microsoft.com)

For example:

item()['DateShipped']

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
BenderMatt 21 on at

Like (0)

Report

Re: Extract Excel Table, Filter by Date, Email Table

I created this flow 1/25/2020, and the output from the following still yields a number.

The excel table originated in Excel desktop and then I migrated it to a Microsoft Teams location. Does that matter?

Was this reply helpful? Yes No
Verified answer

v-litu-msft on at

Like (1)

Report
Re: Extract Excel Table, Filter by Date, Email Table

Hi @BenderMatt,

Ok, This change was due to a regression bug. Microsoft rolled it back on 1/21/2021.

Let back to the problem, when you compare two dates, the date format should be yyyy-MM-dd:

Convert the date column from number to date string:

addDays('1899-12-30', sub(int(item()['DateShipped']),7), 'MM/dd/yyyy')

Get date 7 days ago:

addDays(utcNow(),-7,'yyyy-MM-dd')

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
BenderMatt 21 on at

Like (0)

Report

Re: Extract Excel Table, Filter by Date, Email Table

Hi @v-litu-msft

I tried your expression addDays in the filter criteria. I'm puzzled, as when the expression is used in the first portion of filter criteria it does not modify the date numerical value. If I place the same expression in the Value portion of the HTML table generation it does modify the date value.

Although, nothing is filtered. 🧐

Was this reply helpful? Yes No
BenderMatt 21 on at

Like (0)

Report

Re: Extract Excel Table, Filter by Date, Email Table

I found the error causing the date expression to not work.

Now I'm stuck on how to perform an if statement. I wish to check to see if the filter array output contains data, if so, create HTML Table then email(HTML Table data), else, send a different email (nothing to report).

Was this reply helpful? Yes No
BenderMatt 21 on at

Like (0)

Report

Re: Extract Excel Table, Filter by Date, Email Table

@v-litu-msft

Okay. I finally got it.

Your conversion and filter criteria worked, it just doesn't show the date format in the output box after performing a test, it still shows the numerical date value...but it works correctly.

As far as the filter criteria, I used the length function the find out if the array output of the filter was greater than 0, which worked for my purpose.

It works!

Was this reply helpful? Yes No