How to format JSON into excel through SharePoint file creation

(0) Share

Report

Posted on by MFEProjects

Hi,

I have followed the solution on the post below to get my collection into an excel spreadsheet:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Generating-a-SharePoint-file-from-collection-data/m-p/2073088#M518441

This solution above puts each column of data into it's own excel column. However, one of the columns in my collection is a record, with multiple entries. The data ends up getting formatted into the column like this:

[{"Class":"Piping","Client":"Company, Company2","Name":"ASME BPVC Sec. IX"},{"Class":"Piping","Client":"Company","Name":"AS3992"}]

Is it possible to make it so that when this is passed into the spreadsheet, it gets formatted with a simple comma between each entry?

Thanks

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MFEProjects ,

Could you show Excel's current output and your desired output?

Best Regards,

Wearsky
MFEProjects 76 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-xiaochen-msft
No worries,

This is the current output:
This is what I would like:
I want to remove the unnesaccary headers, such as 'Class' and 'Client' and only keep 'Name'. I would like to remove the braces and also the quote marks.

Something I also forgot in my original question... I'm doing all of this through Automate and the Create File component as you can see in the post I linked in my question. Is it possible to make the top row a header automatically when creating the file?

Had to make the tables images since for some reason the forum's table system wasn't working.

Thanks
Verified answer

v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MFEProjects ,

I made a sample for you.

'PowerAppV2->CreateCSVtable'.Run(JSON(DropColumns(AddColumns(Col,"temp",Concat(Test3,Name,",")),"Test3")))

Result:

Best Regards,

Wearsky
MFEProjects 76 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-xiaochen-msft

Thanks, that worked perfectly.