Transforming data from API in Power BI

Mark Hibyan
Mark Hibyan ✭✭
edited 02/17/22 in API & Developers

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