Unanswered

I need help transposing data into a usable array.

(0) Share

Report

Posted on by martinav

3,340

I have some rather complex data. There is a LOT of it too. I'm looking for an inexpensive way (from a time perspective) to take this format into a flat array:

My data now:

[

{

"id":3070267953047428,
"rowNumber":1,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2019-12-09T17:12:52Z",

 "cells":[{"columnId":3898437847541636,"value":201013.0,"displayValue":"201013"},
 {"columnId":8402037474912132,"value":"JS75S PRV Dwg. 72784-2-1","displayValue":"JS75S PRV Dwg. 72784-2-1"},
 {"columnId":1083688080435076},
 {"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
 {"columnId":8299765713659780,"value":"jsmith@company.com","displayValue":"jsmith@company.com"}
 ]
},

{
"id":7573867580417924,
"rowNumber":2,
"siblingId":3070267953047428,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2018-07-03T18:25:56Z",

 "cells":[{"columnId":3898437847541636,"value":201015.0,"displayValue":"201015"},
 {"columnId":8402037474912132,"value":"JS75S316 General Arrg.","displayValue":"JS75S316 General Arrg."},
 {"columnId":1083688080435076},
 {"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
 {"columnId":8299765713659780,"value":"jsmith@company.com","displayValue":"jsmith@company.com"}
 ]
},

{
"id":6447967673575300,
"rowNumber":3,
"siblingId":7573867580417924,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2018-07-03T18:25:56Z",

 "cells":[{"columnId":3898437847541636,"value":201017.0,"displayValue":"201017"},
 {"columnId":8402037474912132,"value":"JS75S316 General Arrg. w/Flue","displayValue":"JS75S316 General Arrg. w/Flue"},
 {"columnId":1083688080435076},
 {"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},
 {"columnId":8299765713659780,"value":"jsmith@company.com","displayValue":"jsmith@company.com"}
 ]
}

]

I need to make it into this form. Or something very similar.

[
{

"id":3070267953047428,
"rowNumber":1,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2019-12-09T17:12:52Z",
"3898437847541636":201013.0,
"8402037474912132":"JS75S PRV Dwg. 72784-2-1",
"1083688080435076":"",
"3796166086289284":"2017-01-10T14:54:30Z",
"8299765713659780":"jsmith@company.com"
 
},

{
"id":7573867580417924,
"rowNumber":2,
"siblingId":3070267953047428,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2018-07-03T18:25:56Z",
"3898437847541636": 201015.0,
"8402037474912132":"JS75S316 General Arrg.",
"1083688080435076":"",
"3796166086289284":"2017-01-10T14:54:30Z",
"8299765713659780":"jsmith@company.com"
 
},

{
"id":6447967673575300,
"rowNumber":3,
"siblingId":7573867580417924,
"expanded":true,
"createdAt":"2017-01-10T14:54:30Z",
"modifiedAt":"2018-07-03T18:25:56Z",
"3898437847541636":201017.0,
"8402037474912132":"JS75S316 General Arrg. w/Flue",
"1083688080435076":"",
"3796166086289284":"2017-01-10T14:54:30Z",
"8299765713659780":"jsmith@company.com"
}
]

I feel I could do this with a number of text replacement statements. However, that may occasionally not work quite right, depending if special characters are used in the actual values. However, if there is an easier way, that would be great. There has to be accomodation for no value as well. I also realize that a number may not be possible for the label. That can be addressed with a prefix, perhaps.

Thoughts on this??

Thank you!

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (2)

abm abm 32,856 Most Valuable Professional on at

Like (1)

Report

Re: I need help transposing data into a usable array.

Hi @martinav

Did you tried using Parse JSON. You need two Parse JSON. First one is for extracting the main elements and second for cells which contains column names and values. The second one (Cell columns) you need to append to a string to construct the json you required and finally you can construct the JSON you required.

Thanks

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

Re: I need help transposing data into a usable array.

@abm ,

That is what I was hoping I could do. However, I do not know how to set it up, since its somewhat of a "nested" situation. I did attempt it, but the data was unable to create a proper schema. Also, the data in "cells" is not in a normal tabular kind of arrangement.... if that makes sense. I have had a similar situation in SQL where I had to pivot those tables prior to doing keyed joins. If that makes any sense.

Unfortunately, I am unable to control the format the data is provided. In fact, I have already manipulated the large data file to chop off the header and trailer info.

If I get this part solved, I will hopefully have a hands-off method to create a legit table of data from Smartsheet. Its too much of an issue to post all at once. This is the last piece to get working, and its good to go.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
Re: I need help transposing data into a usable array.

It seems there is another challenge. It seems Smartsheet has the ignorant idea of having a mixed text/number column type. Looks like there is no strictly text field. This screws up the scheme. I actually got the scheme to work, but it failed because of the mixed text field. I have no idea how to fix this. However, if I could eliminate the "Value": NNNNNN via replace(), that would work. However, this is yet another challenge because as far as I know, there are no wildcards that can be used.

If I could just do this:

replace(SourceText,'"value":'&*&',','')

I dont think this is possible in expression builder.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Re: I need help transposing data into a usable array.

Hi!

I just implemented this test example some week ago for another topic on this same forum:

https://powerusers.microsoft.com/t5/Building-Flows/Changing-an-array/td-p/527159

The challenge was make this array:

[ { “FieldName”: “FirstName”, “FieldValue”: “Fred” }, { “FieldName”: “LastName”, “FieldValue”: “Flintstone” } ]

into this array:

[ { “FirstName”: ”Fred”, “LastName”: ”Flintstone” } ]

...so looks quite similar to what your looking for, right?

ColumnId-DisplayValue in your case; FieldNAme-FieldValue in the example

Hope this helps

Was this reply helpful? Yes No
Verified answer

v-litu-msft on at

Like (3)

Report
Re: I need help transposing data into a usable array.

Hi @martinav,

I initialize an array variable to store the processed JSON, I store the data you provided in a Compose action, then put it into Apply to each action to loop through each member of the array.

Then I structure this JSON schema to parse it:

{ "type": "object", "properties": { "id": { "type": "integer" }, "rowNumber": { "type": "integer" }, "siblingId": { "type": "integer" }, "expanded": { "type": "boolean" }, "createdAt": { "type": "string" }, "modifiedAt": { "type": "string" }, "cells": { "type": "array", "items": { "type": "object", "properties": { "columnId": { "type": "integer" }, "value": { "type": [ "integer", "string" ] }, "displayValue": { "type": "string" } }, "required": [ "columnId" ] } } } }

Then append it into the array variable as below:

body('Parse_JSON')?['cells'][0]['value']

……

body('Parse_JSON')?['cells'][4]['value']

Result:

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
martinav 3,340 on at

Like (0)

Report
Re: I need help transposing data into a usable array.

@v-litu-msft ,

We're getting really close here. It looks like you captured exactly what I was after. However, I'm not sure what I did differently, I'm getting an error on my append array.

Here is one record from the parse json:

{ "id": 3070267953047428, "rowNumber": 1, "expanded": true, "createdAt": "2017-01-10T14:54:30Z", "modifiedAt": "2019-12-09T17:12:52Z", "cells": [ { "columnId": 3898437847541636, "value": 201013, "displayValue": "201013" }, { "columnId": 8402037474912132, "value": "JS75S PRV Dwg. 72784-2-1", "displayValue": "JS75S PRV Dwg. 72784-2-1" }, { "columnId": 1083688080435076 }, { "columnId": 3796166086289284, "value": "2017-01-10T14:54:30Z" }, { "columnId": 8299765713659780, "value": "jsmith@company.com", "displayValue": "jsmith@company.com" } ] }

The issue is with blank values. You did not add body('Parse_JSON')?['cells'][2]['value'] for 1083688080435076. This has to take into account blank values. I will look into an if() statement.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
Re: I need help transposing data into a usable array.

@v-litu-msft ,

Well, it looks like I need a way to trap the error. empty() does not suffice. I have to be able to accomodate the missing variable. I cannot control that at the source.

This was my plan:

if(empty(body('Parse_JSON')?['cells'][2]['value']),'',body('Parse_JSON')?['cells'][2]['value'])

This was my result:

Is there error trapping??? like iferr()

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Re: I need help transposing data into a usable array.

Hi!

Did you consider using coalesce() ?

if(equals(coalesce(body('Parse_JSON')?['cells'][2]['value'],''),''),'',body('Parse_JSON')?['cells'][2]['value'])

Hope this helps

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

Re: I need help transposing data into a usable array.

@efialttes ,

I sure didnt. Probably because I haven't a clue what it does.

Checking it...

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
Re: I need help transposing data into a usable array.

@efialttes ,

Well, it appears thats also a no-go. It appears it still has to be there to find a null value of it.

Statement:

coalesce(body('Parse_JSON')?['cells'][2]['value'],'')

Result:

Looks like I need another idea, or I need to do another creative replace() if its possible.

Was this reply helpful? Yes No