Press "Enter" to skip to content

Category: Power BI

Web Scraping With Power BI

Published 2018-02-14 by Kevin Feasel

Imke Feldmann shows how to use Power BI to scrape multiple tables from a webpage:

I will present 2 methods here:

  1. Append-method: This is the obvious one and is fast for just a few tables.
  2. Add-Column-method: A bit more complicated but will be faster for a large number of tables and is also suitable for a dynamic number of tables.

You will also find 2 options at the end of this article:

  1. Use custom functions for multi-step table transformations

  2. Use dynamic filters to select the desired tables

Read the whole thing.

Comments closed

Using Power BI To Pass Parameterized Values To R

Published 2018-02-12 by Kevin Feasel

Stacia Varga shows how you can parameterize your R scripts within Power BI:

It’s not difficult, but the cool thing about Power BI is that I can use parameters to dynamically change the report visualization without opening up the script. To do this:

  • Open the Query Editor in Power BI

  • Click Manage Parameters, and then click New Parameter.

  • Set the parameter properties – Name, Type, and Current Value.The Name is how I will reference the parameter my R script, the Type is the data type, and Current Value is the initial value that I want to set (if any).

Click through for an example and more details.

Comments closed

Options For Deploying Power BI Reports

Published 2018-02-09 by Kevin Feasel

Eugene Meidinger covers the various deployment options for Power BI:

Even worse, Power BI is rapidly being iterated on. This is great for users, but a challenge for people trying to keep up with the technology. One year ago the following deployment options modes didn’t exist.

  1. Sharing individual reports (Jan 2018)
  2. “Apps” (May 2017)
  3. SharePoint Embedding (Feb 2017)
  4. Power BI Premium (May 2017)
  5. Power BI Report Server (June 2017)
  6. Power BI Embedded V2 (May 2017)

It can be a real challenge to keep up. I think that a lot of the dust has settled when it comes to deployment options. I don’t see them adding a lot of new methods. But I expect there to be many small tweaks as time goes on. In fact I had to make two changes to my slides this morning because they announced changes yesterday!

In contrast, I expect another six to be added to this list in the coming three months.  Because it’s Power BI and the only rule behind Power BI is that there must be more.

Comments closed

Displaying Items Not Selected In A Power BI Slicer

Published 2018-02-07 by Kevin Feasel

Matt Allington tries to solve the converse of an easy problem:

My idea was that I would load school photos and also the reunion photos onto the one page.  The user can then click on a slicer with someone’s name (or any other information about people) and “see” those people highlighted in the photo.  I started thinking that I could use the excellent Synoptic Panel from The Italians for this.  The only problem I could foresee was that Synoptic Panel is designed to provide shading over an image based on what was selected.  I wanted to shade/hide those people that were NOT selected.  Anyhow, I love a challenge.

Read on for Matt’s solution.

Comments closed

Making Power BI Reports Screen Reader Accessible

Published 2018-02-07 by Kevin Feasel

Meagan Longoria has a couple of posts on designing Power BI reports to make them accessible to people who use screen readers.  First up is a list of good tips:

  • Avoid auto-playing video or audio as that conflicts with the screen reader. If you must use video or audio, provide it in a way that requires the user to start it rather than stop it.

  • Be sure to format numbers appropriately so screen readers don’t read out a long series of insignificant digits.

  • Avoid the use of lots of decorative shapes and images within your report page that do not relay information to users. The screen reader reads each one. When using shapes and images to call out data points, use the alt text to explain what is being called out.

Meagan also has a follow-up blog post with more detail:

The data in the accessible Show Data table will render in the order it is shown in the visual, so you can control that in your design. One exception to this is when the data is rendered in a matrix rather than a table: the total in the accessible Show Data table is positioned at the top rather than the bottom where we see it visually. This is a purposeful design decision to help the user understand the total and then the breakdown of subtotals. Another good thing about the accessible Show Data tables is that tooltips are included, just like when we use the See Data feature.

