Categories
Adventures In Power Query Experiment Formatting Uncategorized What Not To Do

What Not To Do: When Letting Your Code Breathe Goes Bad

White Space before a function call is allowed

Whitespace between function calls and the name are allowed. Including newlines
These are equivalent statements:

= DoStuff( args )

// and
= DoStuff

                                ( 
args )

Record lookups also allow whitespace.
This is totally valid syntax wise. Not necessarily morally though.

let
    Func = () => [ 
        user = [ Name = "bob" ] 
    ]
in 
    Func  


(

              )
                            
                                             [    
     user  



]

Misleading Comments inside Lists, Records, and Function Calls are allowed

Comments do not affect parsing or execution.
Without syntax highlighting it looks like 4 is the final item in the list

let
    num = List.Count( { 0
                     ..
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
                
    without syntax highlighting, it looks like the func call ended
    
    later, secretly do more
*/                                                  ,99 } )

in num

Now it’s slightly easier

let
    num = List.Count( { 0
                     ..
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
                
    without syntax highlighting, it looks like the func call ended
    
    later, secretly do more
*/                                                  ,99 } )

in num

The list operator .. allows a lot of expressions

You can use inline comments, resuming the list expression later

let
    num = List.Count( { 0
..
           5 } & { 3, 4 /*
    now do more */ } )
in num

You don’t have to wrap the try-catch expressions in parenthesis.

let l = { 
          try "foo" + 99 catch (e) => 3           ..
          try File.Contents( "invalid" ) catch () => 7 } 
in 
    l = { 3..7 } // is true

This version gave an interesting error. I thought perhaps try-catch expressions doesn’t work for inline list indices ?

let g = { 
          try 10 catch (e) => 3           ..
          try 27 / 0 catch () => 53 }

in g
Expression.Error: The number is out of range of a 32 bit integer value.
Details:
    Infinity

But then realized division by 0 in power query does not throw an error record. It has the type number.

Categories
Command Line Experiment Formatting

Experiments of 2022-09

Power BI / Power Query

Things to note

  • options is an record, when used this way it’s similar to Python’s kwargs
  • Merging two records with update the existing fields, adding new fields, if they do not yet exist
  • (in PowerQuery) the order of steps don’t change the final result (order of execution is the same) That’s why defaults after config works
  • Sometimes readability improves when placing large “blocks” like values_list out of order so the logic is on top
  • I replaced values *before* converting them to text so you have more control (before coercion )
Text.JoinSpecialValues_impl = (source as list, optional options as nullable record) as text =>
    let
            config = Record.Combine({defaults, options ?? []}),
            defaults = [
                Separator = "|",
                UseSpecialSymbols = true
            ],
            text_list = List.Transform( values_list, Text.From),
            joined_string =  Text.Combine( text_list, config[Separator] ),
            values_list  = if not config[UseSpecialSymbols] then source else
                List.ReplaceMatchingItems( source,
                    {
                        // replace true null, and true empty strings (vs whitespace)
                        { null, "␀"},
                        { "#(cr,lf)", "#(240d)␤" }, // is #(2424)" }
                        { "#(lf)", "␤" }, // is #(2424)" }
                        {"", "␠"}
                    } ) 

        in
            joined_string,
Power Query and Report.pbix (permalink) at github://ninmonkey

Powershell

Goto Everything

Goto /c/foo/bar

# go back
> Goto -Back
> Goto '-'    # normal cd history works too
> Goto '+'

# Goto the world
$Profile            | goto  # go to string's path
Get-Item $PROFILE   | goto  # cd to the FileItem's path
gcm EditFunc        | goto  # jump to function declaration
gmo NameIt          | Goto  # go to module's folder
[CompletionResult]  | goto  # to docs for
# <https://docs.microsoft.com/en-us/dotnet/api/System.Management.Automation.CompletionResult>

# Open git repos in browser
goto git microsoft/powerquery-parser | goto
goto git@github.com:microsoft/powerquery-parser.git | goto 

# goto the newest log
Get-ChildItem 'c:\root\manyLogs' -Recurse
| Sort LastWriteTime -desc -top 1 | goto

Pwsh Cli

Display all parameters
My favorite hotkey, ctrl+spacebar

s

Breaking Formatting

2, 40, 100, 200, 400, <#700, 2000,#> 20000 | %{ $i=$_;
  0..10 | %{ $j = $_;
    0..3000 | Get-Random -Count 300 | %{ $k = $_
      [pscustomobject]@{ DisplayString = [string]$_ }} | fw -Column $i }
        "Was: $i"
        sleep -sec 0.7 }

Is it a bug, or, is the extra y-axis padding working as intended in a situation where intended is not intended ( ie: column count orders of magnitude larger than the terminal’s column count )

Excel functions: What’s New

https://insider.office.com/en-us/blog/text-and-array-manipulation-functions-in-excel

  • [ ] new array ops, make a query like
  • = a1: e1 , except new functions could dynamically change the selection based on variable

Categories
Experiment Formatting

Experiments of 2022-08

Query to Summarize All Queries

Summarize.Query.pq

