cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous5
Level: Powered On

SQL Transform Data Calculated Column

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

7 REPLIES 7
Community Support Team
Community Support Team

Re: SQL Transform Data Calculated Column

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.

Anonymous5
Level: Powered On

Re: SQL Transform Data Calculated Column

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

Community Support Team
Community Support Team

Re: SQL Transform Data Calculated Column

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:

Annotation 2019-11-11 161825.png

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.

 

Community Support Team
Community Support Team

Re: SQL Transform Data Calculated Column

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:

Annotation 2019-11-11 161825.png

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.

 

Anonymous5
Level: Powered On

Re: SQL Transform Data Calculated Column

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:

 

2019-11-11_10-13-40.jpg

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.

 

 

Community Support Team
Community Support Team

Re: SQL Transform Data Calculated Column

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.

 

Anonymous5
Level: Powered On

Re: SQL Transform Data Calculated Column

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:

 

2019-11-12_12-08-09.jpg

 

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,489)