Key Value Pair Converted to an Array

[rebeccarlisle]

Hi there, I am desperately trying to convert fields (originally from a JSON) from a key value pair to an array. The below table illustrates 3 different events (Record 0, 1 & 2 with associated Event IDs). Each event has associated entities which are impacted by that event, seen in red. The entity ID is currently found in the 'Field Name' field while a '1' represents 'true' and a '0' represents 'false'. I would like the entityID numeral value to show up in the 'Value' field and the 'Field Name' to contain 'entityID' and a count of the number of entities within that record (same as location field). I am able to switch the fields around but battling to get a count on the entityID. Please help if you are able to!

Record	Current Field Name	Current Value	Preferred Field Name	Preferred Value
0	EventId	665	EventId	665
0	creationDate	2019-04-09	creationDate	2019-04-09
0	locations.0	UNITED STATES	locations.0	UNITED STATES
0	locations.1	CHINA	locations.1	CHINA
0	locations.2	UNITED KINGDOM	locations.2	UNITED KINGDOM
0	status	Being processed	status	Being processed
0	596	1	entityID.0	596
0	597	1	entityID.1	597
0	598	1	entityID.2	598
1	EventId	684	EventId	684
1	creationDate	2019-04-10	creationDate	2019-04-10
1	locations.0	AUSTRALIA	locations.0	AUSTRALIA
1	locations.1	CHINA	locations.1	CHINA
1	status	New	status	New
1	663	1	entityID.0	663
1	667	1	entityID.1	667
1	668	1	entityID.2	668
2	EventId	688	EventId	688
2	creationDate	2019-04-10	creationDate	2019-04-10
2	locations.0	FRANCE	locations.0	FRANCE
2	locations.1	UNITED STATES	locations.1	UNITED STATES
2	locations.2	UNITED KINGDOM	locations.2	UNITED KINGDOM
2	status	New	status	New
2	698	1	entityID.0	698
2	699	1	entityID.1	699

[LordNeilLord · Accepted solution]

Hey @rebeccarlisle 

mutlirow formula is your friend here, you can get it incrementally increase the number depending on your desired criteria :) Here's an example: