Press "Enter" to skip to content

Category: Power BI

Building a Cartesian Product with Power Query

Published 2020-03-03 by Kevin Feasel

Reza Rad shows how to build a Cartesian product using Power Query:

Sometimes, you need to create a multiplication of all sets of all pairs from two different data tables in Power BI. This action is different from Merge (Join) because there is no matching key columns and no way to relate the two tables together. However, you want to create a multiplication as a flatten table. If this process is needed to be done in Power Query, then there is a simple trick to do it, In this article, I’ll explain how you can do it.

Read on for that trick.

Comments closed

Distinct Counts in Power Query

Published 2020-02-28 by Kevin Feasel

Reza Rad shows how you can get a distinct count in Power Query:

You can have a distinct count calculation in multiple places in Power BI, through DAX code, using the Visual’s aggregation on a field, or even in Power Query. If you are doing the distinct count in Power Query as part of a group by operation, however, the existing distinct count is for all columns in the table, not for a particular column. In this article, I’ll show you a method you can use to get the distinct count of a particular column through the Group By transformation in Power Query component of Power BI.

Click through to learn how.

Comments closed

Preventing Query Timeouts with Power BI Incremental Refresh

Published 2020-02-28 by Kevin Feasel

Gilbert Quevauvilliers shows how to set the default timeout for a query against SQL Server from Power BI:

This was because on the first refresh it has to process all the data before it can incrementally refresh the dataset.

As per the documentation the default timeout for a SQL Server database is set to 10 minutes, and when I am processing a lot of data it can easily take longer than 10 minutes to return all the data.

Read on to see how you can change that if you need to.

Comments closed

Power BI and Tabular Model Relationship Types

Published 2020-02-25 by Kevin Feasel

Marco Russo takes us through the different types of relationships we might encounter in Power BI and Analysis Services Tabular models:

A relationship can be strong or weak. In a strong relationship the engine knows that the one-side of the relationship contains unique values. If the engine cannot check that the one-side of the relationship contains unique values for the key, then the relationship is weak. A relationship can be weak either because the engine cannot ensure the uniqueness of the constraint, due to technical reasons we outline later, or because the developer defined it as such.

A weak relationship is not used as part of table expansion. Power BI has been allowing composite models since 2018; In a composite model, it is possible to create tables in a model containing data in both Import mode (a copy of data from the data source is preloaded and cached in memory using the VertiPaq engine) and in DirectQuery mode (the data source is only accessed at query time).

There is quite a bit of useful information in here.

Comments closed

Entering Data into Power Query from Excel

Published 2020-02-25 by Kevin Feasel

Ed Hansberry shows a quick way to hand-enter some data into Power Query from Excel:

One of the cool things about Power BI is you have a nice “Enter Data” button on the home ribbon where you can quickly add data to your model. The table isn’t dynamic, but sometimes you just need a small table to finish your model to add descriptions or some other bit of data without creating another table in a source that needs to be connected to. Enter Data is perfect for this.

It did take a little bit of trickery to accomplish, but it’s pretty easy to do.

Comments closed

Writeback in Power BI Using Power Apps

Published 2020-02-24 by Kevin Feasel

Shabnam Watson shows how you can use Power Apps to write back to data sources in Power BI:

The Power Apps visual first became available as a custom visual in 2018 and then as one of the default visuals as of the October 2019 release of Power BI Desktop.

The Power Apps visual provides an important functionality to refresh a Power BI report page automatically which eliminates the need for the end user to manually refresh the page by clicking on the Refresh option from the Power BI menu to see changes in the data.

In this post, I will show you how to add a simple app to a Power BI report to update the data in the report and have the app automatically refresh the page. All of this can be done with a few lines of code thanks to all the work that has been done in Power Apps to make the app creation experience extremely user friendly and relatively easy to learn.

Click through for the demo.

Comments closed

Creating a Power BI Report Book

Published 2020-02-21 by Kevin Feasel

Teo Lachev shows us how to build a Power BI report book:

Scenario: Management has requested an easy way to view a subset of strategic reports located in different Power BI workspaces. You can ask the users to mark reports and dashboards as favorites so they can access pertinent content in the Favorites menu, but you’re looking for an easier configuration, such as to create a book of reports with a built-in navigation that organizes reports in groups (like a table of contents), such as the screenshot below demonstrates.

Click through for the instructions as well as a discussion on why you wouldn’t necessarily want to build a Power BI app for this.

Comments closed

Options for Read-Only Licensing with Power BI

Published 2020-02-20 by Kevin Feasel

Reza Rad explains that, depending on how much you’re willing to pay, there are ways of letting users view your dashboards for free:

In most of my presentations all around the world, I still get this question often: “Is there a Read-Only license for Power BI?”, and often starts with “I have some end-users, who are not building any reports, I don’t want to pay for Developer License for them”. I have written about Licensing in Power BI previously, however, I believe that the article is not explaining it clearly enough and there are still some questions around it. So here I am going to talk about this only: The Read-Only license for Power BI.

Read on for the answers. It’s not all terrible news, but at the very low end, the answer isn’t great.

Comments closed

Power Apps and Read-Only SQL Data Sources

Published 2020-02-19 by Kevin Feasel

David Eldersveld diagnoses a weird issue:

Recently when working with a table in Azure SQL Database, I came across an issue that manifested itself in Power Apps in a few ways.

The message stated that the data source is read-only, when I knew that my SQL credentials had the correct permissions to insert and update table records. I was successfully doing this with other tables, so SQL was not the issue.

Attempting to use functions like Remove and Patch provided the message, as did Form.Edit.

The solution kind of makes sense after the fact but there’s no way I’d have guessed it.

Comments closed

Reducing Visual Count to Improve Performance

Published 2020-02-18 by Kevin Feasel

Chris Webb explains that you might get better performance in Power BI with fewer visuals:

Before we go any further, I don’t want you to go and change your reports if you’re not going to get any benefit from doing so. Use Performance Analyzer (as shown here) to determine which visuals on your report are the cause of slow performance – there’s no point redesigning visuals that are fast anyway.

As a general rule the more visuals you put on a report page the slower it’s going to get. It’s logical if you think about it: the more visuals there are, the more queries have to be run against your dataset and the more work Power BI has to do to render the report. I know there is a tendency to try to pack as much information onto a page as possible and this often happens when someone else has designed the report you’re trying to build, but you should always try to resist this. Splitting a single large page into multiple smaller pages, using slicers or filters to reduce the amount of data shown at any one time and avoiding gigantic Excel-like tables are a good idea.

It certainly doesn’t mean “get rid of all of your visuals;” after all, speed is only one part of the story. Read the whole thing.

Comments closed