A #dataOp ramblings

Adventures in Business Intelligence

Advent of Code 2020 - Day 5

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: .codeaa { display: inline-block; width: 106px; text-align: center; } FBFBBFF RLR If we replace the lower half [F, L] by 0, and the upper half [B, R] by 1 we get: .codeaa { display: inline-block; width: 20%; text-align: center; } 0101100 101 Doing the conversions:

Advent of Code 2020 - Day 4

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.

Advent of Code 2020 - Day 3

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: codeaa { position: relative; display: inline-block; margin: 0; background: #10101a; padding: 0; font-weight: bold; color: #ffffff; font-size: x-large; font-family: "

Advent of Code 2020 - Day 2

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.

Advent of Code 2020 - Day 1

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:

Google Spreadsheets Custom Connector

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: https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/ https://www.skolenipowerbi.cz/l/google-drive-connector-kompletni-pruvodce/ https://github.com/fluf1024/PQGoogleSpreadsheet A lot of the work and heavy lifting has been done by them and the rest of this post will be about how to do it today, as some of their content is outdated and was not working when scheduling refreshes via the Power BI Enterprise Gateway.

Import data from another Power BI dataset

⚠ 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

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.

Diffing Power BI files

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”.

EzApi - Define flat file columns from connection string

So what’s new? Thanks to the contribution via GitHub from Hadi Fadl there’s a new method available for the flat file connection manager that adds the existing columns in the source file. This is very handy because in the past you would have to define each and every column that existed in the flat file. I’ve updated the nuget to version 0.8.93. Please give me your feedback of anything you might think should be improved.