Categories
Experiment Getting-Started Power BI Uncategorized

Inspecting Function “subtypes” in Power Query

PowerQuery has metadata that you don’t normally see. For example, take the function Table.FromRecords

Create a new blank query, and set the value to a function’s name. When you reference a function without arguments or parenthesis, it displays documentation. ( It’s mostly the same as the online docs )

Where does this come from? A lot of it is generated by metadata on the function’s type itself.

Let’s start drilling down. Using the function Value.Type you can view the type’s definition

It doesn’t seem very useful at first. The data we want is from the metadata, the Ascribed types.

Metadata of the function type

The first level describes the function using specific field names that the UI looks for . The UI renders some Html from the [Documentation.LongDescription] value.

You can drill down to [Documentation.Examples] , which is a list of records.

Viewing Documenation.Examples

FunctionParameters Types

There can be data defined for the arguments themselves. Parameters that are type any may have more information in their metadata.

Parameters’s may have metadata
Sometimes the metadata, of a parameter’s metadata — has more metadata!

Your First Function types

How do you document your own functions? Here’s a medium example. The function declared further below starts with this definition:

Text.ReplacePartialMatches_impl = (
        source as text, mapping as table
    ) as text => ...

To create the type, you start your function type almost the exact same as the definition. Then wrap it inside a type function

Text.ReplacePartialMatches.Type = type function(
        source as text,
        mapping as table
    ) as text meta [ .. ]

Next you start adding records to the final type’s metadata. If you haven’t seen the meta operator, check out Ben’s series:

The part that comes after meta operator is a regular record

let
    Text.ReplacePartialMatches = Value.ReplaceType( Text.ReplacePartialMatches_impl, Text.ReplacePartialMatches.Type ),
    Text.ReplacePartialMatches.Type = type function(
        source as text,
        mapping as Table.Type
    ) as text meta [
        Documentation.Name = "Text.ReplacePartialMatches",
        Documentation.LongDescription = Text.Combine({
            "Test strings for partial text matches. Replace the entire cell/value with the new replacement text.",
            "",
            "Mapping table requires two columns: <code>[Partial]</code> and <code>[New Value]</code> "

        }, "<br>")
    ],

    Text.ReplacePartialMatches_impl = ( source as text, mapping as table ) as text =>
        // todo: performance, exit early on first replacement
        let
            mappingList = Table.ToRecords( mapping ),
            result = List.Accumulate(
                mappingList,
                source,
                (state, cur) =>
                    if Text.Contains( state, cur[Partial], Comparer.OrdinalIgnoreCase )
                    then cur[New Value] else state
            )
        in result

in
    Text.ReplacePartialMatches

Query For the Screenshot

let
    Custom1 = Value.Type( Value.Type( Table.FromRecords ) ),
    fn_typeMeta = Value.Metadata( Value.Type( Table.FromRecords ) ),
    fn_typeMeta_example = ( Value.Metadata( Value.Type( Table.FromRecords ) )[Documentation.Examples]){1},
    t_fnParams = Type.FunctionParameters( Value.Type( Table.FromRecords ) ),
    fn_metaType = Value.Metadata( Type.FunctionParameters( Value.Type( Table.FromRecords ) ) ),
    type_ofFuncType = Value.Type( Type.FunctionParameters( Value.Type( Table.FromRecords ) ) [missingField] ),
    type_param_ofFuncType = Value.Metadata( Type.FunctionParameters( Value.Type( Table.FromRecords ) )[missingField] ),
    required_ofFuncType = Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ,
    type_ofRequiredType = Value.Type( Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ),
    type_ofType_ofRequiredType = Value.Metadata( Value.Type( Type.FunctionRequiredParameters( Value.Type( Table.FromRecords ) ) ) )
    
in
    type_ofType_ofRequiredType

See More

Functions

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
Experiment

Animations of 2022-07

Single File With Automatic Inline Testing

Categories
App Config Getting-Started PowerShell VS Code

Viewing Default Settings in VS Code

Where is the default settings.json or keybindings.json ? There isn’t one, because it’s dynamically generated.

When you run run the ‘default settings’ command, it builds a new one — this means it’s always up to date. It includes settings from all enabled addons.

Command Palette: The Only Hotkey You Need to Remember.

Can’t remember what key formats without saving? No problem, f1 -> forsave and it will come up.

