Skip to content

Instantly share code, notes, and snippets.

Avatar

Mike Honey Mike-Honey

View GitHub Profile
@Mike-Honey
Mike-Honey / openpyxl_theme_and_tint_to_rgb.py
Last active Oct 12, 2020
For python openpyxl, translates a cells theme and tint to an rgb color code.
View openpyxl_theme_and_tint_to_rgb.py
# found at: https://pastebin.com/B2nGEGX2, WRT https://stackoverflow.com/a/58443509/1787137
from colorsys import rgb_to_hls, hls_to_rgb
#https://bitbucket.org/openpyxl/openpyxl/issues/987/add-utility-functions-for-colors-to-help
RGBMAX = 0xff # Corresponds to 255
HLSMAX = 240 # MS excel's tint function expects that HLS is base 240. see:
# https://social.msdn.microsoft.com/Forums/en-US/e9d8c136-6d62-4098-9b1b-dac786149f43/excel-color-tint-algorithm-incorrect?forum=os_binaryfile#d3c2ac95-52e0-476b-86f1-e2a697f24969
def rgb_to_ms_hls(red, green=None, blue=None):
@Mike-Honey
Mike-Honey / fnRunningSum.M
Last active Apr 19, 2020
Power Query / M function to add a Running Sum or Running Count.
View fnRunningSum.M
// Power Query / M function to add a Running Sum or Running Count.
// Expects a numeric column named Input to exist in the table. To achieve a Running Count, set the value of Input to 1 on every row.
// Call by using the UI to "Group By" with an "All Rows" operation. Then edit the generated "Grouped Rows" step to change: "each _" to: "each fnAddRunningSum ( _ )"
// Then expand the All Rows table-type column to get the added column named "Running Sum"
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Input],{0},(cumulative,Input) => cumulative & {List.Last(cumulative) + Input})),
View Xero Invoice Line Items via Stitch Data and BigQuery.sql
-- Having extracted Xero data into BigQuery (via Stitch Data), extracting the Invoice Line details is still quite tricky.
-- On the invoices table, the invoice lines are stored as a nested JSON object: lineitems
-- The following query produces a row for each Invoice Line, and also unpacks Tracking fields
-- this line handles the easy single-field selections
SELECT invoiceid, invoicenumber, lineitems.lineamount, lineitems.description as linedescription
-- unpack the sub-nested lineitems.tracking nodes for each invoice, and return the option field for a specific name.
-- LIMIT 1 ensures we only return the first match. SQL will break if more than one result is returned.
, ( SELECT option from UNNEST ( lineitems.tracking ) WHERE name = 'Studio' LIMIT 1) as Studio
-- repeat the unpack for another tracking name
View PowerTrim.M
// Based on Ken Puls' blog post - http://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/
// Trims in the style of the Excel TRIM function - embedded whitespace is trimmed to a single space.
// This variation accepts null values in the text parameter, in which case it returns an empty string.
(optional text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then " " else char_to_trim,
text2 = if text = null then " " else text,
split = Text.Split(text2, char),
removeblanks = List.Select(split, each _ <> ""),
@Mike-Honey
Mike-Honey / ExpandAllRecords.M
Created Mar 22, 2016
ExpandAllRecords function for Power Query or Power BI - expands all record-type columns recursively
View ExpandAllRecords.M
// Based on Chris Webb's blog post - http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
let
//Define function taking two parameters - a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
@Mike-Honey
Mike-Honey / ExpandAll.M
Created Mar 22, 2016
ExpandAll function for Power Query or Power BI - expands all table-type columns recursively
View ExpandAll.M
// From Chris Webb's blog - http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
let
//Define function taking two parameters - a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},