Transforming data from API in Power BI

Mark Hibyan · 02/17/22

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!

Genevieve P. · 02/25/22

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/getReports

Cheers,

Genevieve

Genevieve P. · 02/25/22

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/getReports

Cheers,

Genevieve

Mark Hibyan · 03/02/22

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.

Genevieve P. · 03/03/22

Thanks for following-up and confirming, Mark! 🙂

Transforming data from API in Power BI

Best Answer

Answers

Categories