Tech tips

This post demonstrates how to use the TMDL view in Power BI,to generate multiple DAX measures simultaneously, intended for use in conditional formatting and to improve visual design.

Introduction In this blog post I will share how to effectively leverage conditional formatting with a low code approach suitable for enterprise grade solutions. I have found that there are two key components that are critical for success: Field Values – predefined measures that dynamically specify colour values, icons and even Unicode characters. Power BI dataflows – a low code method that creates a single source of truth of field values and is effortless to manage and ensur

I recently had a minor whinge that when publishing a Power BI workspace as an App, it was difficult to tell the difference between the three types of links in the navigation pane on the left. To set the record straight, I love the functionality of publishing a workspace as an App. Coupled with the not-so-recent addition of Audiences, they are now an excellent way for consumers to interact with a "solution" as opposed to a folder of reports/semantic models/dataflows/etc. When

As I sit here in Pisa Airport after a wonderful break away with the family, I look in despair as my departure time gets more and more delayed. Looking at the full page of departures for today, I notice many departed late. I also noticed they got later as the day progressed. It raises that question oft noted that every delay has a further knock-in effect for subsequent flights in the day. Let's test that hypothesis! I loaded the departure data directly from the Pisa departures

Recently I spoke at an event where we did a hands-on exercise to create a simple Power Automate Flow, which sends a daily weather summary to my inbox at 6am every day. Afterwards, I made some enhancements to the layout and thought I would share this with you. At high-level, the Flow is very simple: Get the weather and send the details to my inbox. Under the bonnet I had to draw on some relatively complex expressions to ensure the output was exactly as I needed. I will go into

This blog post demonstrates how to effectively use incremental refresh with binary files to drastically improve the refresh performance of your Power BI datasets and dataflows. Incremental refresh is widely understood and employed for structured, relational data sources like SQL Database and Azure Synapse. It is not as commonly known that incremental refresh can also be harnessed for other data sources such as binary files. The key to success is to ensure the binary files are

I use Power Automate to trigger Power BI dataset refreshes when files arrive in a source folder (like SharePoint Document library or Azure Data Lake Storage). In one scenario I had a dataset loading/combining files from multiple folders into separate tables. The files would land in the various folders at certain times of the day, often at once and in multiples. The issue I was facing was the Power Automate Flow would be triggered multiple times so I'd get failures that the da

In this blog post I will share how to effectively leverage the performance boosting functionalities available in dataflows when preparing large volumes of data. I have found there are two key components that are critical for developing efficient dataflows: Enhanced Compute Engine – loads data into a temporary SQL cache and enables Power Query to leverage query folding. Incremental refresh – partitions your data into smaller chunks to refresh only what you need. Focusing on th

If you are making use of the feature to store your Power BI dataflow results in your own Azure Data Lake Gen 2, then I'd like to share with you a Python script to get the latest file and load it into a pandas DataFrame. This came from a recent Twitter conversation Ruth Pozuelo, who has been doing some very cool updates on the matplotlib Python module. She asked: Is it possible to access a dataset curated with Power Query or/and DAX outside Microsoft walls? Like a dataflow or

At some of my most recent customers I've developed an efficient way to analyse & deliver more meaningful, actionable and business-impactful insight to the users. I'd like to share this with you! This is based on Power BI, so I'll talk about DAX and Power BI report design, but it could be adapted to any tool. Using the Adventure Works sample database I created a couple of examples. It reads like a DevOps User Story, which is good as it switches to being busines-driven over dat

Recently a customer provided access to an Azure SQL Database on their tenant. They had it secured using firewall rules and so requested any IP addresses from me to add to the rules. Given this was part of a Power BI Solution, this forced the need for an Azure Virtual Machine with a fixed IP and the Power BI on-premises gateway That immediately means cost in running the VM and time/effort securing/patching/updating the OS and the Gateway software. They lost me at hello! The Vi

Datalineo started operating in August 2018. Based on our calculator, that makes us four years old this August! We have thoroughly enjoyed the last four years and are delighted to see our team continue to expand and deliver best-in-class solutions across Logistics, HR, Manufacturing, Cybersecurity & much more. To celebrate this milestone, we are putting on our Power BI Kickstarter Class for free! This is a full-day, in-person class where you'll gain knowledge about building a

At a customer site, I developed some Power BI dataflows and datasets that connect to Azure Storage. Recently, we decided to secure the storage accounts using the built-in IP Address and Virtual Network options. It was a little challenging to get the Power BI Service to connect to the storage, so this blog is going to run you through the options & hopefully save you time, and money. Up front, there are a lot of things I won't be covering as we'd be here all day. So Private En

I've blogged twice (here and here) about authentication using the msal Python module. I'm going to cap off this topic with a third and final entry by moving onto the azure-identity module. When I first looked at Azure authentication for the Power BI REST API, I honestly kind of fell into using the msal library first. I found it easy to use and went on to blog about it. Recently, I needed to use the azure-identity module took the time to try it out for getting access tokens to

In February 2021 at the Scottish Summit, I spoke about Dataflows & Shared Datasets and how you can evolve your Power BI architecture using these two super cool features. The session was recorded and uploaded to YouTube, so I thought I'd share it here too. Enjoy!

The ability to load data from Parquet files into Power BI is a relatively new thing and given it's storage structure, I wanted to see how Power Query dealt with it, and whether it gave any improvements over the more common format of CSV. This is a pound-for-pound Import-mode comparison between the two file types, covering the reading of the file and processing in the Power BI Data model. Early admission, some of the results I've shared below are not strictly related to CSV vs

In the Business Intelligence world, there is consensus among some that Pie Charts have no place on a report. There's even a Twitter hashtag: #SayNoToPieCharts. I'm here to offer my defence of this visual, and by the end of this article, if you haven't reconsidered your stance I will gladly eat humble pie chart. Without going too deeply on this subject, data visualisations are intended to invoke visual queues for us to spot a trend, comparison or outlier to direct our eyes to

I had a requirement to clean out all data in a field except for the numbers. This was a plain text field where users would manually enter data and within the text was an order number. For example, from the below I just want 51638, the rest is discarded. The data I got was never entered consistently (humans, am I right?) so using any kind of pattern to obtain the order number was not possible. The below shows an example of the data, and the result I want. Power Query has a won

When you perform a Distinct operation in Power Query, the elimination policy is to keep the first instance it sees of the column(s) you have nominated, then discard any subsequent matching rows. There are occasions when you may want to be more selective in choosing which row you keep. For example, the below shows a list of Products with a Part Number, Description & Last Sold Date. As you can see, the description has changed over time, so I want my result set to reflect the cu

I have several customers using Dynamics CRM and have built Power BI solutions using these as a data source. Dynamics CRM uses the Common Data Service, now known as Microsoft Dataverse, as it's storage layer and being able to search or browse objects in there has been a challenge if you don't have access to the Dynamics/Power Apps admin portal. The solution I am sharing below is going to be redundant in about 20 minutes as the evolution of the data platform, encompassing Datav