Skip to content
April 2, 2014 / Erik Svensen

Create Featured Q & A questions inside your Excel model – #PowerBI

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

…..AppCatalog%2FPowerBI&k=Show%20number%20of%20windmills%20by%20municipality%20as%20map

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

=HYPERLINK(Siteprefix&C6;C6)

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.

Advertisements

2 Comments

Leave a Comment
  1. francois / Apr 4 2014 12:05 am

    interested by a copy of your model to test it

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: