IMPORTING EXCEL POWER VIEW DASHBOARDS INTO POWER BI

If your organization is now a Power BI customer, congratulations. You’re now ready to create some very cool dashboards, integrate disparate and disconnected data sources and take advantage of Power BI’s ability to modify and transform your data, build interactive and dynamic dashboards and then share them with your team and organization. But until you create your dashboards to take advantage of the new visualization types and other improvements, you can easily import any existing Power View sheets in Excel into your Power BI site.

Above you’ll see an example of a Power View dashboard that I will import into my Power BI site.

Importing Power View into Power BI

To import Power View sheets into Power BI, navigate to your team’s Power BI site and click the Get Data button at the bottom left.

Then select where your data exists. In my case, I have my Power View reports in Excel saved in my One Drive folder so I’ll select Files.

You’ll have to log into your One Drive account. Once you’ve done that, just navigate to where your Excel file is and then click Connect.

After the import is complete, you’ll see your new Report and Dataset on the left in your My Workspace explorer. Open the new Report based on your Power View sheet. Now I can browse and interact with my Power View dashboard within my Power BI site.

I can also pin visualization from my Power View report to a Power BI dashboard with other visualizations.

And now that we have our Power View report imported and pinned to a dashboard can use Q&A to quickly generate visualizations using the imported data set.

Pretty cool stuff! It’s nice to know that I can easily jump from my legacy Power View dashboards to the new Power BI.

It’s important to not that in order to import Excel workbooks into your Power BI site, you’ll need to make sure that you have the latest version of Power Query installed before uploading.

Resources

For more information on importing data, take a look at the Power BI support page. There’s lots of good information there:
https://support.powerbi.com/knowledgebase/topics/63369-get-data

Power BI Tip: Use a Scatter Chart to Create a Calendar Report

The Scatter Chart in Power BI and Excel is very useful chart for visualizing three different metrics in tandem. But with a little bit of work you can use a Scatter Chart to create a Calendar chart for visualizing your metrics across the days of an individual month.

To configure a Scatter Chart too mimic a Calendar type report, you need the following:

1) An attribute for the day number of the week (1,2,3,4,5,6,7).
2) An attribute for the day number of the month (1,2,3…29,30,31).
2) An attribute for the week number of the month (1,2,3,4,5,6).
3) An attribute for sorting the week numbers in reverse order.
4) A business metric you wish to represent in the report.

Most of these items you can get from a traditional date dimension. In this example, I’m utilizing the Adventure Works DW database which has a date dimension table.

To set up the Scatter chart correctly, configure the visualization as seen in this screen shot from Power BI Desktop.

I want to point out a couple things here. First, you can optionally add a field to the Legend to differentiate between the weekend and weekdays or to identify holidays, as seen below.

Secondly, in order to display the Calendar in the correct order, we actually need to reverse the order to the Week numbers so that the first week of the month is numerically higher than the last week of the month. To do this I used a TSQL Case statement to populate the new column in the Adventure Works Date dimension table:

[WeekNumberOfMonth]  AS 

    ((datepart(week,[FullDateAlternateKey])

    -datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1)),

[WeekNumberOfMonthReverse]  AS 

    (case (datepart(week,[FullDateAlternateKey])-datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1) 

        when (1) then (6) 

        when (2) then (5) 

        when (3) then (4) 

        when (4) then (3) 

        when (5) then (2) 

        when (6) then (1)  

    end)

In the above code snippet, I also included the TSQL I used to create the WeekNumberOfMonth column.

You may also notice that I included the days of the week names at the bottom of the chart. I kind of cheated here as the Scatter chart doesn’t allow string fields on the X axis. I simply used a text box to rig this up but when the charts resize it shifts out of position, so be aware of this.

The final step is to hide all the axes and the background if you desire. In my case I left the data labels visible to display the day of the month number.

Resources

Interested in other Power BI tricks? Check these out:

Import Excel Power View Reports into Power BI
Clean Your Power Query Code
Create Cool Slicers with Treemaps, Funnels, Column & Bar Charts