Import date from excel rows to HTML table

[PierrickBIT]

Well, first, I know there have been like dozens of topics like this and I'm sorry to bother.

I've read for many hours now and cannot get a solution.

Pasted below are some good links that helped people

Power Automate flow HTML Table Formatting in Email | Flows & SharePoint - YouTube

https://powerusers.microsoft.com/t5/General-Power-Automate/Manipulating-Flow-Excel-Date/td-p/365876

https://powerusers.microsoft.com/t5/Building-Flows/Datetime-string-must-match-ISO-8601-format-Can-t-...

https://www.bythedevs.com/post/working-with-date-time-data-of-excel-in-power-automate

Unfortunately, maybe I'm cursed, maybe I'm dumb, but guess what? I cannot get out of this.

Anyway, here we go: an excel sheet, formatted as a table, contains (among other things) dates. Those dates are Date-type-excel-formatted. I added a column, containing same dates but formatted as text, because... why not, just to run some tests.

Here is the very beginning of the flow. Note that I forced the date format to ISO8601 (as I read a couple of times to be the magic trick)

Next step is to select the data I will later paste in the HTML table

The formula for the date is the following: formatDateTime(addDays('1899-12-30',int(item()?['DATE_BRUT'])),'yyyy-MM-dd')

This error I get (actually the end of it):

"@formatDateTime(addDays('1899-12-30',int(item()?['DATE_BRUT'])),'yyyy-MM-dd')"
}' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

The error is the same if I use DATE (date format) instead of DATE_BRUT (text format).

I don't think the rest of the flow is relevant because I'm stuck just above, but here it is

The "compose" step is used to make a beautiful table, like Reza Dorrani showed in his video I linked earlier.

That may be the ultimate question about this issue, so if you please help me you will be remembered as a true hero.

[grantjenkins]

When you set your List items present in a table to ISO8601, what format do the dates come back as? It should come back as standard ISO format. To check, click on Click to download (see screenshot below) on your List rows present in a table and it will show all the raw data in a new window. You can press CTRL+F then search for your date fields to see the actual values. If they are coming back as ISO format (yyyy-MM-dd) then you don't need to use the expression to convert. However, if still coming back as a serial date (44953 for example) then your expression should work.

If you've set to ISO8601 and the dates are coming back in the format yyyy-MM-dd, then your expression would throw an error, which might be what's happening here.

[PierrickBIT]

Thanks for your time Grant

You are right, when I set the list format to ISO8601, the date turns to something like: 

That aint that far to good but the timestamp after the day is useless.

That is the reason why I adapted the formula to get rid of the correction based on 01/01/1900 trick.

The new formula is: formatDateTime(item()?['DATE'],'yyyy-MM-dd')

But an error occurs too!

"Date": "@formatDateTime(item()?['DATE'],'yyyy-MM-dd')"
}' failed: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'. 

[grantjenkins]

When you click on Click to download and view all the data, can you check to see the name of the column? In your expression your using 'DATE', but maybe that's not the actual internal name - it will be whatever you see in the output. Your expression looks fine, so assuming this might be the issue.

//Need to validate the name of your column

formatDateTime(item()?['INTERNAL_NAME_OF_YOUR_COLUMN'],'yyyy-MM-dd')

[PierrickBIT]

This is an extraction of the output. See at the end "DATE" field

{"statusCode":200,"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","Vary":"Accept-Encoding","x-ms-request-id":"6784170a-3b06-4d45-989e-5ec5aa7c996e;a61b6f34-ceac-4316-a99c-2b63e8cae97d;3a9734e2-e5bf-421e-a3f4-dfd41a03b8cf;1908d1d0-d04a-470a-914c-6fb63469de61","OData-Version":"4.0","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","x-ms-apihub-obo":"false","Cache-Control":"no-store, no-cache","Date":"Tue, 31 Jan 2023 08:05:44 GMT","Content-Type":"application/json; odata.metadata=minimal; odata.streaming=true","Expires":"-1","Content-Length":"99758"},"body":{"@odata.context":"https://excelonline-we.azconn-we-002.p.azurewebsites.net/$metadata#drives('b%21rZkVczBYwEKlF-nJgwkFAYDBVHCtrX9KmO77I8xCCIFwWBqTwm3eRItxAt6XfkOa')/Files('01U43LYLSS2MJKUE5575C2R3QLV3AFOXT4')/Tables('%7B00000000-000C-0000-FFFF-FFFF00000000%7D')/items","value":[{"@odata.etag":"","ItemInternalId":"59b69bbb-562e-4f9c-9caf-52c1666eaf76","SOMME":"20","TYPE":"virement","DATE":"2023-01-23T00:00:00.000Z","DATE_BRUT":"44949",

[grantjenkins · Accepted solution]

Next thing to check - are the dates filled in for all your rows, or are some of them blank? If some of them are blank, then you would need to check first before using the formatDateTime expression. See example below:

if(empty(item()?['DATE']), null, formatDateTime(item()?['DATE'],'yyyy-MM-dd'))

[PierrickBIT]

My God you are right @grantjenkins ! A few lines of the excel tables are actually empty, THAT is the issue in the first place!

Look at this ugly thing that comes out HTML table in the email (I had to seriously zoom out) :

Man, your parents should have named you Great, not Grant.

Well, I will gently smash this Accept as a solution button and send you all my love.

BONUS of the most annoying client ever:

But, could'nt that flow be like .. smarter? I mean it would be awesome if I had the opportunity to filter every empty line of the original excel table within the excel action, right?  

I found this : Solved: Re: Ignore blank rows using ODATA filter - List Ro... - Power Platform Community (microsoft....

which obviously add a filter action to the flow

Wouldn't that be more efficient to use the filter ODATA inside excel online action instead?

[grantjenkins]

Glad it got sorted 🙂

Using the Filter array would be an option if you were happy to completely exclude those rows where the DATE is blank. But as you mentioned, it would be better to just use the Filter Query in List rows present in a table. The Filter Query you would use is:

//Note we are using two single quotes

DATE ne ''

However, if you still wanted to return those rows to get the other data, and just leave the date blank in your output, then using the expression to check if the date is empty would be the way to go. It looked like you were outputting additional fields such as NOM PRENOM into your HTML table.

[PierrickBIT]

DATE ne '' ...

what kind of a language is this? Sounds like old roman latin scriptus 🤣

Don't know where that comes from but it works, impressive!

You're right, there are far more fields that come with the date (like NOM PRENOM) but none can be empty. That's why your solution is typically what I needed.

anyway MANY MANY MANY thanks. You've just earned the status of Sir @grantjenkins . Admins, give that man a cookie!

Cannot wait to ask you another tricky question 😅.

Best regards