0

I need to give power-users of the web application I am working on the ability to create their own reports, analysis, etc. in Excel. Basically they need to create some Excel sheets, read some data from the web app and than mix the result with further data coming from other data sources.

The web application already exposes data in JSON/XML format through a web api (not fully REST but this doens't matter) and I would use this channel to get data in Excel.

At the moment I have these three options in my mind:

  • In the past I've solved a request like this with some VBA code and a COM object (that talked with the server/database) but I am not sure if today this is still the best solution to do this kind of job.
  • I have learned that today Excel has PowerPivot that can read data from a web service. I could develop an oData feed for PowerPivot but I am not sure if PowerPivot is what power-users need
  • Another solution could bean ad-hoc Excel add-in

How would you solve a request like this?

Improve this question

2 Answers 2

Reset to default
1

Power Query would be a better option than Power Pivot here. Power Pivot is a dimensional modeling and analytical database (it is exactly a private instance of SSAS Tabular running behind the Excel process).

Power Query is an end-user friendly ETL tool, developed as an add-in for Excel, and available natively as of Office 2016. It allows loading directly to an Excel worksheet or into a Power Pivot model. It will give more flexibility to your end users. It is a GUI-driven interface that is a front-end to the M query language, developed by Microsoft.

Unfortunately, I am not able to help with Power Query too much, but it fits your use case perfectly.

Edit: An additional feature of Power Query, likely not to meet your needs, but I thought I'd throw in.

Power Query can read directly from HTML tables. If you present data in HTML tables, your end users can simply load directly from a URL.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Power Query definitely the "correct" tool for this within Microsoft world. It can also handle JSON and XML (and Odata) directly. How well it manages your data will depend a bit on how nicely formatted it is, but it can work with most things with a little bit of effort.

It is a free Microsoft authored add-in for pre Excel 2016 and built in to Excel 2016.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.