Using PowerQuery as a self-help interface to database applications

[Jens Theisen]

I'm a database application developer and I'm looking for ways to give my customers more power over how to extract and use the data from the applications I write.

Since Excel is the tool everybody knows, it would be great if they could just connect, probably with PowerQuery, possibly through OData, with the app and do the rest themselves. Sadly, there are some stumbling blocks that spoil the fun:

The data sets are often a) large and b) sensitive enough to require retrieving only a subset. That means it is mandatory to filter the data before loading it. I found two ways to do this with PowerQuery:

1. Power Query has a parameter concept and I can indeed create an ODATA request depending on a parameter, but...
   1. ...it's much more cryptic to build such an ODATA query than doing filtering with the PowerQuery UI. It's probably too demanding for my customers.
   2. ...when I set the parameter to be restricted to a list based on a query, the query using the parameter depends on the restriction query, leading to a variant of this issue.
2. PowerQuery can access the Excel sheet and get parameters from there. That too, is pretty icky as it requires my customers dealing with the M language in its textual form.

I could offer any kind of interface, ODATA or else, but I want

• the whole user experience to be as nice as the ODATA ETL UI experience already is and
• data loaded in the Excel Sheet to be restricted so that it loads fast and contains no inappropriate information.

Given that PowerQuery already makes so much really easy, it drives me mad that there are these last issues I can't get around.

What can I do?

 

[Ken Puls]

PowerQuery can access the Excel sheet and get parameters from there. That too, is pretty icky as it requires my customers dealing with the M language in its textual form.

Lost me here... if you're pulling queries from the worksheet, why do they need to mess around with M? You can code things up and just send them an Excel workbook that already has this part of the work flow done?

The one kicker you may have to deal with is getting them to turn off data privacy if you can't work around the firewall issues you mentioned...

 

[Jens Theisen]

Lost me here... if you're pulling queries from the worksheet, why do they need to mess around with M? You can code things up and just send them an Excel workbook that already has this part of the work flow done?

Possible, but I was really hoping for a neater solution - given that the ETL part is already so neat.

What bothers me in particular with this approach is:

• If I'm making a template with the parameters in some worksheet, I have to dictate a part of the Excel book itself. Since many books are, in the end, supposed to be sent to other people, potentially in other companies, I would rather have my customers have full control over the contents of their books - but without having to mess with M textually.
• Part of what I really liked about the unparameterized ODATA case is that the user just enters a URL and then gets to choose which table/entity they want to access. It's a great experience that people can easily remember. As soon as they need pre-prepared Excel templates or M code snippets, the user experience degrades substantially from that.

 

[Ken Puls]

I'm still not following why anyone needs to get into textual editing of the M code. I've setup up many queries so that the user can change a value in an Excel worksheet and hit refresh and it just works. They don't even know that there is such a thing as M code at all, and should never need to. Even if they are going into Power Query, if the base query is set up so that they can update an Excel cell, then jump into the query editor and tack more steps on to your existing basic query, I would think that would be just as easy as teaching them how to create a new ODATA query from scratch.

Ultimately though, the issue you're dealing with is that the data sets you are presenting sound like they contain too much. There is NOTHING that we can do to resolve that at the end of the day. If your data is open, someone will be able to access it and see records that they shouldn't. The true fix for this is to get some kind of setup that requires a user to log in in order to view data that they are entitled to see.

 

[Jens Theisen]

Ultimately though, the issue you're dealing with is that the data sets you are presenting sound like they contain too much. There is NOTHING that we can do to resolve that at the end of the day. If your data is open, someone will be able to access it and see records that they shouldn't. The true fix for this is to get some kind of setup that requires a user to log in in order to view data that they are entitled to see.

This is a misunderstanding. My concern is just that if my customer retrieves all data for all his clients at once and filters on one specific client C1 to then send the Excel book out to C1, that client will receive a book that contains potentially sensitive data about a different client C2 - as all the data Power Query has downloaded is stored in the Excel book somewhere, even if not all of that data is used in the sheets, correct?

That's the most important reason I think at least some filtering has to be done on the server side.

I'm still not following why anyone needs to get into textual editing of the M code. I've setup up many queries so that the user can change a value in an Excel worksheet and hit refresh and it just works. They don't even know that there is such a thing as M code at all, and should never need to. Even if they are going into Power Query, if the base query is set up so that they can update an Excel cell, then jump into the query editor and tack more steps on to your existing basic query, I would think that would be just as easy as teaching them how to create a new ODATA query from scratch.

Okay, I'll probably go that route then. Just to clarify:

- The only feasible way for having parameters is to put them in a sheet cell, correct?
- If I need to create Excel templates with the base query set up, can I create those programmatically somehow? You know of a library that does that? Can I do that with COM automation?

Thank you for your assistance!