Unable to format dates in Excel table created via Power Automate

[Stacey_T]

Here's a summary of what my flow does: 

1. Upon file upload to SP (when a file is created)
2. creates a worksheet in that file (Create worksheet "New")
3. creates a table in that worksheet (Create table "New")
4. Gets all the rows already present in the Excel table (List rows present in a table, Table1)
5. Gets all the rows in the SP list I want to compare it to (Get Items ECAS)
6. Looks for items where the Engagement IDs match
7. If no match --> writes those rows to the New table

The flow runs successfully but I can't seem to format the dates to 'MM/dd/yyyy'. I've tried a couple of different things but because the table doesn't exist at the start of the flow, I'm unable to write formulas to format those specific columns. My 'Add a row into a table' action looks like this (no specific columns, just Row and Current item): 

Serial Number and ISO 8601 in the DateTime Format don't produce the results I want. I tried putting these expressions in but I'm still getting dates formatted as ISO 8601 in the New table. 

if(empty(items('Apply_to_each_in_New')?['Announcement Letter - Plan']), null, formatdatetime(items('Apply_to_each_in_New')?['Announcement Letter - Plan'],'MM/dd/yyyy'))

if(empty(items('Apply_to_each_in_New')?['Report Issuance - Plan']), null, formatdatetime(items('Apply_to_each_in_New')?['Report Issuance - Plan'],'MM/dd/yyyy'))

 

[v-yetonggu-msft]

Hi @Stacey_T ,

I did a test for your reference.

My SharePoint:

My Table:

My Flow:

{
  "ID": @{items('Apply_to_each')?['ID']},
  "AnnouncementLetter-Plan": @{if(empty(items('Apply_to_each')?['AnnouncementLetter_x002d_Plan']), null, formatdatetime(items('Apply_to_each')?['AnnouncementLetter_x002d_Plan'],'MM/dd/yyyy'))},
  "AnnouncementLetter-Actual": @{if(empty(items('Apply_to_each')?['AnnouncementLetter_x002d_Actual']), null, formatdatetime(items('Apply_to_each')?['AnnouncementLetter_x002d_Actual'],'MM/dd/yyyy'))},
  "ReportIssuance-Plan": @{if(empty(items('Apply_to_each')?['ReportIssuance_x002d_Plan']), null, formatdatetime(items('Apply_to_each')?['ReportIssuance_x002d_Plan'],'MM/dd/yyyy'))}
}

Please note that the internal column names in my SharePoint list are:

It is the internal name of the column that is to be referenced in the expression rather than the column's display name.

Also, the 'Apply_to_each' name I use is different from yours. You need to change the name when you quote the expression.

Best Regards,

Sunshine Gu

[Stacey_T]

To clarify, there is no data being written from the SharePoint list so I don't need those column names. It is just comparing the Excel data to the SharePoint list and writing the Excel data if it finds any changes.

I keep getting this error when trying to implement your solution, I have not yet figured out the cause. I also tried putting the Row function into a compose statement, which runs successfully but doesn't add the row to the table. 

{

  "error": {

    "code": "",

    "message": "The request entity's media type 'text/plain' is not supported for this resource."

  },

  "debugInfo": "clientRequestId: 3a1115be-0798-47ab-8f2d-afafc1238871"

}

[v-yetonggu-msft · Accepted solution]

Hi @Stacey_T ,

I think I got what you mean, I did another test:

When a row ID in Table1 does not match an item ID in the SharePoint list, the row will be added to another newly created table.

Table1:

New Table:

SharePoint List:

My Flow:

{
  "ID": @{items('Apply_to_each')?['ID']},
  "Announcement Letter - Plan": @{if(empty(items('Apply_to_each')?['Announcement Letter - Plan']), null, formatdatetime(items('Apply_to_each')?['Announcement Letter - Plan'],'MM/dd/yyyy'))},
  "Announcement Letter - Actual": @{if(empty(items('Apply_to_each')?['Announcement Letter - Actual']), null, formatdatetime(items('Apply_to_each')?['Announcement Letter - Actual'],'MM/dd/yyyy'))},
  "Report Issuance - Plan": @{if(empty(items('Apply_to_each')?['Report Issuance - Plan']), null, formatdatetime(items('Apply_to_each')?['Report Issuance - Plan'],'MM/dd/yyyy'))}
}

Best Regards,

Sunshine Gu

[Stacey_T]

Hi, I'm still not sure I'm following. My scope has changed slightly since the initial post but my error is the same. 

I am now uploading a template with two tables already created in Excel, the second one is called Changes. Right now I am trying to insert just one column of data, "CoSA", but get the 

"The request entity's media type 'text/plain' is not supported for this resource." error. 

Compose statement seems to be generating the right value? 

[Stacey_T]

Oh my gosh, I think it is because I was using parentheses () instead of curly brackets {} 🙄 😂 

[Stacey_T]

Unfortunately my date formatting is still coming over as ISO 8601 format despite figuring out the Row syntax but I have decided to go another route.