Power Automate

Unanswered

Convert Json data to Tabluar data in excel

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I'm getting a data from an API in JSON format.

after parse json step, i'm getting the below data in excel:

{"Time Period":"12/1/23 - 12/15/23"

Resource Name:"Rob Silva"

Timesheet Status:"Submitted"

Submitted by:"Rob Silva"

Last Modified:"12/12/23 7:12 AM"

Tasks:[{"Investment ID":"PRO41254"

Investment:"Market Risk Regulatory Compliance Housekeeping F24 [Input Data]"

Description:"C3-Development/Build"

Hours:{"12/1":9.0

12/2:9.0

12/3:0.0

12/4:9.0

12/5:9.0

12/6:9.0

12/7:9.0

12/8:9.0

12/9:9.0

12/10:0.0

12/11:9.0

12/12:9.0

12/13:9.0

12/14:9.0

12/15:9.0}

Total:99.0}]

Total Hours:{"Week 1":45.0

Week 2:45.0

Overall:90.0}}

But I'm getting special character like curly braces in output also and header and values are in same cell.

Expected format:

time period

resouce name

timesheet status

Submitted by

Last modified

Invesment

Description

1-Dec

2-Dec

3-Dec

4-Dec

5-Dec

6-Dec

7-Dec

8-Dec

9-Dec

10-Dec

11-Dec

12-Dec

13-Dec

14-Dec

15-Dec

Total

week1

week2

overall

12/12/23 - 12/15/23

Rob Silva

submitted

Rob Silva

12/12/2023

PRO111

Market Risk

AS3-Dev

Let me know how can i do this :

Categories:

General topics

All responses (7)

Answers (0)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @adi124 ,

Could you please show me the parse json action's input parameter.

Best Regards,

Bof
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft
Input Parameter :
outputs('json')['body']?['choices'][0]?['message']?['content'].

Note :I'm not able to select the columns in above as i get error message like -
Array elements can only be selected using an integer index.
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @adi124 ,

Could you please show me the value of

outputs('json')['body']?['choices'][0]?['message']?['content']

Best Regards,

Bof
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft ,
Output is :
{
"TimePeriod": "12/12/23 - 12/25/23",
"ResourceName": "Rob Silva",
"TimesheetStatus": "Submitted",
"SubmittedBy": "Rob Silva",
"LastModified": "12/12/23 7:12 AM",
"Tasks": [
{
"InvestmentID": "PRO41254",
"Investment": "Market Risk",
"Description": "AS3-Dev",
"Hours": {
"12/12": "9.00",
"12/13": "9.00",
"12/14": "9.00",
"12/15": "9.00",
"12/16": "9.00",
"12/17": "0.00",
"12/18": "0.00",
"12/19": "9.00",
"12/20": "9.00",
"12/21": "9.00",
"12/22": "9.00",
"12/23": "9.00",
"12/24": "0.00",
"12/25": "0.00"
},
"Total": "90.00"
}
],
"TotalHours": {
"Fri": "9.00",
"Sat": "0.00",
"Sun": "0.00",
"Mon": "9.00",
"Tue": "9.00",
"Wed": "9.00",
"Thu": "9.00",
"Total": "90.00"
}
}
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

I've made a test but did not encountered the problem you mentioned.

Result:

Maybe you could readd the parse JSON action and try again, if the problem cannot be solved , I suggest you create a support ticket for professional assistance following the below procedure.

Get Help + Support - Power Platform | Microsoft Docs

Best Regards,

Bof
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft ,

Thanks a lot , I was able to select the columns now.
But Investment ID, investment Description and Hours still cannot be selected.
I think this are under array and there might be some different way to do for these columns.
Thanks!
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @adi124 ,

Please try these expressions:

InvestmentID

body('Parse_JSON')?['Tasks'][0]['InvestmentID']

investment Description

body('Parse_JSON')?['Tasks'][0]['Description']

Hours

body('Parse_JSON')?['Tasks'][0]['Hours']

Best Regards,

Bof