how to use macro

[WuCong]

in a flow ,I have serval fields which need to deal with partial date.

partial date is when date format is UN-UNK-2002 deal to 31-12-2002

UN-Jan-2002 to 31-Jan-2002, the day part is always UN .

deal to max date for the day part by the month.

how can I do this

[AMiller_Tri · Accepted solution]

Hi @wu cong?, thanks for the question.

If I understand correctly, your dates look like this:

A solution to this would be to use the DATEADD() function to

1. calculate the 1st day of the following month [DATEADD(updatetime, 1, month)]
2. subtract one day from it to find the last day of the current month [DATEADD(updatetime,-1,day)]

But first, we'll need to fix the given format to a valid date.

So our first step would be to replace these "UN"s with a '01':

Then we nest a DATEADD() within another DATEADD() to calculate this month's last day date:

While you can convert this step (or 2) into a macro, I would advise against it since the number of input columns in a macro is static. Instead, you can use these steps as is to edit as many columns as you'd like, with the $col annotation to refer to their original value.

I hope this helps.

Please feel free to ask any further questions.

Thanks,

Amit.

[WuCong]

great ,it is wonderful. thanks very much .

 you can use these steps as is to edit as many columns as you'd like, with the $col annotation to refer to their original value

--with this statement , I have no idea to deal with, can you give an example?

[AMiller_Tri]

Yes, of course.

When we use the "Edit with formula" transform instead of "new formula", we can reference the original value of the column.

so the "$col" value references the columns used in the values parameter

In this example we have 2 columns - updateTime+UpdateTime_copy.

We can change both columns simultaneously by utilizing '$col'.

You can also use the "all" or "range" option instead of "multiple" in the "columns" section if you want to work on a range of columns or all the columns.

I hope that helps clear the matter.

Feel free to ask any further questions.

Thanks,

Amit.