Press "Enter" to skip to content

Category: Power BI

Building Nested Data Types in Excel

Published 2023-12-26 by Kevin Feasel

Chris Webb shows off a feature:

A year ago support for nested data types in Excel was announced on the Excel blog, but the announcement didn’t have much detail about what nested data types are and the docs are quite vague too. I was recently asked how to create a nested data type and while it turns out to be quite easy, I thought it would be good to write a short post showing how to do it.

I came into this prepared to cringe, but it’s actually pretty cool insamuch as you’re using Excel as a UI for business work. And, let’s be honest, Excel is still the most common UI for business work out there.

Comments closed

Power BI, Event Streaming, and Notebooks in Microsoft Fabric

Published 2023-12-21 by Kevin Feasel

Tomaz Kastrun continues a series on Microsoft Fabric. Day 18 has us looking at Power BI:

We have created a Power BI report directly from the datalake and today we will check how to do same with dashboard and paginated reports.

Day 19 covers event streaming:

In Fabric, you can create streaming semantic model and when selecting you will get the usual sources:

Day 20 shows how you can work with notebooks in Microsoft Fabric:

Notebooks have been around for a long time and people, community, and professionals have proven the usability, practicality, versioning and reliability of notebooks. Not to mention the clarity and hygiene. But opinions are also divided.

The purpose of this post today is to check for a couple of functionalities that might not be that straightforward when it comes to notebooks.

Comments closed

The Updated Stacked Bar Chart in Power BI

Published 2023-12-21 by Kevin Feasel

Tom Martens reviews an updated visual:

Personally, the stacked bar chart holds a special place in my heart when it comes to data visualization. It’s the tool I find myself using most frequently, which is why I decided to share a template using Deneb that I’ve been utilizing for a considerable amount of time: https://www.minceddata.info/2023/11/12/the-better-rectangular-pie-chart/

With the December 2023 release of Power BI Desktop, I can almost create the Deneb visual, which is fantastic as it eliminates the need for an additional custom visual. It’s important to note that while I’m a huge fan of Deneb, I also serve as the Power BI/Fabric sherpa in a large organization, and for this, I always try to reduce overall system complexity.

Click through for a fairly complex example of the visual.

Comments closed

Generating Fabric Delta Tables from Power BI Semantic Models

Published 2023-12-19 by Kevin Feasel

Nikola Ilic is excited:

A few days ago, while preparing materials for the customer training on Microsoft Fabric, I stumbled upon a very interesting article at Microsoft Learn. The article describes how to integrate Power BI semantic models (aka datasets) into OneLake.

At first glance, this doesn’t sound like something epic, but when I started thinking more and more about it, I realized that this really might be a huge thing in many different scenarios. First of all, at the moment of writing, this feature is still in preview – this means, it can change to some extent in the coming months, before eventually becoming GA. Nevertheless, I decided to take a shot and explore what can be done with OneLake integration for semantic models.

Read on to learn more about what this is doing and what you can do with it.

Comments closed

Warehousing and Power BI in Microsoft Fabric

Published 2023-12-18 by Kevin Feasel

Tomaz Kastrun continues a series on Microsoft Fabric. Day 15 covers building a warehouse:

I have named my as “Advent2023_DWH”.

You can create a warehouse using T-SQL scripts, from data flow gen2, from data pipelines and from the sample data. Let’s select the sample data and grab a coffee.

Day 16 looks at data pipelines:

With the Fabric warehouse created and explored, let’s see, how we can use pipelines to get the data into Fabric warehouse.

In the existing data warehouse, we will introduce new data. By clicking “new data”, two options will be available; pipelines and dataflows. Select the pipelines and give it a name.

And Day 17 provides a primer on how Power BI can read Fabric assets:

Within the Power BI in Fabric, you will find many of the components, that can be used to create a final report. And here are the components:

Comments closed

Delta Table Incremental Refresh in Power BI

Published 2023-12-18 by Kevin Feasel

Chris Webb shows off a bit of functionality:

One of the coolest features in Fabric is Direct Lake mode, which allows you to build Power BI reports directly on top of Delta tables in your data lake without having to wait for a semantic model to refresh. However not everyone is ready for Fabric yet so there’s also a lot of interest in the new DeltaLake.Table M function which allows Power Query (in semantic models or dataflows) to read data from Delta tables. If you currently have a serving layer – for example Synapse Serverless or Databricks SQL Warehouse – in between your existing lake house and your import mode Power BI semantic models then this new function could allow you to remove it, to reduce complexity and cut costs. This will only be a good idea, though, if refresh performance isn’t impacted and incremental refresh can be made to work well.

Click through to learn more about the performance of this operation and how it all works.

Comments closed

Data Modeling for Sankey Charts in Power BI

Published 2023-12-14 by Kevin Feasel

Marco Russo and Alberto Ferrari explain when Sankey charts can actually make sense:

Picture this: you manage a company that sells subscription services on the web, and you want to track the evolution of your customers by analyzing different events to understand how many customers start a trial before they purchase or how many renew or cancel their subscriptions.

The report should look like this: the darker flow indicates the number of customers who skipped the trial and went directly from a phone call to purchasing a subscription.

Read on for that sales funnel example and how you can prepare the data to make best use of Power BI’s Sankey chart visual.

Comments closed

Refreshing Individual Tables and Partitions using Semantic Link

Published 2023-12-08 by Kevin Feasel

Sandeep Pawar doesn’t have time to refresh everything:

The latest version of Semantic Link (0.4.0) has many methods that provide a convenient abstraction for calling Fabric/Power BI REST APIs. You can see them here. In this blog, I will show how to use the .refresh_dataset() which uses the Enhanced Refresh API to refresh Power BI semantic models, tables and partitions.

Read on for two ways to do it.

Comments closed

Anti-Joins and Folding in Power Query

Published 2023-12-08 by Kevin Feasel

Chris Webb has a workaround:

Power Query allows you to merge (“join” in database terms) two tables together in a variety of different ways, including left and right anti joins. Unfortunately, as I found recently, anti joins don’t fold on SQL Server-related data sources, which can result in performance problems. Luckily there is a different way of doing anti joins that does fold.

An anti-join, by the way, is the type of thing you use when performing a NOT EXISTS operation: what is in driver table A that is not in lookup table B given some condition set?

Comments closed