Power BI

In a previous post I showed how to trigger a semantic model refresh from a Fabric notebook using sempy.fabric. That approach works great for small models, but in production you’ll quickly run into problems when the model has many tables or large fact tables. The problem: out of memory The sempy default for max_parallelism is 10. For a model with 27 tables including several large fact tables (tens of millions of rows), processing 10 tables concurrently can easily exhaust the available memory on the capacity and the refresh fails with a generic “out of memory” error — or worse, it succeeds but takes an unreasonably long time because of internal retries. ...

The sempy Python library in Fabric is quite powerful and offers a range of capabilities that make working with Fabric environments more efficient and flexible. One of the most useful features I’ve implemented in my projects is the ability to trigger semantic model refreshes directly from a Spark notebook. This approach allows to trigger the refresh right after the data load is finished. import sempy.fabric as fabric import time workspace = fabric.resolve_workspace_name() dataset = "My_Amazing_Semantic_Model" refresh_request_id = fabric.refresh_dataset(workspace=workspace, dataset=dataset, refresh_type="full") # refresh api is async, so we need to poll until it completes while True: time.sleep(60) res = fabric.get_refresh_execution_details( dataset=dataset, workspace=workspace, refresh_request_id=refresh_request_id ) if res.status != "Unknown": break if res.status == "Completed": print(f"Refresh Dataset completed with success {res.extended_status}") else: errors = res.messages["Message"].str.cat(sep="\n") raise Exception(f"Refresh Dataset failed: \n\n{errors}") This script starts by resolving the current workspace and identifying the target dataset. It then initiates a full refresh of that dataset and waits for the process to complete, checking periodically for updates. ...

Last weeks I had several meetings with Microsoft support to try this wrong credentials issue. The problem was that after right clicking to “View Usage Metrics Report” the report would come up empty and after 24h you would get an email with the following message: <ccon>The credentials provided for the UsageMetricsDataConnector source are invalid. (Source at UsageMetricsDataConnector.)</ccon>. The exception was raised by the IDbCommand interface We tried several approaches until we found this piece of documentation: https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-modern-usage-metrics#considerations-and-limitations ...

As a data enthusiast, I’m always on the lookout for ways to streamline my workflow and make my life easier. In this post, I’ll be sharing two Tabular Editor scripts that have saved me countless hours of manual formatting in my Power BI projects. There are two versions of this software. To use the scripts below you only need version two. This version is free and open source: https://tabulareditor.github.io/TabularEditor/ If you have the money for the license you can support the author (Daniel Otykier) by buying the license for version 3: https://tabulareditor.com ...

Yesterday I had the pleasure to be back to in-person to give a small fun presentation about bending Power BI to help us solve Wordle puzzles. The content is in Portuguese but I will upload an English version during this week. Here’s the link on GitHub where you can download the file: https://github.com/fpvmorais/presentations If you have any questions please reach me. Have fun!

A few days ago I had to add a new team member to more that 50 workspaces. I started dying inside just thinking of going to each workspace and add it using the portal ui. Here’s a little snippet that saved me a few hundred clicks: Login-PowerBI $workspaces = Get-PowerBIWorkSpace $user = "mySpecial.User@MyDomain.com" foreach($workspace in $workspaces) { Write-Host "Adding User to : " $workspace.Name try { Add-PowerBIWorkspaceUser -Id $workspace.Id -UserPrincipalName $user -AccessRight Member } catch { Write-Host "Message: [$($_.Exception.Message)"] -ForegroundColor Red } } Adapt it to your needs. ...

There’s now an official connector to Google Spreadsheets on Power BI With the November 2021 Power BI release, came the official connector support for Google Spreadsheets from Microsoft. As such there’s no more need for my custom connector. The repository on GitHub is now archived. No more Relationship tag contains incorrect attribute. Line 2, position 86 During the last week I’ve tested it so see if it had the same limitations as mine had and I’m happy to report the XML errors when trying to import data from a Google Sheet with a pivot table don’t occur in the new one. No more having to create a proxy file and use the IMPORTRANGE function. ...

Today’s puzzle is called ‘Ticket Translation’. We have a bunch of fields in a language we do not understand and we need to figure out which is which. There’s a set of rules we collected in several ways to a document that will serve as the puzzle input. If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. Part 1 Our input file is divided into three sections separated by a blank line: ...

Rambunctious Recitation is number game that follows the rules of the Van Eck sequence. Here’s a great video from the Numberphile channel on YouTube about it. And here start our troubles because it seems no one has yet found an efficient algorithm for generating this sequence. We will really need to brute force it. How can we resolve this predicament with the tools available to us? If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. ...

The troubles are far from over, this time it’s the docking program on the ferry that’s not compatible with the port computer system. We need to emulate the port’s software by using a bitmasking system to figure out the correct instructions. Let’s see if we are able to implement this logic with Power BI. If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions. Part 1 As usual there’s a file with a set of instructions. Some lines refer to memory addresses and others to bitmasks. We need to apply the mask to the values stored in the memory addresses beneath it until we reach the next mask repeating the process to the end of the file. ...