When I was developing a new dataset, which was going to be referenced by many hundreds of reports, I wanted to be able to tell the end users that this dataset is under maintenance for some time. It is a kind of user friendliness if the user immediately knows that the visualized data in Power BI service can be wrong.

I was searching for a simple solution to inform the users that I am currently working on the dataset. It means some reports may deliver wrong data or some visuals may not work properly.

How can it happen that a…


In this short blog post, we will learn how to create a separate table for measures which are developed by citizen developers when creating a new report. This table will have an icon similar to an icon of a measure and will be placed on top in the list of all tables in the Fields pane.

Use case

Imagine the following use case. You are a Power BI expert who has created a dataset for your company. This dataset is used by many citizen DAX developers for creating reports via Live connection. You, as a dataset designer, can create new tables, columns…


A year ago, I was developing a solution for collecting and analyzing usage data of a Power BI premium capacity. There were not only some simple log files, but also data that I had to convert into a slowly changing dimension type 2. Therefore, I decided for the following architecture — Azure Data Factory pipelines collect data on daily basis, the raw data is stored in a data lake forever, and the cleansed data is then moved to a SQL Server database. Because the data is stored on a SQL Server, I can use incremental refresh in Power BI service…


Today I will deep dive into an elementary feature called Clipboard. You use it probably hundred times a day using the keyboard shortcut Ctrl+C and Ctrl+V. But do you know what happens behind the curtains in the Power Query Editor when you copy a query?

What is a clipboard in the computer science terminology?

Let´s ask Wikipedia. It says: “The clipboard is a buffer that some operating systems provide for short-term storage and transfer within and between application programs. The clipboard is usually temporary and unnamed, and its contents reside in the computer’s RAM. The clipboard is sometimes called the paste buffer.” (https://en.wikipedia.org/wiki/Clipboard_(computing))

How does it work internally?

You know that you can copy…


I have prepared an article about a transitive closure in Power Query. I wanted to publish the article already, but I decided to wait a little bit and write another one about a performance boost I’ve used in the code. It is a lookup table in Power Query.

Imagine the following scenario: You have a recursive algorithm and it contains maybe thousands of joins. You have just 1 table which you must join on itself in every step of a recursion. The result of the join is always at most one value. …


I’ve been testing incremental refresh for more than half a year in an enterprise environment and I’d like to share my experience with this feature.

You can read a full description of Power BI incremental refresh here. If you have not worked with incremental refresh yet, I recommend that you read the documentation before.

User voice

It was about 2 months ago when I read a blog post about the incremental refresh written by Christian Wade. He says that the Power BI team is pleased to announce that incremental refresh is now available in Pro instead of Premium only. There were more…


One of the many benefits of Power BI is the possibility to create your own theme file, which defines many design properties of a report, like colors, fonts, and many more. There are also tools on the internet that help you to create such a theme file. But, as you will see, there are some undocumented theme settings. This blog post is about those.

The corporate design

I work for a company which has its own corporate design. There are templates for Word, PowerPoint, newsletters, and so on. But we didn’t have an official one for reports in Power BI.

We went to…


Recently, I have been faced with a challenge of pivoting many tables. Every table was completely different, I needed some preprocessing steps, like filtering rows and so on. And I also didn’t want to pivot a huge table in Power Query. The solution? A dynamic pivot written in T-SQL and called from Power Query.

Let’s start with the dynamic pivot written in T-SQL. I have asked Uncle Google for a help. There are many different solutions on the internet. At the end, I have combined some of them and created the following stored procedure.

It has 3 parameters. The first…


Is there a simple way to convert a timestamp in UTC to your local time zone in Power Query? Yes and no. Let’s find out what the options are.

TLDR section

https://github.com/nolockcz/PowerQuery/tree/master/UTC%20to%20Local%20Time However, I personally recommend reading the article once before you use it in your project.

Research

I have started with research on the internet. Maybe there is a solution I can use without spending time on writing my own code. Let’s list some of them — I do not want to call them solutions, a finding is a better word, I think.

Finding №1

On forums, you can find a recommendation for using…


There are many things you do so often that you don’t think how they work. They just work and that is enough. Not today.

I would like to discuss a Power Query query which is automatically generated when you create a new table in Power BI Desktop via the Enter Data button.

The code is the following:

letSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk7NyTE0VNKBMIyADEOlWB2IuBFM3AgkbqQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t])
in
Source

When you open the Power Query Advanced Editor, you see a long line. …

Nolock

Cloud BI Professional Architect

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store