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.
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: [$($_.
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.
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.
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.
We arrived to the island after the ferry trip and we see ourselves in need of transportation to the nearest airport. Fortunately there’s a shuttle bus service between the sea port and the airport that could take us there.
Day 13 is a wonderful mathematical puzzle, following the theme of modular arithmetic of the previous one. Let’s dive into it as there some surprises with the numeric limits on Power BI.
After the waiting room we are at a ferry navigating to the island. There seems to be a problem with the navigation system, and we volunteered to help. We have a set of instructions and we need to make some sense of them to help the captain circumvent the storm.
The solution to the problem will be given by calculating the Manhattan distance (sum of the absolute values of its east/west position and its north/south position).
Today’s puzzle title is “Seating System” but it’s actually a variation of a well known zero-player game called Conway’s Game of Life. Many of us may recall this game as a popular choice for work assignments in computer science courses.
Dr. John Conway, the English mathematician who created this game was last year a victim of COVID-19. If you want to know a little more about him you can read this article that was featured this week on Hacker News.
Day 10 puzzle brings us an array of adapters. In the first part we will need to find the number of differences of 1 and 3 jolts. Part two asks us to find the total number of distinct ways in which we can arranje the adapters to connect the device to the charging outlet.
If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.
This puzzle looks a lot like day 1. We have a list of numbers and need to do some operations that will return some big numbers.
Let’s try to DAX out them.
If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.
Part 1 We have a list of numbers of each the first 25 are a preamble. After this, any number can be calculated as a sum of any pair of numbers in that interval.
In day 8 we find a some weird infinite loop on a handheld device. To fix this problem we are asked to create a program that goes trough a stack of instructions.
nop +0 acc +1 jmp +4 acc +3 jmp -3 acc -99 acc +1 jmp -4 acc +6 And after we find where the infinite loop is located we replace the bad command with the correct instruction.
If you want to follow along please download AdventOfCode2020.
Day 7: Handy Haversacks is the hardest puzzle to solve using only Power BI until now. It took me a lot of time to find a viable and fast solution.
I have first tried to solve this using DAX PATH but had issues with the different levels of recursion. It was possible in the real data input for the same bag to be child of different bags in different levels. Only after a deep dive on using the List.
Time to check for some custom declarations forms. In the first part we will check the distinct number of questions anyone on each group answered “yes”, and on the second part the questions where everyone gave a positive response.
If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.
Part 1 The input for this problem is very similar to the passports we have seen on day 4.
This problem seems a lot of work at first but in reality it’s just binary number conversion to decimal.
Picking up the provided example:
FBFBBFF RLR If we replace the lower half [F, L] by 0, and the upper half [B, R] by 1 we get:
0101100 101 Doing the conversions:
\begin{equation} \begin{aligned} (0101100)_2 &= 0*2^6 + 1*2^5 + 0*2^4 + 1*2^3 + 1*2^2 + 0*2^1 + 0*2^0 \\\ &= 32 + 8 + 4 \\\ &= 44 \\\ \\\ (101)_2 &= 1*2^2 + 0*2^1 + 1*2^0 \\\ &= 4 + 1 \\\ &= 5 \end{aligned} \end{equation}
Day 4 is about passport fields validation. In part 1 we need to check if all the fields exist and in part 2 check if they are valid.
This seems like a job where the heavy lifting should be made by Power Query.
https://adventofcode.com/2020/day/4
If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.
Part 1 Following this strategy we load the data to our file and we find our first challenge.
Now we need to check for trees in paths. The interesting bit is that the path can grow ad infinitum to the right.
Check the details at https://adventofcode.com/2020/day/3
If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions.
Part 1 How do we avoid repeating the pattern? Looking at the provided sample we have:
..##.......
#...#...#..
.#....#..#.
..#.#...#.#
.#...##..#.
..#.##.....
.#.#.#....#
.#........#
#.##...#...
#...##....#
We are at an toboggan rental shop and we need to help the shopkeeper to validate a bunch of corrupted passwords.
(If you want to follow along please download AdventOfCode2020.pbit and check Day 1 post for instructions)
Part 1 In part one we need to check if a given character occurs between a certain lower and upper bound.
This kind of operation seems to be more suited to be done in Power
Query as we are going row by row checking for the validity of the rule.
This will be the first post of a series where I hope to be able to solve the problems from the Advent of Code using only Power BI.
This event, sadly, only came to my attention this year. It started in 2015 and it’s a lot of fun. In these posts I will talk about my thinking and strategy to try and solve these problems.
Without further ado let’s get started:
⚠ WARNING: The content of this post has been made deprecated due to the availability of the official Google Sheets connector and you should use that feature to import the data. ⚠ Check the official announcement on the link below: https://powerbi.microsoft.com/en-us/blog/power-bi-november-2021-feature-summary/#post-17912-_Toc87473946 This post is just for future reference In the beggining I’ve been tasked to find a way of exporting data from Google Spreadsheets for a client. I’ve started researching for an already working solution and got to the following links:
⚠ WARNING: The content of this post has been made deprecated due to the availability of composite models and you should use that awesome feature to import the data. ⚠ Check the official announcement on the link below: https://powerbi.microsoft.com/en-us/blog/directquery-for-power-bi-datasets-and-azure-analysis-services-preview/ This post is just for future reference While I was working with a client they were trying to import data from another Power BI dataset to their file and they got this error:
Emacs Major Modes for Power BI After many years of using Vim I finally understood the Emacs way. What I was trying to do all along was trying to make Vim behave exactly like Emacs.
To celebrate this I made some bad Elisp code and shared it on GitHub. I based my code on the Simple Emacs Major Mode for Syntax Coloring by Xah Lee.
Power Query Mode This mode gives you syntax coloring and code completion inside Emacs.
Do you really can diff Power BI files? While working with some customers a few times this question came up: “How can I diff my current file with a older one? I really want to compare my files, to know what changed from one to another.”
Many many people working with Power BI end up with a directory full of files looking like: “myPowerBIFileV1.pbix”, “myPowerbifilev2.pbix”… “myPowerbifilevN.pbix”. The scenario wull get worse if we have two or more people working in the same file, and until recently my answer was always the same: “You can’t”.