Another nice feature (not sure if this is built in to JAWS or something the Power BI team added) is that if you have a report page that takes a while to load JAWS will say “Alert: Visual are loading” so it’s obvious to a blind/low vision user that they need to wait to get the full report page.

There is still a bit of work to be done to make Power BI truly accessible to screen readers.

Both posts are definitely worth the read.

Comments closed

Building A Power BI Date Dimension

Published 2018-02-06 by Kevin Feasel

Martin Schoombee explains why you should have a date dimension in Power BI and then has a script which generates one:

If your source system does not contain a Date entity or dimension, a better way is to create a such an entity with Power Query (aka “M”). By creating your own, you can add more attributes than just year, quarter and month…and optimize the size and performance of your model at the same time.

Not a Power Query expert? You don’t have to be. Others in the community have already shared their versions, and I will share my own modified version that you can copy and use for yourself. I’ve used the examples of Matt Masson and Chris Webb as the basis of my own Date entity. You can find their versions here and here.

Read on for Martin’s version.

Comments closed

Library Paths In R

Published 2018-02-05 by Kevin Feasel

Stacia Varga troubleshoots an issue integrating Power BI with R:

As I was putting together an example of using an R script as a Power BI data source, I ran into some issues on my development machine that was frankly driving me crazy. When I tried to run the query in Power BI with my R script (that ran successfully in the IDE, by the way), I kept getting this message:

DataSource.Error: ADO.NET: R script error.
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :

  namespace 'scales' 0.3.0 is being loaded, but >= 0.4.1 is required

Error: package or namespace load failed for 'rnoaa'

Execution halted

Stacia’s answer works as long as the .libPaths() results match expectations.  Another idea would be to set the R_LIBS_USER user-level environment variable to the desired starting directory and that should force the directory in the environment variable to be first when calling .libPaths().

Comments closed

Displaying Power BI Filter Values

Published 2018-02-02 by Kevin Feasel

Reid Havens shows how to display Power BI report filter values on the report itself:

The client and I brainstormed, and we decided to create card visuals to identify filter selections. The beautiful thing about DAX, as mentioned in many articles on our site, is that it can easily return text values. I actually did something similar to this with Dynamic Titles when I posted about Power BI’s new Drill Through feature last year, that article can be found here. So, I essentially wanted to do something similar here, but to call out the filter selection for each slicer. The end result looked something like this

The end result was a new section of the report, dedicated to calling out slicer selections. The BIGGEST reason the client wanted this, was for screenshots. They often took screenshots of this report, and pasted it into emails or slides to use in presentations. The result works well, and uses a bit of clever DAX to always return the right selections, no matter the combination of selections among the slicers.

Read on to see a couple odd scenarios that Reid ran into and how to fix them.

Comments closed

Power BI Cumulative Totaling

Published 2018-02-01 by Kevin Feasel

Martin Schoombee runs into an interesting issue with cumulative totals in Power BI:

A common practice in the data warehousing world is to use a Date Key as unique identifier in a date dimension. This attribute is usually a number in the format yyyymmdd. I’m not going to dive into all the reasons why it is used in data warehouse environments here, but (for fun) let’s change our data model to use the Date Keyattribute in the relationship between the two tables.

If we look at our visualizations again, we see a very different picture. Sales by date still looks the same, but the sales by month seems a little out of whack (image below). If you had cumulative sales at any other aggregated level (quarter, year, etc.) it would also have been incorrect.

The answer is not immediately intuitive, so it’s good to know this ahead of time rather than have to struggle with it later.

Comments closed

Power BI Desktop File Shrinkage

Published 2018-01-31 by Kevin Feasel

Eugene Meidinger notes that Power BI Desktop files are a bit smaller now:

I was working on a demo for my upcoming Pluralsight course, and I noticed something odd. It used to be that a empty PBIX file was 123 KB, but some point since May 2017, the file size has become 10 (!) KB. So what’s the cause of the difference?

If you rename a .pbix file to .zip, you can crack it open. If we look at two nearly empty files side by side, we can see the difference comes from the data model. In this example, each data model has a single value that I manually entered.

Yet another reason why it pays to keep up to date on Power BI versions.

Comments closed