Summarize ⁞ Queries ┐main_query.png

Using Inline Images and SVG in a Power BI Table

With the column set to Image Url, you’re able to

  • use an external image like https://www.fakeurl.com/image.png
  • or output a svg image programmatically, by placing logic in a measure
  • or embedded a raw a .png image into the model/report itself
    • First encode the image Base64
    • Save that text in a table
    • Finally create a measure that prefixes the text with
[Inline Png ] := 
      "data:image/png;base64, " & SelectedValue( [ColumnWithText] )

Recent Discord Api

/*
note: make sure your GUIDs are strings, why?
because javascript does not have an integer type, so it has to squeeze
inside a floating point, see: <https://discord.com/developers/docs/reference#snowflakes>
*/
const cfg = {
    "GuildId": "180528040881815552",
    "Channel": "490008213056389120",
}

const apiUri = {
    "prefix": "https://discord.com/api/v9/",
    "activeThreads": `guilds/${ cfg.GuildId }/threads/active`, // bot only endpoint
    "channelMessage50": `channels/${ cfg.Channel }/messages?limit=50`
}

// apiUri.curUri = apiUri.channels
apiUri.curUri = apiUri["channelMessage50"]

console.log(`Cur uri: "${ apiUri.curUri }"`)
curUri = `${ apiUri.prefix }${ apiUri.curUri }`

await fetch(
    curUri, lastOpt
).then((r) => r.json()
).then((x) => console.log(JSON.stringify(x)))
  • run Discord in the browser
  • open web dev console
  • take an existing request -> copy as Fetch() or curl
  • saved those headers as lastOpt
Categories
Command Line Experiment Formatting Power BI Power Query VS Code

Experiments of 2021-10

Pwsh: Fuzzy Select Colors With Preview

PS> Get-ChildItem fg: | Where Name -match ‘red’
  • queries the color provider with partial matches
  • pipes to Out-Fzf or fzf for the preview and selection

Bitwise operators

Generated using a Pwsh script

ShouldProcess formatting

More Fzf

PSReadline: Auto-expand Aliases in the CLI

  • alt+shift+( surrounds selection, or the entire command in parenthesis
  • alt+shift+% runs Invoke-Formatter to format the code, including alias replacement

Pester5 ForEach | `code` snippet

Using Notebooks: for Github issue queries

Created a list of queries for the release of https://vscode.dev
ninmonkey/VS Code on the Web – Cheat Sheet – Custom urls.md

Power Query Web.Contents wrapper for Web API / REST calls

  • WebRequest_Simple.pq
  • enables ManualStatusHandling for better errors ( This is important for any REST APIs)
  • detects whether it’s Json or html, and returns a json record if existing
  • Always displays the response as plain-text as response_text
  • Lets you inspect request info, like headers used.

Power Query: Import from an external file

Experimenting with console formatting

Select a ton of properties, saving names as an array

Hashtables: Command line formatting

DAX: Syntax highlight bracket pairs



    "[dax-language]": {
        "editor.bracketPairColorization.enabled": true, //colorize the pairs
        "editor.guides.bracketPairs": true, // colorize vertical lines
        "editor.matchBrackets": "always",
        "editor.minimap.enabled": false,
        "editor.lineNumbers": "off",
    },
},

Pwsh: Prompt that summarizes recent errors

Pwsh: Colorized Directory Listing

  • Using terminal wt, Pwsh as the shell, and module: pansies for color
  • The current directory is a gradient, the boldest part is the most important part of the path.
  • cd-ing to a directory will summarize the new directory, without “spamming” the user when there’s a lot of items
    • Folders first, sorted by most recently modified
    • Files second, sorted most recently
    • Icon shows filetype

DAX: Conditionally Toggle Button using Measure

WordPress Image scaling

link to 1:1 image
link to 1:1 image
Categories
Formatting PowerShell Snippets

Reusable Calculated Properties in PowerShell

The function Label is from the module: github.com/Ninmonkey.Console

$basePath = 'C:\Program Files'
$calcProp = @{}
$calcProp.FileSize = @{
    n         = 'Size'
    e         = { $_.Length | Format-FileSize }
    Alignment = 'right'
}
# relative path defaults to relative your current location, so I override it
$calcProp.RelativePath = @{
    n = 'RelativePath'
    e = {
        Push-Location $baseBath
        $_.FullName | Resolve-Path -Relative
        Pop-Location
    }
}
$calcProp.ColorizedName = @{
    n = 'ColorName'
    e = {
        $Color = ($_ | Test-IsDirectory) ? 'blue' : 'green'
        Label $_.Name -fg $Color -Separator ''
    }
}

$DefaultPropList = 'Name', $calcProp.ColorizedName, $calcProp.RelativePath

Label 'basePath' $basePath

$files = Get-ChildItem $basePath -Depth 2
$files | Format-Table Name, Length, $calcProp.FileSize, $calcProp.RelativePath

$files | Format-Table  $calcProp.FileSize, $calcProp.ColorizedName

# Using pre-declared property lists, which may include scriptblocks like $calcProp.FileSize
$files | Format-Table -Property $DefaultPropList