Press "Enter" to skip to content

Category: Power BI

Creating a Power BI Dataset and Report via DirectLake

Published 2023-09-07 by Kevin Feasel

Gilbert Quevauvilliers finishes up a series:

In the final part of my series, I am going to be creating the Power BI DirectLake dataset and report from my tables that I had previously loaded into the lakehouse.

In this series I am going to show you all the steps I did to have the successful outcome I had with my client where I created the dataset (measures and fields) and the Power BI report.

Click through for links to the prior posts, as well as a walkthrough on creating a DirectLake asset in Power BI.

Comments closed

Value and Hash Encoding in VertiPaq

Published 2023-09-07 by Kevin Feasel

Kristyna Hughes looks at column encodings:

Power BI encoding is a powerful optimizing option that is often overlooked because it’s not visible in neither the Power BI Desktop tool nor in Power BI Service. Natively, the VertiPaq engine in Power BI investigates all columns in the data model and determines how it can store that data most efficiently. To achieve maximum compression, the VertiPaq engine starts by encoding each column which determines the method of compression applied to that column. There are a couple types of encoding – value and hash.

Read on to learn the difference, as well as how to push your columns to use a specific type of encoding.

Comments closed

First Impressions of DAX Optimizer

Published 2023-09-07 by Kevin Feasel

Nikola Ilic takes a look:

A few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).

One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.

Comments closed

Power Query Templates

Published 2023-09-06 by Kevin Feasel

Reza Rad looks at a new feature:

Have you ever considered exporting your entire Power Query Editor project as a single object? Have you thought about what benefits this would bring for you? Things such as version control and team development can be on the horizon, bringing the ability to migrate between tools and services easily. Fortunately, such functionality exists, called the Power Query Template. In this article and video, I will explain what this is, how it works, and the importance of such a feature.

Reza is, on the whole, quite pleased with it.

Comments closed

The Value of KPIs and Cards in Power BI

Published 2023-09-05 by Kevin Feasel

Kurt Buhler and Stepan Resl give you a card:

When a user arrives at your report, they should be able to answer their most important questions in a few seconds. To do this, we typically put the most critical information in the top-left of the report (where we often look first). This information should provide a high-level overview, whereas additional details should be placed at the bottom of the report, behind interactions, or on later pages.

An effective and popular way to call attention to important numbers in Power BI is by using cards and KPI core visuals.

Read on for several examples and a breakdown of how they work best.

Comments closed

Querying the Power BI REST API from Fabric Spark

Published 2023-09-01 by Kevin Feasel

Gerhard Brueckl makes the call:

Microsoft Fabric has a lot of different components which usually work very well together. However, even though Power BI is a fundamental part of Fabric, there is not really a tight integration between Data Engineering components and Power BI. In this blog post I will show you an easy and reusable way to query the Power BI REST API via Fabric SQL in a very straight forward way. The extracted data can then be stored in the data lake e.g. to create a history of your dataset refreshes, the state of your workspaces or any other information that is provided by the REST API.

Click through for a list of operations, followed by the code you’ll need to pull this off.

Comments closed

Setting Table and Matrix Column Widths in Power BI

Published 2023-08-30 by Kevin Feasel

Kurt Buhler controls the horizontal, Kurt Buhler controls the vertical:

One challenge of the table and matrix visuals in Power BI is that it’s difficult to precisely and consistently set column widths. Unlike in Excel, where you can set the row and column widths in a spreadsheet, you have no option in the visual interface to control the column width property. However, it’s still possible to control it in the report metadata, which is exposed in the officially supported Power BI Projects format (.pbip) which is in preview. Notably, however, opening and modifying report metadata from this format isn’t yet supported. Despite that fact, it still works reliably, so I thought I’d demonstrate how to do this.

There are a fair number of steps involved but it all makes sense in the end.

Comments closed

Managing Security Roles for Hierarchical Organizations in Power BI

Published 2023-08-29 by Kevin Feasel

Marco Russo and Alberto Ferrari are working for The Man:

The security model in Tabular used by Power BI can filter rows of a table based on a DAX expression. When security is applied to a hierarchical structure, every hierarchy level is represented by a different column in the table. This structure can make it challenging to define a dynamic security filter based on the name of a node in the hierarchy, because the DAX expression must filter the column corresponding to the hierarchical level in which that node exists. If the security needs to be maintained dynamically in a configuration table, the resulting code may end up being extremely complex and hard to maintain, as well as create possible performance issues.

Without describing the complexity of solutions based on a filter applied directly to the appropriate hierarchy level, we want to describe a solution that minimizes the effort required in maintaining a configuration table for the dynamic security rules, while also providing good performance at execution time by minimizing the processing overhead required to apply the dynamic security.

Click through for the scenario and how you can implement this kind of security model in Power BI.

Comments closed

ADX Date and Time Representations in Power Query and Power BI

Published 2023-08-28 by Kevin Feasel

Dany Hoter does some explaining:

Data in ADX (aka Kusto aka RTA in Fabric) almost always has columns that contain datetime values like 2023-08-01 16:45 and sometimes timespan values like 2 hours or 36 minutes.

In this article I’ll describe how these values are represented in ADX in Power Query and in Power BI.

Notice that I don’t just say Power BI because timespan values have different types in Power Query and in Power BI.

Read on for those details.

Comments closed

External Table is Not in the Expected Format for Power Query

Published 2023-08-28 by Kevin Feasel

Chris Webb handles an error:

Sometimes when you’re importing data from files using Power Query in either Power BI or Excel you may encounter the following error:

DataFormat.Error: External table is not in the expected format

What causes it? TL;DR it’s because you’re trying to load data from one type of file, probably Excel (I don’t think you can get this error with any other source but I’m not sure), and actually connecting to a different type of file.

Read on for an example, a more detailed description of when you’d get the error, and how to fix it.

Comments closed