SQL Transform Data Calculated Column

(0) Share

Report

Posted on by Anonymous5

107

Hi Everyone,

I am working on a flow that will connect to an SQL table using the "Transform Data" action. I have the table customized the way I want it but the final step will require a new calculated column that will subtract the due date of a particular item from today's date to arrive at "days until due". I do not see the ability to add a calculated column in the Flow Transform Data Action. All I see is Add Conditional Column and Index Column. What I would need is a new column with the formula "DUE_DATE - 'TODAY()'" or something to that effect.

The final step would be to take this table output and convert to HTML for emailing.

Thanks for the help,

Rob

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

v-litu-msft on at

Like (0)

Report
Hi @Anonymous5,

There is no date operation function like Datediff, but you can use the dayOfYear() function and sub() function to achieve get the diff between two dates. Such as:

sub(dayOfYear(variables('DueDate')),dayOfYear(utcNow()))

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Anonymous5 107 on at

Like (0)

Report

Hi Lin,

Thanks for the suggestion. My main issue is that the options for inserting formulas in the Flow SQL Power Query editor appears to be quite limited. Just a few minutes ago I discovered how to "Add Columns" using the functions for calculations like add/subtract/divide/etc. This along with the "Age" function for the date columns has opened up a few more options for me.

Directly from the table of Work Orders I am working in is a "due date" column (as a date) and "calendar interval" column (in days). What I would like to do is apply the interval days to the date to arrive at a new date as M/D/YY. From the column options for dates, there doesn't appear to be much in the way of calculations. Any ideas here?

Rgds,
Rob

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Anonymous5,

You can use the addDays function, and the 'M/D/YY' could not work in Flow, you should use the 'M/dd/yy' to do it, for example:

addDays(startDate, number of days want to add, date formate)

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Anonymous5,

You can use the addDays function, and the 'M/D/YY' could not work in Flow, you should use the 'M/dd/yy' to do it, for example:

addDays(startDate, number of days want to add, date formate)

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Anonymous5 107 on at

Like (0)

Report

Hi Lin,

I should have been clearer. This calculation needs to take place within the SQL Transform action. That is, I'm creating and manipulating the data inside this query. Here is a screenshot below:

What I want to do is create a NEW column that will add the number of days from the "CALC_CALENDAR_INTERVAL" column to the "LAST_DONE_DATE" column to produce a new column called "DUE_DATE" but will be in the format "mm/dd/yyyy" as above. From what I'm seeing, there are not any native formulas under "Transform Column" inside this query to add days to a date. Perhaps there is a custom formula that could be used but I'm not familiar with the language used for the steps of the Power Query inside Flow.

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @Anonymous5,

If you not familiar with the SQL query, you can refer to this tutorial:

https://www.w3schools.com/sql/default.asp

And this is the ODATA query:

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Anonymous5 107 on at

Like (0)

Report

Hi Lin,

Thank you for the links. I have not done much with direct SQL Queries and ODATA so I'm sure I could brush up there. My current flow looks like the following:

Currently I am manipulating / merging 2 tables and several other calculations within the SQL Power Query action (my earlier screenshot). I have the table looking the way I want it before it is output as an array and converted to an HTML table for emailing.

Forgive my ignorance but I don't see how a direct SQL table query will help me get to the point where I will have a calculated column from separately manipulated columns from the Power Query already in my Flow.

B/R

Was this reply helpful? Yes No