Press "Enter" to skip to content

Category: Power BI

R Links

Published 2016-12-16 by Kevin Feasel

Ginger Grant has some links on learning R in the context of Power BI:

Comprehensive Resource Archive Network [CRAN] is where one can download Open Source R, packages and contains lots of information about R.

Microsoft R Open which is a fully CRAN compatible version created using the Intel MKL for improved performance can be downloaded here.

One thing I would push a little bit on that list is R Tools for Visual Studio.  My default R IDE is still R Studio, but RTVS has made some nice improvements, and it’s worth checking out.

Comments closed

Dial Gauge

Published 2016-12-15 by Kevin Feasel

Devin Knight explains the dial gauge custom visual:

  • The effectiveness of gauges on dashboards is an often debated topic.

  • The Dial Gauge is completely data driven. Which means not only must your measure (drives the needle) come from a dataset but also the different thresholds ranges must come from your dataset too.

  • There are no specific Format settings for the Dial Gauge, which does limit you a bit with what you can do with this gauge.

There are certain scenarios in which I think the dial gauge works well.  The best scenario is the the same as its analog counterpart:  when you are measuring a single continuous variable with a safe range and meaningful range differences.  This scenario occurs less often than you might think.

Comments closed

Power BI Drillthrough

Published 2016-12-12 by Kevin Feasel

Ginger Grant explains how to create and use hierarchies in Power BI:

Finding where to create hierarchies is the hardest part of creating them in Power BI, especially if one has ever created hierarchies in Excel Power Pivot as they are not it the same place. Hierarchies are not in the Relationships data view, instead they are found in the Report view. Right clicking on the ellipse next to any field in a table displays a menu, and the second item on the menu is New hierarchy. Hierarchies can also be created by clicking and dragging a field on top of another field, which also will create a hierarchy. Once the hierarchy has been created, to add another field to the hierarchy, drag a new value on top of the value with the hierarchy icon. If the value added is not added to the location you want it, click on the ellipse next to the field named and move the field up or down as you wish.

Ginger also shows how to create drillthrough reports once you have hierarchies in place.

Comments closed

Passing Parameters To SQL Queries Via Power BI

Published 2016-12-12 by Kevin Feasel

Chris Webb shows how to use the Value.NativeQuery() function to pass parameters to SQL Server queries:

It looks like, eventually, this will be the way that any type of ‘native’ query (ie a query that you write and give to Power Query, rather than a query that is generated for you) is run against any kind of data source – instead of the situation we have today where different M functions are needed to run queries against different types of data source. I guess at some point the UI will be updated to use this function. I don’t think it’s ‘finished’ yet either, because it doesn’t work on Analysis Services data sources, although it may work with other relational data sources – I haven’t tested it on anything other than SQL Server and SSAS. There’s also a fourth parameter for Value.NativeQuery() that can be used to pass data source specific options, but I have no idea what these could be and I don’t think there are any supported for SQL Server. It will be interesting to see how it develops over the next few releases.

It’s good to know that you can parameterize queries now.

Comments closed

Importing CSV Files In Power BI

Published 2016-12-08 by Kevin Feasel

Gil Raviv explains the new “combine binaries” feature of Power BI Desktop:

The Power BI team has recently released an enhanced “combine binaries” experience as part of November 2016 update to Power BI Desktop. (Jargon Alert:  “Combine Binaries” is a scary term.  Instead it should be named “Magically combine multiple files together into one table and make me SUPER happy.”)  The improved experience can drastically help you to import multiple Excel or other files from a folder and avoid writing advanced query functions. But today we will focus on a specific scenario, which is so common that it deserves this special post – Handling CSV files.

In fact, today’s blog post is actually the first post in “The CSV Series”. I hope you will enjoy it. To celebrate the November update of Power BI Desktop, we will review the improved experience, and will walk you through one of the most common scenarios that is now so easy to implement – Importing multiple CSV files from a folder, including parts of their filenames.

This looks very useful.

Comments closed

R + Power Query

Published 2016-12-07 by Kevin Feasel

Ryan Wade makes his argument that R can be more powerful than M inside Power Query:

I want to leave you with two more things. If you look at the trade balance data set you will notice that it is not in a good format for data analysis. Here is a link to the file if you want to take a closer look. When you are doing data analysis you want your data to be in a “tidy” format. A “tidy” format means that each column represents a variable and each row represents an observation. To make this data set “tidy” you need to reformat the data into the following format: Country, Year, Trade Balance, Exports, and Imports.

This was an interesting example.

Comments closed

Sankey Custom Visual

Published 2016-12-07 by Kevin Feasel

Devin Knight looks at the Sankey visual in Power BI:

In this module you will learn how to use the Sankey Power BI Custom Visual.  The Sankey is a type of diagram that visualizes the flow of data between a source and destination.

Sankey diagrams are among the most information-dense diagrams out there.  They aren’t general-purpose diagrams, but for someone willing to take the time to unpack them, they can be quite informative.

Comments closed

Custom Functions In Power BI Desktop

Published 2016-12-06 by Kevin Feasel

Reza Rad explains custom functions:

Benefits of Custom Function

  • Re-Use of Code
  • Increasing Consistency
  • Reducing Redundancy

With a Custom function you are able to re-use a query multiple times. If you want to change part of it, there is only one place to make that change, instead of multiple copies of that. You can call this function from everywhere in your code. and you are reducing redundant steps which normally causes extra maintenance of the code.

I like Reza’s example of reading from a holidays table, as it’s easy enough to follow without being so trivial that it leaves you to wonder what the real value is.

Comments closed

Custom R Visuals In Power BI

Published 2016-12-05 by Kevin Feasel

Ginger Grant notes that there are R-powered custom visuals for Power BI:

Interacting with R visuals works differently than with other report visualizations as you cannot click on elements within the visualization and filter other items on the page. Other visuals on the page will filter the data contained within the R visual. For example, let’s say my report contains a total field, a slicer which contains years and a correlation plot which contains products. If the slicker is changed to select a year, total field and the data within the R visual will change to reflect that. If on the other hand, I choose to click on the R visual to select one of the product categories, the total field will not change and the R visual will not change. The R visual’s appearance will not change in any way.

Read on for more.

Comments closed