Press "Enter" to skip to content

Category: Power BI

Creating Power Query Custom Connectors

Published 2019-02-21 by Kevin Feasel

Eugene Meidinger shows how easy it can be to create a customer Power Query connector:

When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced features like query folding,  creating your very first connector is simple.

So, first you need Visual studio installed and the Power Query SDK installed as well. Once you do that, you will see Power Query as an option when creating a new project. Visual studio will also have support for .pq or Power Query files.

Click through for an example of the process in action.

Comments closed

Displaying Filters In Power BI Tooltips

Published 2019-02-21 by Kevin Feasel

Marco Russo shows how you can create a filter dump measure to show all active filters as part of your tooltip:

The Tooltips can display a string with multiple lines. This is useful for the DumpFilters measure that creates a new line for every column with a filter. You might wonder why the DumpFilters measure is required considering that Power BI can already display any filters and slicers affecting a visual. The reason is that the DumpFilters measure isolates the filters of a single cell and can show the effects of filters that are not visible in the standard visualization provided by Power BI.

This is interesting reading and a good way of sharing to users how they got to the current view of data.

Comments closed

Combining Stream Analytics And Azure ML With Power BI

Published 2019-02-19 by Kevin Feasel

Brad Llewellyn shows us how to feed Azure ML predictions into Power BI via Azure Stream Analytics:

Today, we’re going to talk about combining Stream Analytics with Azure Machine Learning Studio within Power BI.  If you haven’t read the earlier posts in this series, Introduction, Getting Started with R Scripts, Clustering, Time Series Decomposition, Forecasting, Correlations, Custom R Visuals, R Scripts in Query Editor, Python, Azure Machine Learning Studio and Stream Analytics, they may provide some useful context.  You can find the files from this post in our GitHub Repository.  Let’s move on to the core of this post, Stream Analytics.

This post is going to build directly on what we created in the two previous posts, Azure Machine Learning Studio and Stream Analytics.  As such, we recommend that you read them before proceeding.

Read on for the demo.

Comments closed

Extracting Numerical Data Points From Images

Published 2019-02-19 by Kevin Feasel

Matt Allington visualizes changes in the Gartner magic quadrant for BI tools:

Today Gartner released the 2019 magic quadrant for Business Intelligence.  As expected (by me at least), Microsoft is continuing its trail blazing and now has a clear lead over Tableau in both ability to execute and completeness of vision.  I thought it would be interesting to see a trend over time for the last 5 years, as this is the time period that I have been a professional Power BI Consultant.  I needed some way to extract the numerical data points from the images I had collected.  This article shows you how to do that.  Here is the final output – a scatter chart with a play axis in Power BI of course.

I was just commenting the other day about how somebody should do this and Matt went and did it.

Comments closed

Power BI Architecture Diagram V4

Published 2019-02-18 by Kevin Feasel

Dustin Ryan has a new version of the Power BI Architecture Diagram:

First and most importantly, I updated the Power BI logo in the diagram to the latest version of the logo!

Secondly, I included Power BI Dataflows in the diagram tagged #6. Power BI Dataflows are used to ingest, transform, integrate, and enrich big data by defining data source connections, ETL logic, refresh schedules, and more. Data is stored as entities in the Common Data Model in Azure Data Lake Storage Gen2. Dataflow entities can be consumed as a data source in Power BI and by using Power BI Desktop. Read more about Dataflows here.

Click through for a full changelog and a link to download the architecture diagram and legend.

Comments closed

Power BI Violin Plots

Published 2019-02-15 by Kevin Feasel

Meagan Longoria shows off a violin plot custom visual in Power BI:

A violin plot is a nifty chart that shows both distribution and density of data. It’s essentially a box plot with a density plot on each side. Box plots are a common way to show variation in data, but their limitation is that you can’t see frequency of values. In other words, you can see statistics such as min, max, median, mean, or quartiles, but you can’t see the individual values nor how often they occurred.

Read on for a review of the custom visual available for violin plots, including areas where it does well and where it falls short at present.

Comments closed

No Type Equivalence In M

Published 2019-02-14 by Kevin Feasel

Imke Feldmann notes an oddity in types in Power Query:

But this function will not return any matches. I also tried out a (potentially) slower version using Table.SelectColumns(Types, each [Value] = x[Types]) – but still no match. 

What I found particularly frustrating here was, that in some cases, these lookups or filters on type-columns worked.

That behavior seems odd to me. Imke shares a link from Microsoft which explains that the behavior occurs, but the why behind it eludes me.

Comments closed

Syncing Slicers In Power BI

Published 2019-02-13 by Kevin Feasel

Prathy Kamasani takes us through a recently added feature in Power BI:

As per Microsoft docs:
“This feature lets you create a custom group of slicers to keep synchronized. A default name is provided, but you can use any name you prefer.
The group name provides additional flexibility with slicers. You can create separate groups to sync slicers that use the same field, or put slicers that use different fields into the same group.”

First, let’s look at creating groups to sync slicers that use the same field. The use case Syncing within a page, we can easily use the group functionality to do this.

Click through for a few demos of increasing complexity.

Comments closed

Effective Identities And Power BI Embedded

Published 2019-02-13 by Kevin Feasel

Angela Henry shows how you can use Power BI Embedded for row-level security even when the accessing users don’t have Power BI accounts:

Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power BI accounts?  It seems like the only way to make dynamic row level security is to use the Username() DAX function?  But wait, doesn’t that require the user to have a Power BI account?  Sigh, it seems we are going in circles.

The one thing these articles don’t talk about is that when you are using Power BI Embedded, you can pass in whatever you like for the EffectiveIdentity via the Power BI API and it will “overwrite” the Username() function.  What?!  That’s right, it will completely ignore the Username() function and use whatever you give it.  WooHoo!

Read on for the details.

Comments closed

Using Profiler To Get Power Query Timings

Published 2019-02-12 by Kevin Feasel

Chris Webb shows us how we can combine DAX Studio with Profiler in order to time our Power Query operations:

And there you have it, exact timings for each of the Power Query M queries associated with each of the tables in your dataset. Remember that the time taken by each Power Query M query will include the time taken by any other queries that it references, and it does not seem to be possible to find out the amount of time taken by any individual referenced query in Profiler.

There is a lot more interesting information that can be found in this way: for example, dataset refresh performance is not just related to the performance of the Power Query M queries that are used to load data; time is also needed to build all of the structures inside the dataset by the Vertipaq engine once the data has been returned, and Profiler gives you a lot of information on these operations too. 

Check it out if you do any work with Power BI.

Comments closed