Filtering results from the Select connector

(0) Share

Report

Posted on by Automate123

128

Hi Power Automate community,

I am currently working on a flow that is almost complete, however I need to make a few changes.

The flow itself basically calls Jira's REST API, from the JSON I use the Select connector to filter out results, and then put this data into a CSV file.

The issue I am having is related to the "Created" field. The JSON for this can be seen below:

"created": "2021-04-19T09:58:13.469+0100"

When I use the following expression on my Select connector:
item().fields.created

I extracted the correct value of:
2021-04-19T09:58:13.469+0100

However, if possible I would only like the date and time to be extracted. With the last 9 characters not needed. Also if possible to replace the T with a space to improve the format and readability. My desired outcome can be seen below:

2021-04-19 09:58:13

Is there something I could add to my expression, or modification I could make to my expression that would allow me to achieve this?

Thanks in advance!

All responses (14)

Answers (1)

Automate123 128 on at

Like (1)

Report

Re: Filtering results from the Select connector

Hey @CFernandes

Thank you very much for the response and for all your help. Could I ask where would I put the expression you just sent?

Ahh I see I would need to add a new convert timezone connector?

I will try the expression you gave and see if that does the trick.

Thanks again
Verified answer

CFernandes 8,295 Most Valuable Professional on at

Like (1)

Report

Re: Filtering results from the Select connector

Hey @Automate123

Woohooo.... I am glad it works...

Yes, it is related to Timezone. For details see -

https://support.microsoft.com/en-us/topic/converting-time-zone-in-microsoft-power-automate-8bce2441-1859-4d76-2f51-985d9c96534d

I think you use -

convertTimeZone( item().fields.created,'UTC','Eastern Standard Time','yyyy-MM-dd HH:mm:ss')

Note - You will have to select your time zone instead of Eastern Standard Time

As we have managed to resolve the original question related to Date-Time formatting, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Automate123 128 on at

Like (0)

Report

Re: Filtering results from the Select connector

Hey @CFernandes

Thank you for your response. I tried what you suggested and it works!
There is only 1 issue that I found with the results.

The results seem to be 1 hour behind the expected outcome.

So if I am expected the time to be 12:17:00 I am instead getting back 11:17:00.

Do you have any ideas as to why this is happening?

Maybe something to do with timezones?

Thanks
CFernandes 8,295 Most Valuable Professional on at

Like (1)

Report

Re: Filtering results from the Select connector

Hey Mate, Try passing.

formatDateTime( item().fields.created,'yyyy-MM-dd HH:mm:ss')
Automate123 128 on at

Like (0)

Report

Re: Filtering results from the Select connector

Hi @CFernandes
Thank you for a quick response. I did not pass the output of the Compose action previously.

This is currently the Compose connector. It is just before the Select connector

When I add the output of the Compose action to the created field

output I get is the following:

Could I ask you to clarify what I should add to the Created field?
My previous expression of item().fields.created?
The output of the Compose action?
Both?

When adding both the results returned can be seen above.

Any ideas on how I could resolve this?

Thanks
CFernandes 8,295 Most Valuable Professional on at

Like (1)

Report

Re: Filtering results from the Select connector

Hey Mate,

It shows me -

Are you sure that you have passed in the output of the Compose action.
Automate123 128 on at

Like (0)

Report

Re: Filtering results from the Select connector

Hi @CFernandes

I have added the Compose action as you suggested before my Select action.
I added the expression you gave into the input.

The Select expression for the Created field is set to the following:
item().fields.created

When I tested the flow, the output from the Created field was the same and had the unwanted characters.

Any ideas on how to achieve just a date and time format?

Thanks
CFernandes 8,295 Most Valuable Professional on at

Like (0)

Report

Re: Filtering results from the Select connector

Let try the other way round -

Add the Compose action -

In the Compose action add the formula -

formatDateTime(<Created>,'yyyy-MM-dd HH:mm:ss')

Let me know if the that works.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Automate123 128 on at

Like (0)

Report
Re: Filtering results from the Select connector

Hi @CFernandes
Yes Parse JSON connector does return the created field.

When I add the expression you sent

formatDateTime(outputs('Compose'),'yyyy-MM-dd HH:mm:ss')

I am getting the following error.
In relation to you last comment, where would I add the Compose connector? Before the Select connector?

Is there not a way of achieving this just by modifying my current Select expression?
Thanks
CFernandes 8,295 Most Valuable Professional on at

Like (1)

Report

Re: Filtering results from the Select connector

@Automate123 Parse JSON does not return you the Created field directly?

As a test add a "Compose" and show me the value of the Created field.