Rob Horton

Rob Horton is the Executive Director of Strategy and Marketing at ThreeWill. His experience includes over 25 years of leading software architecture, design, and development focusing on support tools, automation, and e-commerce for large corporations and his own small businesses.

Background

Being a consulting firm, you can imagine we do a lot of reporting. One of my responsibilities as the Solution Sustainment lead at ThreeWill is to provide monthly Sustainment usage reports to our clients. It typically involves massaging time tracking reports into more friendly formats. This process can be a bit time consuming, so I set out to automate the process and implement Microsoft Power BI reports.

I decided to use Microsoft Flow for the automation and was challenged to import the reporting data into Azure Table storage. Our reporting data does not contain unique fields that could easily be used as Row Keys and I wanted to make the process self-healing (avoid duplicate data while also being able to reprocess a report), so I needed to query the Table for existing entities by date. This required storing the date as a DateTime data type in the Table. If I didn’t need to query the Table by date, I could have simply added the date as a string and shaped it on the consumption side in Power BI.

The Problem

Flow does a lot of things well but doesn’t have any out-of-the-box functions for converting strings to DateTime data types. I needed to first format my “m/d/yyyy” string to a standard DateTime format.

The Solution

I created 3 Compose actions for each date as such:

To calculate the dateReportStartMonth, I used the following expression:

[code]
substring(concat(‘0’,split(body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’],’/’)[0]),sub(length(split(body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’],’/’)[0]),1),2)
[/code]

Where body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’] is the report’s start date. This expression adds a ‘0’ to the beginning of the month and takes the right two characters so that all months are depicted by two characters.

To calculate the dateReportStartDay, I used a similar expression:

[code]
substring(concat(‘0’,split(body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’],’/’)[1]),sub(length(split(body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’],’/’)[1]),1),2)
[/code]

Then I put the formatted DateTime string together in dateReportStart:

[code]
concat(split(body(‘Parse_JSON’)?[‘report’]?[‘Report Start Date’],’/’)[2],’-‘,outputs(‘dateReportStartMonth’),’-‘,outputs(‘dateReportStartDay’),’T00:00:00Z’)
[/code]

I also created dateReportEnd using similar expressions to format the report’s end date. With these dates formatted, I was able to query the Table with the following Filter Query:

(the first Output is dateReportStart and the second is dateReportEnd)

The final step is to insert the formatted date into the Table as a DateTime typed value. This is done by adding the data type into the JSON payload just prior to the value as such:

[code]{

“Company”: “<Company Output>”,

“Contract”: “<Contract Output>”,

“Date@odata.type”: “Edm.DateTime”,

“Date”: “<Formatted Date Output>”,

“Ticket”: “<Ticket Output>”,

“PartitionKey”: “<PartitionKey Output>”,

“RowKey”: “<guid() Output>”

}
[/code]

(where Outputs are values from other Flow Actions)

Conclusion

I suspect Microsoft will continue to add expressions to Flow and include string to date conversion at some point, making the process above simpler. Still, it is still possible with the current tools to format strings as acceptable DateTime inputs and use them to query Azure Table storage as well as populate typed entities in a Table.