How to create surveys in OneDrive and view the results in Power BI

So, this blog post is the result of the pestering request of one Jen Underwood, who I mentioned this to previously as something I’d done for some internal work at Microsoft.  Apparently, a number of people aren’t aware there is an easy way to create surveys using OneDrive and Excel Online.  It’s a great way to quickly create an anonymous survey that you can share a public link to.  And because the results are immediately saved in an Excel document in OneDrive, you can use PowerBI to view those results as they come in!  Here’s how you do it –

1. Sign up for OneDrive (duh).

2. Once you’ve signed up/in, go to the new menu.  Here you’ll see the option to create a number of new documents, including an Excel survey.  Choose that.

image

3. A new browser window will open and you can create your survey by giving it a title, description, and begin entering your questions by clicking on the little gear that appears as click in the area to enter.

image

4.  The questions can be multiple choice, true/false, text responses, etc.  You can also make them required or mark if they have a default answer.
image

Once you’ve finished, your question will appear in the list you’ve added to your survey in the order you create each one.

image

5. Continue adding questions until you are finished.  You can move the order they appear in around at any time by simply hovering over a question, clicking it, and then dragging it to the place you wish it listed.

image

6. Once you’ve finished, if you hit “Save and View”, you can preview what your survey will look like for those using it.

image

If everything looks good, you can hit the “Share Survey” button, and a link will be generated that you can share so users can fill out the survey.
image
There, my survey is done.  I invite you to fill it out here – http://1drv.ms/1SXqFt5

Now that my survey is finished, I’ll want to report on the results using Power BI.  I can do that right from the Power BI site.

1. Go to PowerBI.com and login to your Power BI site (or sign up if you haven’t already).
image

2. Once you’ve done that, go to Get Data, and then choose Files
image
3. Choose OneDrive-Personal and select the survey you just created
image

And that’s it – you can now create your report to show the results as they come in from the survey.  Simply design it, name it and save it.

image

And now with the new Power BI public embedding, everyone can see the results that come into my report.  So fill out the survey here – http://1drv.ms/1SXqFt5

And view the results here – Survey Results

Thanks for reading, and I can finally tell Jen to quit bugging me to write this post.  Smile

Combine Excel Online and Load on Demand to power up your Excel data source in Datazen

image

I hadn’t planned on a post today, but after watching the Philadelphia Eagles get their first win of the season, I was inspired to do something I’d first thought about a couple weeks back.  Specifically, I wanted to use an Excel document as a data source in my Datazen dashboard, but enable Load on Demand capabilities so I could take advantage of parameters.  I also wanted to see how that would work in combination with people just typing in data into the Excel sheets that are the data source.  How quickly would I see their changes in my Datazen dashboard?

Out of the box, Datazen allows you to use Excel files in a shared folder as a real time data source.  And in the post I did around combining Datazen dashboards with Power BI Q&A functionality, I first talked using the sync capabilities of OneDrive to sync an Excel file from my local drive to the Datazen server.  The thought was, that way if I refresh a data source locally, I can have it sync up to the server.  The issue I saw with using this as the solution for this scenario were two-fold –

1. I was the only person updating information in this scenario.  Ideally multiple people could make updates using Excel Online at the same time.

2. I couldn’t do parameters with the out of the box Excel data source in Datazen.

Well, what if I used the workaround I mentioned about how you could use Excel files as a data source for KPI’s?  Since that had you using Excel like SQL, maybe then I could use parameters.  Let’s give this a shot.

In my Datazen server, I’m going to create a new data source called Excel as SQL, using the instructions from the blog post I referenced –
image  I’m going to create an Excel file with two tabs – one has Category, Amount and Target along with some sample data.
image
The other just has Category and a single sample value.
image
I then save my file.  It’s now available to me either on the server or in a web browser via Excel Online, thanks to OneDrive.
image

On the Datazen server, I’m going to create two SQL queries to use.  The first one, called Data, looks like this –
image

You see I’ve setup a parameter called @Product to use – here’s what that looks like
image

I used a default value to make sure I get back at least one value, otherwise the Datazen Publisher won’t be able to build the data view properly.  Next, I’ll build my other query called FilterValues.  It’s pretty basic –
image
Now I can build my dashboard.  I’ll bring in two elements – a Selection list and a Number with Delta.
imageThen bring in both of the data feeds and hookup the data to the dashboard elements.
image
I’ll setup my parameter to be equal to what’s passed into my dropdown list.
image
Now I publish my dashboard.  When I hit it from a web browser or app, I get back what I’d expect to.
image
This is fine, but I’ll want to add more items on an ongoing basis.  If I try to open the document and make the changes directly on my desktop, the data source is locked and not available to Datazen –
image
But, if I instead make the changes in Excel Online, the changes get synced and my data source isn’t locked!  This is true even if I have multiple people adding items at the same time.  I’m going to update my Excel Online document by adding a number of new categories (as suggested by my children) –
image
How quickly these show up can vary – I’ve had it in as little as 5 seconds, and sometimes it’ll take up to a minute.  For example, these showed up by the time I finished typing the previous sentence.
image
And I’ll now update my data in the other sheet as well.
image
What’s cool here is I don’t need to refresh the page to see this new data, since it’s always going to back to the original data source thanks to Load On Demand.  Once my data shows up, my values are automatically updated when I change the dropdown.
image And notice I added the Seahawks data – I’ll never see that as a value if unless I add it to the dropdown as well.

