Transforming data from API in Power BI
Hello Smart People!
I need to report on some data that I have compiled from many sheets in a Smartsheet report. The reporting tool that we have is Power BI, but the connector to Smartsheet defaults to IE, which isn't supported by Smartsheet. Apparently, this is a bug, and the work around is to add smartsheet.com to the "compatibility view settings", but this is locked down by our internal IT, so I can't do that. So now I'm pulling the data into Power BI via the API, which I am able to do, and pull just the report I'm interested in - great - all of the data is there - but it came over as json, and is not organized the way I need it to be to do my drag and drop grouping and reporting. All of my columns / cell data are listed as rows and repeated.
Is there an easy way to transform this data back into a neat table view without having to code it back using one of the SDKs? I'm trying to transform and pivot the data using Power BI functionality to no avail.
Sorry, I'm new to this! Thank you for any assistance!
Best Answer
-
Hi @Mark Hibyan
I believe you may be able to change the
Accept
header value to accept only CSV/Excel format. Here's the documentation on it: https://smartsheet.redoc.ly/#operation/getReportsCheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Answers
-
Hi @Mark Hibyan
I believe you may be able to change the
Accept
header value to accept only CSV/Excel format. Here's the documentation on it: https://smartsheet.redoc.ly/#operation/getReportsCheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Thank you @Genevieve P. , I actually got the Smarsheet connector to work, but I tried this using the web / API to confirm in case others run into a similar issue, and this works, just add another header for "Accept" with "text/csv" defined, and it will pull the data in the proper column / cell format.
-
Thanks for following-up and confirming, Mark! 🙂
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.3K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 477 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 150 Just for fun
- 72 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives