Mike-Honey

// 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

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

Mike-Honey

// from: Power BI Community discussion: https://community.powerbi.com/t5/Desktop/Getting-SharePoint-List-items-with-full-history-version/m-p/776458/highlight/true#M374019
// note: consider posts below on use and trouble-shooting

// Function fnGetVersionHistoryFromSharePointList
let
    Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
        Source = Xml.Tables(Web.Contents(Text.Combine({
    VersionsRelevantSharePointLocation,
    "/_api/web/Lists/getbytitle('",
    VersionsRelevantSharePointListName ,

Mike-Honey

// 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

// 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})),

Mike-Honey

// 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},

Mike-Honey

# 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):