This offers some great flexibility for folks to manage data and make updates this way.  You could also do multi-select values vs. a single value by changing your SQL statement and allowing multi-select in your dropdown list.

Thanks for reading everyone!

Combine Datazen dashboards with Power BI Q&A functionality

Happy Friday, everyone!

Did you know that Power BI now allows you to directly link to individual dashboards?  This got me thinking – I love the Q&A functionality in Power BI.  Could I easily put together a solution where I used Datazen for my dashboards and jumped into the Power BI service to ask additional questions?  Why yes, yes I can.  Here’s how –

For this example, we’ll use a simple Excel workbook as the data source.  But let’s leverage the capabilities of OneDrive for Business to make this solution a bit more elegant.

image

Why?  Couple reasons –

1. Power BI will automatically refresh datasets sitting in OneDrive every hour, so anytime you update your Excel data, it’ll get grabbed automatically by Power BI shortly thereafter.

2. You can leverage the OneDrive for Business sync client to save Excel files locally on your PC and have them automatically sync to other machines running the sync client.  This makes it easy to get my Excel files onto my Datazen server after I’ve installed my sync client on there – I can refresh my files locally, save them to my OneDrive folder on my PC, and they’ll show up on the Datazen server within minutes!  I could even use a tool like Power Update to automate the data refresh of my Excel files.

Let’s get started –

First, I’m going to open Datazen and draw my dashboard first so I know how I should lay out the data in my Excel workbook.

Screenshot (34)
With that done, I’m going to create my workbook from my live data source using Power Query to make sure it’s laid out the way I need.
image
Now, I’m going to save the file to OneDrive for Business.  Once that’s done, I’m going to my PowerBI.com site and creating a new dashboard called “StoreQA”.
image
Now, I’m going to add my data to the dashboard.  So I click “Get Data”
image
Then I select “Files”
image
Now I want to select the “OneDrive for Business” tile
image
And then find the file I just saved

image
and hit “Connect”.  It’ll then bring my data into the dashboard and I can leverage Q&A with it.
image
I’m going to leave this open for now, and jump back to my Datazen Publisher App to bring in the Excel data to my Datazen dashboard.  You’ll need to setup the OneDrive for Business folder on your Datazen server as the location for the Excel data (this assumes you setup the sync agent on the box already).

image

With that done, you can bring in the Excel data to your Datazen dashboard
Screenshot (35)
 Screenshot (36)
and hook back in the elements accordingly.

Screenshot (39)

Once that is done, you’ll want to create a link from the Datazen dashboard to the Q&A in Power BI for this dataset.  Go back to your browser and grab the URL for the dashboard from the address bar.  It’ll look like the example here – https://app.powerbi.com/dashboards/xxxx

To go directly to the Q&A functionality for this dashboard, just add ‘/qna’ at the end of the url, so it then looks like this – https://app.powerbi.com/dashboards/xxxx/qna
You can test it out and see how it takes you right to the question and answer area for this dashboard –
image
Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from
image
by using the Drill-Through Target functionality to point to a custom URL
Screenshot (37)
Now when I click on the element, I get right to the Q&A page in Power BI with the same dataset so I can ask it more questions about my data
image
If you really wanted to get clever, you could even use the parameter functionality.  See how when I ask a question in Power BI, the URL changes to include the question text –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20grocery%20by%20year

and gives me the following result

image

I can simply change it to use a parameter instead, so it would look like this –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20{{ SelectionList01.SelectedItem }}%20by%20year

and when I run my dashboard, I can change the dropdown
Screenshot (38)

and the question will dynamically change when I click on my element with the link to Power BI!

image
Remember, you have to publish the dashboard to your server before you can test the URL drillthrough in Datazen!

And with Power BI now having the ability to setup custom links with it’s elements, you could even pin the tile for the question you just asked so you could jump back to your Datazen dashboard!

image 
Hopefully this helped open up a number of possibilities with the tools for you to explore.  Have a great weekend, and don’t forget to download the Windows 7 publisher app currently in preview from here and give it a spin.

Thanks for reading!