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
Command Line Experiment

Experiments / Answers of 2022-02

File Selection CLI

Revisiting BASH

echo -e \\033[{0..100}mhiworld
delegating file path queries to fd
> grep -c '.*' -- $(fd -e ps1 -e txt -d 1)   # ext:ps1,txt depth: 1

# using long-names
> fd --extension ps1 --extension txt --max-depth 1
# 1] highlight matches in red
# 2] preserves all lines
> history | grep --perl-regex --ignore-case --color=always 'less|$'

# 3] add paging 
> history | grep --perl-regex --ignore-case --color=always 'less|$' | less --raw-control-chars

# 4] View a log 
> less someLog

# 5 start on the last line of a log
> less +G someLog

See more

Detect Valid Values for gh --json — then Fzf filter them

Ninmonkey.Console/Invoke-GHRepoList.ps1
$allProperties ??= _enumerateGhProperty
$selectedProps = $allProperties
| Out-Fzf -MultiSelect -Layout reverse -Height 100

Invoke-GhRepoList -prop $selectedProps

PowerApps: Visualize filter in Queries

Is there a way to add columns Programically to many queries?

https://i.stack.imgur.com/hcpw3.png

Labeled Summary of all queries

let
    Source = Record.RemoveFields(
        #sections[Section1],
        "AllQueries", MissingField.Error
    ),
    Summary = Record.ToTable( Source ),
    OnlyTables = Table.SelectRows(
        Summary, each Value.Is( [Value], Table.Type )
    ),
    #"Add Query Id" = Table.AddIndexColumn(OnlyTables, "Query Id", 0, 1, Int64.Type)
in
    #"Add Query Id"

VS

Code Syntax Highlighting

when $null + 3 + 3 = 12

TM tokens accepts colors in the format #RRGGBB or #RRGGBBAA, you can set alpha to 0%
{ "editor.tokenColorCustomizations": {
    "textMateRules": [
    {
      "name": "test1",
        "scope": "keyword.operator.assignment.powershell",
        "settings": {
        "foreground": "#be85c5",
        "foreground": "#be85c500",
        "fontStyle": "underline"
        }
    }
    ],
}}

Random Power Query

Power Query Sugar for selecting distinct filters. If the condition is not true, then it throws an error. Name = "Orders" works because it results in a distinct value from the column Name It’s valid even though the final query is many records. It’s the “distinctness” of the filter that is required to be true

Source{ [Name = "Orders", Signature = "table"] }

Random Powershell

Random VS Code

Random CSS / Web

Regular Expression Engine Comparison.md

CSS Column Selectors to modify an existing table

Refactoring Others Code

https://gist.github.com/ninmonkey/8eb3805012660fc3f0fce86f137fb940

Bash Examples

# session

## counting
| wc --lines
| wc --bytes

# hide long results
| tail -n 10
| head -n 10

| tail --lines=100
| tail --bytes=2MB

# or instead don't print to console
# allow colors, but no other ANSI codes
| tail -R

# file listings safer to use
# `ls` output is bad / breakable
find . -iname 'foo*'
find . -iname '*.py'


# as 1 call ?
find . -iname '*.py' -exec stat {} \;
# as many   ?
find . -iname '*.py' -exec stat {} +


# require pattern
| grep -i 'required pattern'

# invert matches
| grep -iv 'not pattern'

# or xargs and with args that have whitespace
# and/or exec

# output to file (truncate)
foo > bar.log

# append to file
foo >> bar.log

# hide errors
VERIFY
> someCommand &2>/dev/null

history | grep 'find'

# view in pager, or just a few results
# too big of file
grep 'error' apache.log | less +G
grep 'error' apache.log | tail | less
grep 'error' apache.log > apache_errors.log

tail apache.log --lines=100 | less +G
tail apache.log --lines=100 > apache_mini.log
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
Cheatsheet Command Line Excel Power BI Power Query PowerShell

Big List of Cheat sheets and References

Web

XPath

OData

HTTP

Other: Web

Encoding

Unicode.org

Emoji

Table of Contents: Emoji Charts Table

Apps

VS Code

Firefox

Windows Terminal

Excel

Other: Apps

  • .

git

Misc

Programming and Languages

PowerShell

gist/vexx32/PowershellLoopBehavior.md

Power Query

Dotnet / .Net

Linux

Bash

Environment Vars

Categories
Command Line

Improving ‘diff’ readability on Windows | Tip

The output of diff -q path1 path2 is pretty verbose. This function

  • Converts full paths to relative
  • Differences are red.

Missing ‘diff’ ?

If git is installed, you may need to update your %PATH% environment variable.

# in your profile
$Env:Path = "$Env:ProgramFiles\Git\usr\bin", $Env:Path -join ';'

Stand-Alone function Compare-Directory

This is an isolated version of <a href="https://github.com/ninmonkey/Ninmonkey.Console">Ninmonkey.Console: Compare-Directory</a> . I removed all dependencies except coloring is provided by the module PoshCode/Pansies.

function Invoke-NativeCommand {
    <#
    .synopsis
        wrapper to both call 'Get-NativeCommand' and invoke an argument list
    .example
        PS> # Use the first 'python' in path:
        Invoke-NativeCommand 'python' -Args '--version'
    #>

    param(
        # command name: 'python' 'ping.exe', extension is optional
        [Parameter(Mandatory, Position = 0)]
        [string]$CommandName,

        # Force error if multiple  binaries are found
        [Parameter()][switch]$OneOrNone,

        # native command argument list
        [Alias('Args')]
        [Parameter(Position = 1)]
        [string[]]$ArgumentList
    )

    $binCommand = Get-NativeCommand $CommandName -OneOrNone:$OneOrNone -ea Stop
    & $binCommand @ArgumentList
}

function Compare-Directory {
    <#
    .SYNOPSIS
    Compare Two directories using 'diff'
    .EXAMPLE
    Compare-Directory 'c:\foo' 'c:\bar\bat'
    #>
    [Alias('DiffDir')]
    param(
        # Path1
        [Parameter(Mandatory, Position = 0)]
        [string]$Path1,

        # Path2
        [Parameter(Mandatory, Position = 1)]
        [string]$Path2,

        # Output original raw text?
        [Parameter()][switch]$OutputRaw
    )

    $Base1 = $Path1 | Get-Item -ea Stop
    $Base2 = $Path2 | Get-Item -ea Stop
    $Label1 = $Base1 | Split-Path -Leaf | New-Text -fg 'green'
    $Label2 = $Base2 | Split-Path -Leaf | New-Text -fg 'yellow'

    "Comparing:
        Path: $Path1
        Path: $Path2
    " | Write-Information

    $stdout = Invoke-NativeCommand 'diff' -args @(
        '-q'
        $Base1
        $Base2
    )

    $outColor = $stdout
    $outColor = $outColor -replace [regex]::Escape($path1), $Label1
    $outColor = $outColor -replace [regex]::Escape($path2), $Label2
    $outColor = $outColor -replace 'Only in', (New-Text 'Only In' -fg 'red')
    $outColor = $outColor -replace 'Differ', (New-Text 'Differ' -fg 'red')

    if ($OutputRaw) {
        h1 'Raw' | Write-Information
        $stdout
        return
    }

    $outColor
}

function Get-NativeCommand {
    <#
    .synopsis
        wrapper that returns Get-Item on a native command
    .example
        # if you want an error when multiple options are found
        PS> Get-NativeCommand python -OneOrNone
    .example
        # note: this is important, $cmdArgs to be an array not scalar for '@' usage
        $binPy = Get-NativeCommand python
        $cmdArgs = @('--version')
        & $binPy @cmdArgs
    .example
    #>
    [cmdletbinding()]
    param(
        # Name of Native .exe Application
        [Parameter(Mandatory, Position = 0, ValueFromPipeline)]
        [object]$CommandName,

        # One or None: Raise errors when there are more than one match
        [Parameter()][switch]$OneOrNone
    )

    process {
        try {
            $query = Get-Command -Name $CommandName -All -CommandType Application -ea Stop
            | Sort-Object Name

        } catch [CommandNotFoundException] {
            Write-Error "ZeroResults: '$CommandName'"
            return
        }

        if ($OneOrNone -and $query.Count -gt 1) {
            $query | Format-Table -Wrap -AutoSize -Property Name, Version, Source
            Write-Error "OneOrNone: Multiple results for '$CommandName'"
            return
        }

        if ($query.Count -gt 1) {
            $query = $query | Select-Object -First 1
        }

        Write-Debug "Using Item: $($query.Source)"
        $query
    }
}
Categories
Command Line PowerShell Quick Tips References And Cheat Sheets

Easy way to cache results on the Command Line | Power Shell Tip

Sometimes you’ll need to run a command with the same input with different logic.
This can be a hassle using a slow command like Get-ADUser or Get-ChildItem on a lot of files like ~ (Home) with -Depth / -Recurse

ls ~ -Depth 4 | Format-Table Name

PowerShell 7.0+

Powershell 7 added the Ternary Operator, and several operators for handling $null values.

All of these examples will only run Get-ChildItem the first time. Any future calls are cached.

Null-Coalesce ??= Assignment Operator

This is my favorite on the Command line. The RHS (Right Hand Side) skips evaluation if the left side is not $null

$AllFiles ??= ls ~ -Depth 4

Using the Null-Coalesce ?? Operator

$AllFiles = $AllFiles ?? ( ls ~ -Depth 4  )

Ternary Operator ? whenTrue : WhenFalse

$allFiles = $allFiles ? $allFiles : ( ls ~ -Depth 4 )

Windows PowerShell and Powershell < 7

Windows Powershell can achieve the same effect with an if statement

if(! $AllFiles) { $AllFiles = ls ~ -Depth 4 }
Categories
Command Line Getting-Started PowerShell Pwsh7+ Quick Tips What's New

PowerShell : Prefixing lines with the Pipe operator |

There’s a lot of ways to use line continuations in Windows Powershell without backticks . Powershell added a new one, the | pipe operator. It’s cleaner to read, and makes it easier to insert, delete, or toggle comments on the console.

Now you can write:

# Powershell
ls | sort Length
| Select -First 10
| ft Name, Length

Instead of piping on line endings

# Windows Powershell
ls | sort Length |
Select -First 10 |
ft Name, Length

# or
ls | sort Length | Select -First 10 | ft Name, Length