Last Saturday I held a session at SQLSaturday 275 about how to prepare your Excel power pivot model for the Power BI Q & A functionality.
In my preparation to the session I created featured questions to illustrate that you can help the user to ask questions.
So when they for instance click on “Show count of fabrikat by county” they get this nice overview of the windmills in Denmark.
Then I noticed that the last part of the URL was actually the question asked
So I thought why not put the Featured questions of the model inside the Excel file using hyperlinks.
How to do it
So in my model I created this overview
I created the models like this
The Featured question boxes consists of a small row and 4 cells merged. In the small row I type the question in text
In this case the question “Show number of windmill by county as map” in cell C6 – and format the cell with the custom format “;;;” to hide the text
In Cell C7 I create the formula
Where the name Siteprefix contains the full URL except the part with the question. It not necessary to add the %20 for the spaces in the URL.
This now give the user a big cell to click – to go to the report in Power Bi Q & A.
Now we can remove the gridlines and headers and get a nice clean overview, and save the model to our Power Bi site.
And the questions can be activated from Excel Online.
These URL’s can be called from any application so we can send a question to the Q & A site from everywhere.
Let me know what you think…. Or If you want a copy of the model.