Parse JSON with transformation (unpivot) with Power Automate

[NizamFlow]

Hello Experts,

I am looking for a data transformation to be done with Power Automate, the connector or expression to convert a JSON record into multiple rows (unpivot columns). It would be great if someone could assist here.

Current JSON Body :

[
{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"07-06-2021": "40",
"14-06-2021": "50",
"21-06-2021": "60",
}
]

Below is the expectation which converts into multiple record based on Forecast Week.

[
{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"Forecast Week": "07-06-2021",
"Forecast Hours": "40"
},

{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"Forecast Week": "14-06-2021",
"Forecast Hours": "50"
},
{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"Forecast Week": "21-06-2021",
"Forecast Hours": "60"
}
]

Thanks & Regards,

Nizam

[SPOD]

Hi @NizamFlow , have you looked at power query? It allows you to write a query to open json as tabulated data in columns. There is also an unpivot function in PQ that can transform the shape of the data to get it into a pivot table. 

[NizamFlow]

Hi SPID,

Thanks for the reply! I wasn't aware that we can use power query inside power automate. Could you name the connector?

[SPOD]

I was thinking to transform the json into the correct shape in PQ and bring it to a table. From there you could use the excel connector to bring it into power automate. I use the connector Excel, “list rows present in a table” and then apply to each to loop through the rows. 

[Paulie78 · Accepted solution]

You can do it like this:

The expressions used:

Action: arrayToObject...
first(outputs('JSON'))

Action: Dates...
removeProperty(removeProperty(outputs('arrayToObject'), 'GUID_RoleID'), 'Department Code')

Select Action...
From: split(replace(replace(replace(string(outputs('Dates')), '{', ''), '}', ''), '"', ''), ',')
First Column: outputs('arrayToObject')['GUID_RoleID']
Second Column: outputs('arrayToObject')['Department Code']
Third Column: split(item(), ':')?[0]
Fourth Column: split(item(), ':')?[1]

Produces this output:

[
  {
    "GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
    "Department Code": "COMDP",
    "Forecast Week": "07-06-2021",
    "Forecast Hours": "40"
  },
  {
    "GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
    "Department Code": "COMDP",
    "Forecast Week": "14-06-2021",
    "Forecast Hours": "50"
  },
  {
    "GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
    "Department Code": "COMDP",
    "Forecast Week": "21-06-2021",
    "Forecast Hours": "60"
  }
]

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[NizamFlow]

Hi @Paulie78 ,

Thank you so much for your time!

I followed the step you suggested and it works perfect!!

Additionally, is there any control in flow to convert multiple JSON records? Please find below the example,

[
{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"07-06-2021": "40",
"14-06-2021": "40",
"21-06-2021": "40",
"28-06-2021": "40"

},
{

"GUID_RoleID": "b188b0fc-421a-4096-a8ee-eeb912136f05",
"Department Code": "COMDP",
"07-06-2021": "20",
"14-06-2021": "20",
"21-06-2021": "20",
"28-06-2021": "20",
"05-07-2021": "20",
"12-07-2021": "20",
"19-07-2021": "20",
"26-07-2021": "20"
}
]

[
{
"GUID_RoleID": "30f90e50-6bc0-4181-b467-6eef5342a101",
"Department Code": "COMDP",
"07-06-2021": "40",
"14-06-2021": "40",
"21-06-2021": "40",
"28-06-2021": "40"

},
{

"GUID_RoleID": "b188b0fc-421a-4096-a8ee-eeb912136f05",
"Department Code": "COMDP",
"07-06-2021": "20",
"14-06-2021": "20",
"21-06-2021": "20",
"28-06-2021": "20",
"05-07-2021": "20",
"12-07-2021": "20",
"19-07-2021": "20",
"26-07-2021": "20"
}
]

Regards,

Nizam

[Paulie78 · Accepted solution]

Not easily, because that isn't valid JSON, but if it was a valid array or arrays, you could simply use an apply/each.

[NizamFlow]

Hi @Paulie78 ,

I tried using the apply to each condition for the JSON array. Which always returing first record from the Json.

What would be the expression to be used instead of first(outputs('JSON'))?

Thanks & Regards,

Nizam