cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Converting and calculating Json

Hello, im faily new to Flow. I have created a custom connector to get data from G Suite. I work in education and we are trying to work out how much Chromebooks are used in our schools. I am getting the below JSON from the connector. I only require the active time, from the previous day. I would then like to split the Data and Active Time so i can use in PowerBI. I have never done calculations In Flow and wouldnt know where to start. If anyone has any suggestions or guidance you can point me towards i would be really greatful. 

 

"activeTimeRanges": [
{
"date": "2019-04-28",
"activeTime": 7590159
},
{
"date": "2019-04-30",
"activeTime": 3870064
},
{
"date": "2019-05-03",
"activeTime": 3210057
},
{
"date": "2019-05-06",
"activeTime": 1020023
},
{
"date": "2019-05-07",
"activeTime": 360005
},
{
"date": "2019-05-08",
"activeTime": 11370196
},
{
"date": "2019-05-09",
"activeTime": 3090032
},
{
"date": "2019-05-10",
"activeTime": 60000
}
],

2 REPLIES 2

Hi @AJ42 I'm hoping that I'm understanding your requirement correcty, so here is my approach.

 

As I have a load of JSON being returned from GCloud, I will first of all need to parse the JSON to make it useable within Flow. You can use the "Parse JSON" action to do this. Add the action into your Flow and then generate the schema by copying your JSON into the "Create schema from payload" link.

 

What should be returned from the parse action, is an array of objects which I can then interact with. You can use the "Filter Array" action to perform a select on your data, particularly on the date field. To get yesterday's date in the format you want, you will need to combine a number of expressions together:

 

formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')
 
formatDateTime(<datetimestamp>,<format>) will allow you to specify the format of the date. In this example above I have formatted it year-month-day so that it matches your JSON.
 
addDays(<datetimestamp>,<number to add>) will allow you to add or subtract days from the current day. There are other date based arithmetic expressions, but I like to use this as it's simple. In the example above, I have subtracted one day by "adding" -1.
 
utcNow() will get the date time stamp at the point that the expression is called.
 
I hope that helps
 
If you have found this post useful, please give it a thumbs up. If it has answered your question, please accept it as the solution so others can benefit.

@MattWeston365




Did my reply help? Please give it a thumbs up.
Did I answer your question? Please mark my post as a solution!
If you liked my response, please consider giving it a thumbs up. THANKS!


Proud to be a Flownaut!





Community Leader: Black Country PowerApps & Flow User Group

@MatthewGrantAU Thank you so much, that was a great help. It's taken me a week but i have got a flow that is giving me the data i need. 

 

I am now trying to convert Millisecconds to Hours and Minutes in PowerBi from a streaming data set. Has anyone got any links to guides or formulars for this? Im having a look around and seeing what i can find

Helpful resources

Announcements
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (44,254)