Press "Enter" to skip to content

Category: Power BI

Filtering by Cluster in Power BI

Joseph Yeates starts a two-parter:

This is a technique that I have used in reports that analyze a feature in a data set that is at the bottom of a hierarchy. For example, customer that belong to a larger customer segment or accounts that belong to a grouping. The report analyzes information for an individual customer or account; however, we want to bring in some comparisons for other customers or accounts that belong to the same segment, grouping or cluster.

Click through for the technique and stay tuned for part two.

Comments closed

Power Query Design the Right Way

Paul Turley continues a series:

Life is full of choices and trade-off decisions. Let’s say that you need to create a lookup table containing sales regions and this information doesn’t exist in the source database. You could easily create a new table in Power Query using the “Enter Data” feature, and just manually enter the sales regions. This would solve the immediate problem with very little effort, but how will the table be maintained if sales regions are added or change in the future? We could keep the list of values in an Excel file stored in SharePoint for a business owner to maintain when information changes. You could also go all the back back to the beginning of the process and load a dimension table in the source database. IT professionals might take the hardline and say that all data must flow through the data warehouse regardless of the effort and cost. Which of these is the right choice? …it depends on the scope reporting project, and the long-term ownership of the solution. Sometimes quick and convenient are OK but particularly tables that need to scale and handle larger data volumes in the future, the following guidelines are critical.

Read on for Paul’s recommended practices.

Comments closed

Power BI Model Documentation

Marc Lelijveld reflects on an overlooked part of development:

I strongly believe that it is key to describe everything that you have built as part of your Power BI model. As we all have a hate-love relationship with documenting our work, the external tool that I build to document your Power BI solutions could come in useful. In particular everything you add and only exists in your model is important to describe properly. This documentation is key once you share your data model with others and they try to understand the goal of a measure, column or anything else. Other than that, documentation also comes in useful if you handover your solution to your colleague or client.

Read on to see what you can currently document.

Comments closed

Power BI Tools

Benni De Jagere shares a list of useful tools around Power BI:

The External Tools (and the Enhanced Metadata format enabling it) allow end users of Power BI Desktop to call on custom built applications, scripts, .. to augment their developer/designer experience. These days, there’s over 40 (I stopped counting) external tools available, each with their own use case and focal area. When showing off some of the capabilities to my clients, it amazes me to see how quickly they pick up these things, and start building out their own ways of working.

Depending on the client, their IT Compliancy rules, the business and technical requirements, my actual tool belt tends to vary. Not every IT organisation allows user to freely install an application, digitally signed or not, so this is definitely an important one to take into your conversations early on.

Read on for Benni’s choices.

Comments closed

Testing Power BI Report Server Datasources

Aaron Nelson has some cmdlets for us:

I finally did it. I created a function I’ve been wanting to be able to use for *years*. Test-RsRestItemDataSource is here.

I can’t tell you how many times I’ve started to work on a report I was told was working, only to find the connection info was invalid. This wastes valuable time, especially when you’ve already made changes to the report.

Other times, I’ve been asked to figure out why a bunch of subscriptions weren’t working, only to find out it was a simple connection issue. I’ve always wanted a simple PowerShell command to check the credentials of a bunch of reports before I touch anything.

Turns out, it wasn’t that hard to build at all. I only wish I had built it years ago.

Click through to see an example of this, as well as two more cmdlets.

Comments closed

Power Query Folding Indicators

Matthew Roche points out a nice addition to Power Query:

Because of the performance benefit that query folding provides, experienced query authors are typically very careful to ensure that their queries take advantage of the capabilities of their data sources, and that they fold as many operations as possible. But for less experienced query authors, telling what steps will fold and which will not has not always been simple…

Until now.

Read on for more information. I saw this for the first time in a recent presentation and was pleasantly surprised at how well it works.

Comments closed

Default Format String for Power BI Calculation Groups

Gilbert Quevauvilliers figures out how to display a calculation group’s default format string in Power BI:

Recently I was working on a customer’s data and one thing that was missed was when something was not selected the Calculation Group did not return any default Format String.

Below is how I solved this, with the tricky part being formatting of the measure to be displayed correctly.

This follows on from my previous blog posts with regards to Calculation Groups:

Power BI Visuals now support Custom Formatting for measures using Calculation Groups!

Create Currency Formatting Strings using Calculation Groups in Power BI Pro & Premium / Azure Analysis Services / SQL Server Analysis Services 2019

Click through for the process.

Comments closed

Reading Delta Lake Tables from Power BI

Gerhard Brueckl checks out the Apache Parquet connector in Power BI, reading from a Delta Lake:

“Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem, regardless of the choice of data processing framework, data model or programming language.”

However, Parquet is just a file format and does not really support you when it comes to data management. Common data manipulation operations (DML)  like updates and deletes still need to be handled manually by the data pipeline. This was one of the reasons why Delta Lake (delta.io) was developed besides a lot of other features like ACID transactions, proper meta data handling and a lot more. If you are interested in the details, please follow the link above.

Click through for a demo.

Comments closed

Refreshing Power BI Report Server Reports

Aaron Nelson has some new cmdlets for us:

I just wanted to give everyone a heads-up that a new version of the ReportingServicesTools module went out last week, and it includes 3 new PowerShell functions for working with Power BI reports on a Power BI Report Server (PBIRS) instance.

You can now Create (New), Get, & Start a CacheRefreshPlan of a Power BI report deployed to a PBIRS instance.  For clarity, these only apply to reports using an Imported model, not those using Direct Query.

Click through for more detail.

Comments closed

Modifying a Linked Entity in Power BI Dataflows

Reza Rad gives us a method to modify linked entities in Power BI dataflows:

It is not possible to change a linked entity in a dataflow in Power BI. Linked entities has to be modified only in the dataflow in which they are created. However, sometimes, you need to do a small modification to the linked entity in a chained dataflow. There is a very simple trick, In this article and video, I’ll explain how you can do that.

Read on to see how.

Comments closed