Press "Enter" to skip to content

Category: Power BI

Using Dynamic Datasets in Power BI

Published 2020-07-27 by Kevin Feasel

Jose Mendes implements the screening pattern in Power BI:

I recently came across the need to build a screening pattern based on the Kimball “Screening” concept. One of the desired outputs was a Power BI report that allowed a data steward to easily identify the failed screen, drill down to the detail and show the row(s) rejected by the data quality rule. To achieve this goal, I had to mimic in Power BI an SSRS functionality called dynamic dataset, which allowed me to, using a single matrix, show different source columns based on a selected screen. Feeling curious already? Then, let’s dive into the details.

A screen is designed to operate on a single input file or database table and contains the data quality condition to check. For example, there could be a Missing Customer Postcode screen which would test for any customers who are missing a postcode.

Read on for an implementation.

Comments closed

Python and Power BI Desktop

Published 2020-07-24 by Kevin Feasel

David Eldersveld continues a series on Python as an external tool in Power BI. Part 2 is about defining a proper JSON formatted file:

To see a new tool in the Power BI Desktop ribbon, you need to define a JSON file and place it in a specific folder on your workstation. The featured external tools Tabular Editor, DAX Studio, and ALM Toolkit have installers that take care of this step. Since you do not have a dedicated installer to place this file in the required directory, you need to manually define your own.

As long as the “enhanced metadata format” for the data model is enabled, and the JSON in your file is accurate, you should see your new tool in your ribbon after you re-open Power BI Desktop.

Part 3 shows off virtual environments in Python as well as how to connect to the Tabular Object Model:

The Tabular Object Model (TOM) library for .NET opens Power BI’s data model to external tools. Two pieces are required to allow Python to interface with .NET:
1) Pythonnet package for .NET CLR (pip install pythonnet)
2) Python-SSAS module (ssas_api.py placed in the same folder as the main script you’d like to run)

The python-ssas (ssas_api.py) Python module that facilitates the TOM connection is all the work of Josh Dimarsky–originally for querying and processing Analysis Services. I simply repurposed it for use with Power BI Desktop or the XMLA endpoint in Power BI Premium and extended it with some relevant examples. Everything relies on Josh’s Python module, which has functions to connect to TOM, run DAX queries, etc.

This does look pretty handy.

Comments closed

Marking a Table in DAX as a Date Table

Published 2020-07-24 by Kevin Feasel

Alberto Ferrari explains why you have to mark a date table as such in Power BI:

Even though this probably does not surprise you, in fact it should. The measure – as it is written – should not work. The reason why it works is because the relationship between Sales and Date is using the Sales[Order Date] column. Sales[Order Date] has a Date data type. If we use an integer to relate the two tables instead of using a Date column, the formula stops working. We now change the relationship using the integer column Sales[OrderDateKey] instead of the Sales[Order Date].

Read the whole thing.

Comments closed

Python in Power BI Desktop

Published 2020-07-22 by Kevin Feasel

David Eldersveld dives into using Python as an external tool in Power BI:

Why use Python as an external “tool”? Even though Python isn’t a “tool” in the same sense as the “Big 3” community tools focused this month, I want to show how versatile the External Tools feature is. I also want to encourage people to use imagination and also explore how Power BI isn’t really as closed as some people think–at least the data model…

Some of these ideas are not exclusive to Python, but there’s enough variety in the Power BI and data science communities for people to possibly figure out if some of this might be useful within the context of their own environments, skills, and organizations.

David also follows up with a series of sample ideas.

Comments closed

Adding Tooltips to Column Headers in Power Query

Published 2020-07-20 by Kevin Feasel

Chris Webb has some fun with M:

How does this work? Here’s what each of the steps do:

– The source step creates a simple table with two columns called firstname and lastname using #table (see here for more details on that).
– The tabletype step declares a new table type with two columns (the same two columns in the table from the previous step) and then adds a metadata record to this type. In that record the Documentation.FieldDescription field contains the text values that will appear as tooltips when you hover over each column.
– The replacetype step replaces the type of the table returned by source with the type declared in tabletype.

Click through for the code.

Comments closed

Handling Time Zones in Power BI

Published 2020-07-17 by Kevin Feasel

Teo Lachev has some tips on working with time zones in Power BI:

A client has Power BI models connected to Dynamics Online. Dynamics stores all dates in UTC instead of keeping the time offset, such as 7/14/2020 1:21:29 AM +00:00. Naturally, the users want to see dates localized to the US Eastern Time zone. Easy, right? Use the Power Query ToLocal time transformation (in the Transform ribbon, expand Time, and then click To Local) to offset with the desired number of hours. But there are a few issues with this approach:

Read on to understand why this approach won’t cut it, and to get one which will.

Comments closed

Getting Details by the Max Value of a Column in Power BI

Published 2020-07-17 by Kevin Feasel

Ed Hansberry solves a problem in a world without window functions:

When you group data in Power Query, you are able to do a number of aggregations, like count, sum, min, max, etc. The problem is each of those aggregations only operate on that column. Let me show you what I mean. Suppose you have this data:

You need to return a table to the DAX model (or an Excel table) that only has one record per product, and you want the latest date, and the sales person associated with that date. So for product A, it is June 18, 2020 and Suresh, for B it is June 14, 2020 and Ana, and so on.

In the SQL world, this is where a combination of common table expression and ROW_NUMBER() could get the top record partitioned by product ordered by date descending. Click through for Ed’s DAX-based solutions.

Comments closed

Creating a Power BI Streaming Dataset

Published 2020-07-16 by Kevin Feasel

Rob Farley takes us through the process of creating and using a Power BI streaming dataset:

Real-time Power BI sets are a really useful feature, and there’s a good description of them at https://docs.microsoft.com/en-us/power-bi/connect-data/service-real-time-streaming. I thought I’d do a quick walkthrough specifically around the Push side, and show you – including the odd gotcha that you might not have noticed.

To create a dataset that you want to push data into, you need to go to the Power BI service, go to your Workspace, and create a Streaming dataset. Even if you’re not wanting to use it with a streaming service, this is the one you need.

Rob has plenty of animated GIFs to walk you through the process, as well as a couple of caveats if you want to play along at home.

Comments closed

A Warning on Power BI Custom Visuals

Published 2020-07-16 by Kevin Feasel

Martin Schoombee gives us a warning around relying upon free custom visuals:

Before you get the impression that I’m against custom visuals, let me say this: I love custom visuals! I myself have used many custom visuals in the past and have been very quick to look for a custom visual when I couldn’t get something to display or work the way I needed it to in Power BI.

Custom visuals fill an important gap where the base product is not yet where it needs to be, and what better way for Microsoft to see what people need and where they need to invest more time from a visualization standpoint? It’s an awesome concept and I like it.

Unfortunately there are a few BUT’s to follow, but let me first tell you my story…

Read the whole thing. I like custom visuals a lot, but there are risks in a corporate world, and I don’t necessarily mean security.

Comments closed

Replacing GUIDs with Surrogate Keys in Power BI

Published 2020-07-15 by Kevin Feasel

Matt Allington finds another place where GUIDs aren’t your best option:

I was doing some work for a customer this week – they had a performance issue with a Power BI report. The data in the workbook wasn’t overly large, about 400,000 rows, yet the file size was 110 megabytes and the performance of the model was relatively slow given the number of records. When I looked at the report I noted that the report was using GUIDs between the primary and foreign keys on a number of tables. Generally speaking, it is not good practice to use a GUID to join tables, as GUIDs do not compress well and have a negative effect on the efficiency of physical 1 to many relationships.

Read on to learn more as well as what you can do about it.

Comments closed