Configurable Data Model with SQL and parameters from the spreadsheet

[LGXSteve]

I have a PowerPivot Spreadsheet which accesses a large amount of data from a MySQL database, and works well. I would like to expand this somewhat now, but so as to keep the amount of data manageable, and allow some fine tuning, I would like to selectively restrict this to some customers. I can do this by adding to the WHERE clause I use to build the Data Model.

My spreadsheet has a Table Of Contents page, which has links to all the other sheets, and a button to update the Data Model. I would like to extend this, so it includes a Combo to allow me to select the customer from a list, then use this selected customer in my SQL query.

So, my question is what is the best way to make my SQL Query used for creating the Data Model reference a Cell elsewhere in the Spreadsheet?

(I am sure this has been asked before, but my various searches have been unuccesful).

Thanks in anticipation

 

[Kazlik]

One option is to use Power Query to pass parameters from Excel to your SQL query. In your M code in Power Query you reference the table and cell a user can input into to, in the example below FullTextInputs. Then pass this into your SQL code also in M.

let
Team_parameter = Excel.CurrentWorkbook(){[Name="FullTextInputs"]}[Content],
SelectedCategoryID = Team_parameter[CategoryID]{0},
Source = Sql.Database……..where #(lf)cts.team_id = " & Text.From(SelectedCategoryID) & "#(lf)

In
Source

 

[LGXSteve]

Thanks for your reply, which I understand in part. I have read about PowerQuery, but only ever used PowerPivot. As for combining the two, as I think you are suggesting here, that is something different.

If my understanding is correct, are you saying here that I can assemble my SQL Query in Power Query and that will populate the Data Model? PowerPivot will of course use the same Data Model, so away I go.

I have taken a look at PowerQuery and with some dabbling come up with this;

let
ClientIdField = Excel.CurrentWorkbook(){[Name="ClientIdCell"]}[Content],
ClientId = ClientIdField{0}[Column1],
Source = Odbc.DataSource("Provider=MSDASQL.1;Persist Security Info=False;DSN=MyDB"),
mydb_tickets = Source{[Item="mydb_tickets",Schema="",Catalog="mydb"]}[Data],
FilteredTable = Table.SelectRows(mydb_tickets, each ([department] = Text.From("ClientId")))
in
FilteredTable


This is not quite what you said, but I derived it by filtering my PowerQuery by department, then editted the query.

This though does not work, I get an empty table even though the value in the referenced cell is valid. If I change Text.From("ClientId") to a literal number, so this must be close to right. Once I crack this though, how do I get the resultant data into my PowerPivot Data Model?

Thanks again in anticipation

(NOTE - I have changed the DB name by hand, so I may have added some inconsistencies, sorry for any confusion that may cause).