Go to Symbol: The Secret to Navigating giant JSON files

It’s better than using a regular search ctrl+f . If you searched for ‘fontsize’ not only will you get every setting, but, lots of comments as well. When there’s duplicate settings, the bottom one has priority. If you like customizing, you may end up with duplicated keys. This instantly lets shows you which is the final one. Even if they are 3000 lines apart.

Control+Space: The 2nd Best Hotkey

As you’re editing, ctrl+space will fuzzy search every possible setting. Hit it a 2nd time to toggle the documentation.

Searching Keybindings by Name or Command by name

Suggested PowerShell Config

I tried keeping it short, I recommend checking these settings for PowerShell.
If you want to control autocomplete or suggestions , this config has notes on some properties to check out.

{
    // this file is almost the same as 
    //<https://github.com/ninmonkey/dotfiles_git/blob/614fc06cd8b989e8438cba6cae648605fae2491a/vscode/User/nin10/Code/minimum-config/powershell.settings.json>

    "workbench.settings.editor": "json", // good for editing, [ctrl+,]
    // will by default open your global settings as JSON instead of UI

    // improve code quality
    "powershell.codeFormatting.autoCorrectAliases": true,
    "powershell.codeFormatting.useConstantStrings": true,
    "powershell.codeFormatting.useCorrectCasing": true,

    // I have this enabled for most languages
    "editor.formatOnSave": true,

    // some people are pretty polarized on which style to use, 
    // So I have both styles and variants to try
    "editor.wordSeparators": "`~!@#%^&*()=+[{]}\\|;:'\",.<>/?", // combine $ and -
    "editor.wordSeparators": "`~!@#%^&*()-=+[{]}\\|;:'\",.<>/?", // causes splat-expression etc to break
    "editor.wordSeparators": "`~!@#$%^&*()-=+[{]}\\|;:'\",.<>/?", // break on $ and -
    "editor.wordSeparators": "`~!@#%^&*()=+[{]}\\|;:'\",.<>/?", // combine $ and -
    "editor.wordSeparators": "`~!@#%^&*()-=+[{]}\\|;:'\",.<>/?",

    // If you don't like snippets, you can disable them for as specific language, leaving the others
    // the blog isn't rendering the next line, it should say
    // "[power shell]" as the key If you don't like snippets, you can disable them for as specific language, leaving the others
    "[powershell]": {
        "editor.semanticHighlighting.enabled": false,
        "editor.snippetSuggestions": "bottom",
        "editor.snippetSuggestions": "none",
        "files.encoding": "utf8bom",
        "files.trimTrailingWhitespace": true,
    },

    /*
        Adds autocompletion and validation to any .Format.ps1xml and .Types.ps1xml files.
        It uses the addon: 'redhat.vscode-xml'
    */
    "editor.suggest.preview": true, // interesting but can be jarring   
    ],

    "powershell.integratedConsole.suppressStartupBanner": true,
    "powershell.powerShellDefaultVersion": "PowerShell (x64)",

    "powershell.promptToUpdatePowerShell": false,
    // Specifies the path to a PowerShell Script Analyzer settings file. To override the default settings for all projects, enter an absolute path, or enter a path relative to your workspace.
    "powershell.scriptAnalysis.settingsPath": "C:/Users/monkey/Documents/2021/dotfiles_git/powershell/PSScriptAnalyzerSettings.psd1",
    // "powershell.scriptAnalysis.settingsPath

     // Autocomplete and a schema/validation for
     // powershell's  "types.ps1xml" and "format.ps1xml" files
     "xml.fileAssociations": [
        {
            "systemId": "https://raw.githubusercontent.com/PowerShell/PowerShell/master/src/Schemas/Format.xsd",
            "pattern": "**/*.Format.ps1xml"
        },
        {
            "systemId": "https://raw.githubusercontent.com/PowerShell/PowerShell/master/src/Schemas/Types.xsd",
            "pattern": "**/*.Types.ps1xml"
        }

}

Error Lens

"errorLens.followCursor": "closestProblem",
"errorLens.followCursorMore": 2,

One of the extensions from Justin Grote’s addon pack is the error lens. I like it, after reducing the visual noise. You can

See More:

For customizing themes, check out these settings. There’s different sections depending on if semantic color is enabled

