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

Parse JSON with transformation (unpivot) with Power Automate

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Paulie78
Super User
Super User

You can do it like this:

jsonToArray.png

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 😘

View solution in original post

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

View solution in original post

7 REPLIES 7
SPOD
Advocate I
Advocate I

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. 

Hi SPID,

 

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

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
Super User
Super User

You can do it like this:

jsonToArray.png

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 😘

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

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
Frequent Visitor

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

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,548)