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

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
Helper I
Helper 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. 

NizamFlow
Frequent Visitor

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

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

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.

View solution in original post

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (936)