No row was found with Id

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I am building a flow to identify a date in increments of 7 from an Excel using 'Compose' and 'Get a Row' action.

The dates on the excel are formatted in "YYYY-DD-MM format

Although the "Key Value" output shows as "2023-06-05", PA is unable locate the same date in same format on my excel.

Any pointers? Screenshot attached

{

"status": 404,

"message": "No row was found with Id '2023-06-05'.\r\nclientRequestId: 900b3751-8a3f-435c-8f6f-d89851dee4bc\r\nserviceRequestId: 1bde7782-0b94-4dc4-a4ef-6a651b3b8543;62763396-31cf-4016-9152-1a319ad51e2d;b966ac7b-8bb1-4d31-8433-9c591a1e2782",

"error": {

"message": "No row was found with Id '2023-06-05'."

"source": "excelonline-cus.azconn-cus-001.p.azurewebsites.net"

}

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Manish Solanki 15,169 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous

Using expression, you need to convert the date value to integer to update a row in excel file.
Pls refer to the below flow:
Expression used for converting to integer:
add(div(sub(ticks(startOfDay('2023-06-05')),ticks('1900-01-01')),864000000000),2)
In the above example, I have hardcoded the date value, pls replace it as per your need.

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ManishSolanki ,

I am not trying to update a row. I am trying to "Get a row" identify the right row as per the "Date" and retrieve associated data from that row and post as a message in MS teams.

If you see here, the Key Value and date from the error box match. But why is PA throwing the error that '2023-05-06' does not exist in the excel.

Thanks,
Bhavesh N

Was this reply helpful? Yes No
Verified answer

Manish Solanki 15,169 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous

Internally excel stores dates as integer so to search a row we need to search with its corresponding integer value. The expression which I had shared earlier is to convert date value to integer value. The same expression can be used with 'Get a row' action:
add(div(sub(ticks(startOfDay('2023-06-05')),ticks('1900-01-01')),864000000000),2)
Replace the date value ('2023-06-05') in your flow. If you set DateTime Format as 'ISO 8601', you will get the date time in proper format from the output of 'Get a row' action.

If this resolves the problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ManishSolanki ,

Thank you!, it worked.

Also, I replaced '2023-05-06' with output from Compose which was searching for dates with an increment of '7' from utcNow.

Thanks,
Bhavesh N

Was this reply helpful? Yes No
Sg44 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello. Is there a function that can be used to get today’s date to put in the expression? So it knows the date automatically.

Was this reply helpful? Yes No
Manish Solanki 15,169 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Sg44

Here is the required expression:
add(div(sub(ticks(startOfDay(utcNow())),ticks('1900-01-01')),864000000000),2)

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hello @Sg44 ,

Yes, you can use expression "utcNow()"
This will provide an output of todays date and time in UTC.

if you require just the date and exclude the time, you can instead use the below. It will be formatted to the way you desire.
"formatDateTime(utcNow(),'yyyy-MM-dd')"

Thanks,
Bhavesh N

Was this reply helpful? Yes No
Sg44 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Like this?

add(div(sub(ticks(startOfDay(formatDateTime(utcNow(),'yyyy-MM-dd'))),ticks('1900-01-01')),864000000000),2)

because currently I am getting the error “ no row was found with Id ‘45086’

Also if I want it in Eastern Time, would I need to include the following?

convertFromUtc(utcNow(),'Eastern Standard Time','yyyy-MM-dd')

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@Sg44 - Use the below only if you want to convert date into an Integer as Excel stores dates as an Integer.

add(div(sub(ticks(startOfDay(utcNow())),ticks('1900-01-01')),864000000000),2)

45086 converted to date is June 9th, 2023.

Thanks,
Bhavesh N

Was this reply helpful? Yes No