"editor.semanticTokenColorCustomizations": { ... },
"editor.tokenColorCustomizations": { ... },

https://cdn.discordapp.com/attachments/447579065877266454/969757374216802304/unknown.png
Categories
Experiment PowerShell Pwsh7+

Experiments of 2022-04

FdFind, Ansi Colors with Group-Object

# [3] main + UX for long exxtensions + Horizontal rules
fd --color=always --changed-within=10hours
| group { 
    $strExt = $_ | StripAnsi | gi | % Extension
    # QOL: don't let massive names break the table
    if($strExt.Length -gt 10) { $strExt.Substring(0, 10) } else {$strExt}
}
| %{ $_ ; hr }
| ft -AutoSize
# [1] Minimum required
fd --color=always --changed-within=10hours
| group { $_ | StripAnsi | gi | % Extension }
| ft -AutoSize
# [2] UX: Don't let super long extensions break break columns
fd --color=always --changed-within=10hours
| group {
    $strExt = $_ | StripAnsi | gi | % Extension
    if($strExt.Length -gt 10) { $strExt.Substring(0, 10) } else {$strExt}
}
| ft -AutoSize

Using wt‘s Parameters

https://github.com/Ninmonkey.Console/WtThemeTest
PS> ZD-Invoke-WtThemeTest -Random
PS> wt -w theme-test new-tab --title "Tango Light" --profile 'pwsh_nop' --colorScheme "Tango Dark"
PS> wt -w theme-test new-tab --title "Tango Light" --profile 'pwsh_nop' --colorScheme "BirdsOfParadise"

# or
ZD-Invoke-WtThemeTest -Random

Nested Formatting in Powershell

Using module Pansies
What not to do πŸ™‚

CLI bat to preview results

# For every file fd finds, print the first 15 lines
PS> fd --exec-batch bat --line-range=:15 --paging=always

# forcing paging /on/off
PS> fd --exec-batch bat --line-range=:15 --paging=never

Regex Lazy vs Greedy Expressions

Using CSS Selectors

selector 'div.premium-box span.btn.btn-info'

Autocomplete changes based on the first Argument

Parsing Stdout Whitespace

Grouping On Errors

Type Resolution Is Scoped

PwshπŸ’> # test whether it's resolved by coerce to [type]
    'catman' -as 'type' -is 'type'
    'batman' -as 'type' -is 'type'
True
False
PwshπŸ’> # test whether it's resolved by coerce to [type]
    'catman' -as 'type' -is 'type'
    'batman' -as 'type' -is 'type'
True
False


# after 

PwshπŸ’> @(
    # Declaring a new type in inside a [ScriptBlock]
    & {
        class batman { [string]$Name }
        [batman]
    }

    # verses dotsourcing a type into the current scope
    . {
       class catman { [string]$Name }
       [catman]
    }) | ft -AutoSize

   Namespace: <4cf9efd5>

Access Modifiers Name   BaseType
------ --------- ----   --------
public class     batman object

   Namespace: <f2200555>

Access Modifiers Name   BaseType
------ --------- ----   --------
public class     catman object
Categories
App Config Cheatsheet Getting-Started Power Apps Power BI References And Cheat Sheets

How Do I Get Started with Power Apps? With Cheat Sheets

– Moving your cursor around will change the tooltips
– It shows the data type, and the function

The Documentation Is Great

Sample Data Without a Data Source – Declaring Inline Tables

Step1: Choose Insert -> Button.

Step2: Set the button’s OnSelect property to this.

Step3: Alt left click the button, and it’ll create the table. You’re still in edit mode.

ClearCollect(
    Customers,
    Table(
        { Name: "Fred Garcia", Company: "Northwind Traders" },
        { Name: "Cole Miller", Company: "Contoso" },
        { Name: "Glenda Johnson", Company: "Contoso" },
        { Name: "Mike Collins", Company: "Adventure Works"},
        { Name: "Colleen Jones", Company: "Adventure Works"} 
    )
)

The “I know how to program, Give me the good stuff!” Section

Here’s the main links I recommend

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
Power BI What's New

Run DAX in Your Browser: SQL-BI.com has a DAX Web App | What’s New

The announcement: sqlbi.com/introducing-dax-do

Examples on Dax.Guide have a “try it” button to launch the editor