cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
Community Support
Community Support

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.

Highlighted
Helper II
Helper II

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

Highlighted
Community Support
Community Support

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.

 

Highlighted
Community Support
Community Support

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.

 

Highlighted
Helper II
Helper II

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.

 

 

Highlighted
Community Support
Community Support

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.

 

Highlighted
Helper II
Helper II

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
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Top Kudoed Authors
Users online (11,356)