Skip to content

Instantly share code, notes, and snippets.

View Hugoberry's full-sized avatar

Igor Cotruta Hugoberry

126 followers · 88 following
View GitHub Profile
@Hugoberry
Hugoberry / MS-XLDM.md
Created March 24, 2023 13:59
Markdown version of [MS-XLDM](https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xldm/8c62e8ce-f605-488d-81e9-4ecdb7686a52) spec

[MS-XLDM]:

Spreadsheet Data Model File Format

Intellectual Property Rights Notice for Open Specifications Documentation

  • Technical Documentation. Microsoft publishes Open Specifications documentation ("this documentation") for protocols, file formats, data portability, computer languages, and standards support.
@Hugoberry
Hugoberry / ABF.cs
Created March 22, 2023 22:56
Data structures from Analysis Services Backup file format
using System.Xml.Serialization;
[Serializable]
[XmlRoot("BackupLog")]
public class BackupLogHeader{
[XmlElement("BackupRestoreSyncVersion")]
public int BackupRestoreSyncVersion { get; set; }
[XmlElement("Fault")]
public bool Fault { get; set; }
@Hugoberry
Hugoberry / abf.ksy
Created March 7, 2023 12:05
The Kaitai definition of ABF SQL Server Analysis Services backup file. The structure defined in [MS-XLDM] open spec document.
meta:
id: abf
endian: le
title: Spreadsheet Data Model File
seq:
- id: bom
type: bom_t
- id: stream_signature
type: stream_signature_t
@Hugoberry
Hugoberry / ExampleOutput.csv
Created March 6, 2023 16:35
DAX measure that formats a measure `[Quantity]` into a roman numeral
Brand Quantity Roman Quantity
A. Datum 6897 V̅I̅DCCCXCVII
Adventure Works 10901 X̅CMI
Contoso 53151 L̅MMMCLI
Fabrikam 10973 X̅CMLXXIII
Litware 10130 X̅CXXX
Northwind Traders 2311 MMCCCXI
Proseware 9322 I̅X̅CCCXXII
Southridge Video 14866 X̅I̅V̅DCCCLXVI
Tailspin Toys 10590 X̅DXC
@Hugoberry
Hugoberry / BetterRevenue.dax
Last active March 3, 2023 18:00
A DAX measure definition that formats the Revenue numbers in a wordly way. It spells out numbers 1,812,343,434,567,812 like to `one quadrillion eight hundred and twelve trillion three hundred and forty-three billion four hundred and thirty-five million five hundred and sixty-eight thousand eight hundred and twelve`
Better Revenue_ =
VAR scale =
LEN ( [Revenue] )
RETURN
CONCATENATEX (
ADDCOLUMNS (
ADDCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 0, scale / 3 ),
"Factors",
@Hugoberry
Hugoberry / powerbi-lineage.jq
Last active November 4, 2022 15:59
A jq parser for the internal PowerBI file *.pbix/Report/Layout. It surfaces the columns from the datamodel used in the visualizations.
[.sections[] | {
page: .displayName,
visuals: [ .visualContainers[].config |
fromjson |
.singleVisual |
select(has("projections"))|
{
type:.visualType,
projections: .projections |
{(keys[0]):[.[keys[0]][] |
@Hugoberry
Hugoberry / decompile.pq
Created June 22, 2022 10:00
Power Query attempt at decompiling a function call
t29
skippedInput = List.Skip(list,offset)
t28 = if (count = null)
then skippedInput
else List.FirstN(skippedInput,count)
@Hugoberry
Hugoberry / list-cheat-sheet-unicode.md
Last active April 16, 2022 21:38
PowerQuery List functions with unicode examples

List functions

Quick visual overview:

List.Zip({{"■","●"},{"▲","♦"}}) ⇒ {{"■","▲"},{"●","♦"}}
List.Reverse({"■","●","▲"}) ⇒ {"▲","●","■"}
List.Sort({"■","●","■"}) ⇒ {"■","■","●"}
List.Select({"■","●","■"}, each _="■")  ⇒ {"■","■"}
List.Transform({"■","●","▲"}, each "■") ⇒ {"■","■","■"}
List.PositionOf({"■","●","▲"},"▲") ⇒ 2
List.PositionOfAny({"■","●","▲"},{"♦","●"}) ⇒ 1
@Hugoberry
Hugoberry / list-cheat-sheet.md
Last active April 16, 2022 21:39
PowerQuery list functions cheat sheet

List functions

Addition

List.Sum({1, 2, 3}) ⇒ 6

Averages

List.Average({3, 4, 6}) ⇒ 4.333333333333333
List.Mode({"A", 1, 2, 3, 3, 4, 5}) ⇒ 3
List.Modes({"A", 1, 2, 3, 3, 4, 5, 5}) ⇒ {3,5}
@Hugoberry
Hugoberry / XML2DDL.XSLT
Created May 18, 2021 09:41
XSLT for transforming an XML doc into DDl statements for schema population
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsl:output method="text" omit-xml-declaration="yes" />
<xsl:template match="/">
<xsl:text>
-- ---------------------------------------------------------------
-- DDL generated from a .Net DataSet XSD via XSLT