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

Categories
Power Query

Word Wrapping Text in Power Query

How to wrap long lines, *without* splitting words. The input is a very long string, with no newlines:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris

Query

<a href="https://docs.microsoft.com/en-us/powerquery-m/list-accumulate">List.Accumulate</a> is an aggregate function. I’m using it to “sum” — to add strings together. If the current line plus the next word is longer than 80 characters, then insert a newline first.

To find the length of the current line, I only want the length after the very last newline. Occurrence.Last returns the last match, else -1 if nothing is found.

let  
    LoremIpsum = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis. Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque. Etiam bibendum dignissim mauris",

    // calculate length of string *after* the rightmost newline
    Text_LengthAfterNewline = (string as text) as number =>
        let 
            posLastNewline = Text.PositionOf(string, "#(lf)", Occurrence.Last),
            posOffset = if posLastNewline <> -1 then posLastNewline else 0,
            deltaLen = Text.Length(string) - posOffset
        in
            deltaLen,

    // word wraps text
    Text_WordWrap = (string as text, max_width as number) as text =>
        let
            words = Text.Split(string, " "),
            accum_result = List.Accumulate(
                words, "", 
                (state as text, current as text) as text =>
                    let
                        len = Text_LengthAfterNewline(state) + Text.Length(current) + 1,
                        maybeNewline =
                            if len > max_width then "#(lf)" else "",

                        accum_string = Text.Combine({state & maybeNewline, current}, " ")
                    in 
                        accum_string
            )
        in
            accum_result,
    
    wrapped_text = Text_WordWrap(LoremIpsum, 80)
in
    wrapped_text

The final result is 80 characters wide or less

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed eu laoreet turpis.
Curabitur lacinia, risus ut rhoncus mattis, turpis lorem iaculis justo, nec
ultrices arcu erat vitae felis. Pellentesque vulputate efficitur scelerisque.
Etiam bibendum dignissim mauris

Validating lengths of each line

let
    Source = #"wrap lines",
    validate_lengths =
        let 
            lines = Text.Split(Source, "#(lf)"),
            lengths = List.Transform(
                lines,
                each [ String = _, Length = Text.Length(_) ])
        in
            Table.FromRecords(
                lengths,
                type table[String = text, Length = number],
                MissingField.Error )
in
    validate_lengths
Categories
Cheatsheet Power BI Power Query References And Cheat Sheets

Power Query Custom Functions – Cheat Sheet

Converting Types

DateTime.FromText using format strings

let dt =  DateTime.FromText("2001-01-01T05:09:23", [Format = "yyyy-MM-ddTHH:mm:ss", Culture = "en-us"]) in dt

For format strings syntax, see: dotnet/custom-date-and-time-format-strings

Web Requests

Web.Contents without Refresh Errors

The main cause of Web.Contents not refreshing can be fixed by adding the options[Query] and options[RelativeaPath] parameters. (The UI doesn’t create them for you)

This example uses https://www.metaweather.com/api/location/search?query=london See more: Skip Test Connection

// lookup city GUID - simplified
let
    exampleUrl = "https://www.metaweather.com/api/location/search?query=london",
    Headers = [
        Accept="application/json"
    ],
    BaseUrl = "https://www.metaweather.com",
    Options = [
        RelativePath = "/api/location/search",
        Headers = Headers,
        Query = [
            query = "london"
        ],
        ManualStatusHandling = {400, 404}
    ],
    // wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
    response = Web.Contents(BaseUrl, Options),
    buffered = Binary.Buffer(response),
    response_metadata = Value.Metadata(response),
    status_code = response_metadata[Response.Status],
    final_result =
        if List.Contains({400, 404}, status_code) then response_metadata
        else buffered,
    from_json = Json.Document(final_result),
    #"weather record" = from_json{0}
in
    #"weather record"

Handling Specific HTTP Error Status Codes

You can filter results based on the status code, instead of getting errors.
See more: override default error handling, and the example wait-for loop

/* web request, act based on the HTTP Status Code returned
see more:
    override default error handling: https://docs.microsoft.com/en-us/power-query/handlingstatuscodes
    example wait-for loop: https://docs.microsoft.com/en-us/power-query/waitretry#manualstatushandling
*/
let
    WikiRequest = (pageName as text) as any =>
        let
            BaseUrl = "https://en.wikipedia.org/wiki",
            Options = [
                RelativePath = pageName,
                ManualStatusHandling = {400, 404}
            ],
            // wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
            response = Web.Contents(BaseUrl, Options),
            buffered = Binary.Buffer(response),
            response_metadata = Value.Metadata(response),
            status_code = response_metadata[Response.Status],
            final_result = [
                buffered = buffered,
                response_metadata = response_metadata
            ]
        in
            final_result,
    Queries = {"Cat", "DoesNot Exist fake page"},
    Items = List.Transform(
        Queries,
        each WikiRequest( _ )
    ),
    ResponseTable = Table.FromRecords(
        Items,
        type table[buffered = binary, response_metadata = record], MissingField.Error
    ),

    #"Expanded HTTP Status Codes" = Table.ExpandRecordColumn(ResponseTable, "response_metadata", {"Response.Status"}, {"Response.Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded HTTP Status Codes",{{"Response.Status", Int64.Type}})
in
    #"Changed Type"

WebRequest: Wrapper with Better Defaults

You can get the full file with extra comments: WebRequest.pq

    let
        /*
        Example using this url:
            (https://www.metaweather.com/api/location/search?lattlong=36.96,-122.02)

            WebRequest(
                "https://www.metaweather.com",
                "api/location/search",
                [ lattlong = "36.96,-122.02" ]
            )

        Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':
            - Not using Query and Relative path cause refresh errors:
                (https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/)

            - You can opt-in to Skip-Test:
                (https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/)

            - Debugging and tracing the HTTP requests
                (https://blog.crossjoin.co.uk/2019/11/17/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature/)

        */
        WebRequest = (
            staticPath as text,                 // domain
            relativePath as text,               // basically use everything after ".com" to "?"
            optional query as nullable record,  // url query string
            optional asRaw as nullable logical, // use true if content is not Json
            optional headers as nullable record // optional HTTP headers as a record
        ) as any =>
            let
                query = query ?? [],
                asRaw = asRaw ?? false, // toggles calling Json.Document() or not
                headers = headers ?? [
                    Accept="application/json"
                ],

                baseUrl = staticPath,
                options = [
                    RelativePath = relativePath,
                    Headers = headers,
                    Query = query
                    // optionally toggle handling errors for specific HTTP Status codes
                    // ManualStatusHandling = {400, 404}
                ],
                // wrap 'Response' in 'Binary.Buffer' if you are using it multiple times
                response = Web.Contents(staticPath, options),
                metadata = Value.Metadata(response),
                buffered = Binary.Buffer(response),
                result = Json.Document(buffered)
            in
                [
                    response = if asRaw then buffered else result,
                    status_code = metadata[Response.Status],
                    metadata = metadata
                ]
    in
        WebRequest

Chaining Web.Contents to Merge Many Queries

    let 
        response_locations = WebRequest(
            "https://www.metaweather.com",
            "api/location/search",
            [ lattlong = "36.96,-122.02" ]
        ),
        location_schema = type table[
            distance = number, title = text,
            location_type = text, woeid = number, latt_long = text
        ],
        cityListing = Table.FromRecords(response_locations[response], location_schema, MissingField.Error),
        
        city_mergedRequest = Table.AddColumn(
            cityListing,
            "LocationWeather",
            (row as record) as any =>
                let
                    woeid = Text.From(row[woeid]),
                    response = WebRequest(
                        "https://www.metaweather.com",
                        "api/location/" & woeid,
                        []
                    )
                in
                    response,
            type any
        )
    in
        city_mergedRequest

Html.Table – Parsing with CSS Selectors

Select a Single Element: an Image, url, text, etc…

This fetches the current heading text on the blog

let
    Url = "https://powerbi.microsoft.com/en-us/blog/",
    Response = Web.Contents( Url ),
    /*
    note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
    Non-tabular scraping like Images or any single elements, does not use a "RowSelector"

    This CSS Selector finds exactly one element, the Page's Header Text

        .section-featured-post .text-heading1 a
    */
    HeaderText = Html.Table(
        Response,
        {
            { "Page Header", ".section-featured-post .text-heading1 a" }
        }
    )
in
    HeaderText

Parsing Element’s Attributes

let
    Url = "https://powerbi.microsoft.com/en-us/blog/",
    Response = Web.Contents( Url ),
    /*
    The 3rd argument in "columnNameSelectorPairs" is the transformation function.
    by default it uses:

            each _[TextContent]
    */
    HeaderAsElement = Html.Table(
        Response,
        { { "Link", ".section-featured-post .text-heading1 a", each _ } }
    ),

    ElementRecord = Table.ExpandRecordColumn(
        HeaderAsElement, "Link",
        {"TagName", "TextContent", "Attributes"}, {"TagName", "TextContent", "Attributes"}
    ),

    ExpandedAttributes = Table.ExpandRecordColumn(
        ElementRecord, "Attributes",
        {"href", "rel", "title"}, {"attr.href", "attr.rel", "attr.title"}
    )
in
    ExpandedAttributes

Select Tables using your own CSS Selectors

CSS Row Selector
Results of the RowSelector
CSS Column Selector
Results of columnNameSelectorPairs
// Docs on Enum
let
    Source = "https://docs.microsoft.com/en-us/previous-versions/dynamics/ax-2012/reference/gg841505(v=ax.60)",
    // note: normally do not pass dynamic urls like this, see cheatsheet on preventing refresh errors
    Response = Web.BrowserContents( Source ),
    /*

        Think of "RowSelector" as selecting a table row
        Then for every row, you select "columns" using the "columnNameSelectorPairs" selector
        The combination gives you a table cell.

        For more on CSS Selectors, see: <https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Selectors>
    */

    columnNameSelectorPairs = {
        // column names don't matter here, since I'm using .PromoteHeaders
        { "Column1", "TABLE.table > * > TR > :nth-child(1)" },
        { "Column2", "TABLE.table > * > TR > :nth-child(2)" },
        { "Column3", "TABLE.table > * > TR > :nth-child(3)" }
    },

    t1 = Html.Table(
            Response, columnNameSelectorPairs,
            [RowSelector = "TABLE.table > * > TR"]
    ),

    t2 = Table.PromoteHeaders( t1, [PromoteAllScalars = true] ),
    FinalTable = Table.TransformColumnTypes(
            t2,
            { { "Name", type text }, { "Value", Int64.Type }, { "Description", type text} }
        )
in
    FinalTable

SQL Native Query

Parameterized SQL queries with Value.NativeQuery()

let
    Source = Sql.Database("localhost", "Adventure Works DW"),
    Test = Value.NativeQuery(
        Source,
        "SELECT * FROM DimDate 
        WHERE EnglishMonthName=@MonthName",
        [
            MonthName = "March",
            DayName = "Tuesday"
        ]
    )
in
    Test

Other

Cartesian Product

Table Cartesian Product
let    
    #"Add Column Pair2" = Table.AddColumn(
        Table_Pairs1, "Pairs2",
        each Table_Pairs2,
        Table.Type
    ),
    #"Expanded Pairs" = Table.ExpandTableColumn(
        #"Add Column Pair2",
        "Pairs2",
        {"Color", "Property"},
        {"Color", "Property"}
    )
in
    #"Expanded Pairs"

Details: https://radacad.com/cartesian-product-in-power-query-multiply-all-sets-of-all-pairs-in-power-bi

Using Multiple `Enter Data` Tables in One Query

You can make start with ‘enter data’, then duplicate that line. You end up with two steps, each are their own enter data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzEvOLFbSUfLJrEosSgEyTJRidaKVHHNSKxJTijITgSLuqcnZ+UDaEiwTkgpS5ZtfkgGkjJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Species = _t, Age = _t]),
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krNU9JRciotKUktSsupBLINlWJ1opWCE4sSM4A858QSIGlkDBYMSU0Bclzy04GkiYFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Species = _t, Age = _t]),
    Final = Table.Combine( { Source, Source2 } )
in
    Final

Convert Table to JSON

TableToJson = (source as table, optional encoding as nullable number) as text =>
        let
            encoding = encoding ?? TextEncoding.Utf8,
            bin = Json.FromValue(source, encoding),
            jsonAsText = Text.FromBinary(bin, encoding)
        in
            jsonAsText
Animals = #table(
    {"Animal", "Id"},
    {{"Cat", 1}, {"Turtle", 2}} ),
TableTo_Json( Animals )

Json output:

[{"Animal":"Cat","Id":1},{"Animal":"Turtle","Id":2}]

Converting a list of items to a CSV string

let
    // Converts a list of any type to text. Works well on most types
    // although to support all cases, it requires more logic
    mixedList = {4, "cat", #date(1999,5,9), 0.4},
    ListAsText = List.Transform(mixedList, each Text.From(_)),
    CsvText = Text.Combine( ListAsText, ", ")
in
    //output: "4, cat, 5/9/1999, 0.4"
    CsvText 

Functions

Using Optional Parameters with Default Values

The Null_coalescing_operator ?? simplifies default values.
encoding will be set to what the user passed, unless it’s null. In that case, it is set to TextEncoding.Utf8

let
    Read_Json = (json as any, encoding as nullable number) as any =>
        // calls Json.Document, using UTF8 by default
        let
            encoding = encoding ?? TextEncoding.Utf8,
            result = Json.Document(json, encoding)
        in
            result
in
    Read_Json

Mapping Function Calls based on Type

Caller chooses which type of conversioin to use, based on type names { date, datetime, datetimezone }

let
    // 1] get a `type` from caller
    // 2] return a difference function based on the type
    GetTransformByType = (_type as type) as function =>
        let
            // originally from: <https://docs.microsoft.com/en-us/power-query/helperfunctions#tablechangetype>
            func_transform = 
                 if (Type.Is(_type, type date)) then Date.From
            else if (Type.Is(_type, type datetime)) then DateTime.From
            else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From
            else (t) => t // else return self
        in
            func_transform,

    nowDtz = DateTimeZone.LocalNow(),    

    // invoke in 2 steps
    toDate = GetTransformByType(type date),
    callDate = toDate( DateTimeZone.FixedLocalNow() ),

    // create, and invoke functions
    Results = [
        asDate = (GetTransformByType(type date))( nowDtz ),
        asDateTime = (GetTransformByType(type datetime))( nowDtz ),
        asDateTimeZone = (GetTransformByType(type datetimezone))( nowDtz )
    ]
in
    Results
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
PowerShell Quick Tips

Creating a Discord Webhook in a few lines of PowerShell

Discord Web Hooks are easier than you’d think. It uses a regular `HTTP` web request .

Step1: Creating your webhook url

Go to: Server Settings -> Integrations -> View Webhooks
Then click create webhook.

Choose a name, the channel, then copy webhook url

Step2: Invoke-RestMethod

$webhookUri = 'https://discord.com/api/YourWebhookUrlHere'

$Body = @{
  'username' = 'Nomad'
  'content' = 'https://memory-alpha.fandom.com/wiki/Nomad'
}
Invoke-RestMethod -Uri $webhookUri -Method 'post' -Body $Body

Success!

Docs

There are optional webhook arguments to customize the output: https://discord.com/developers/docs/resources/webhook#execute-webhook

Comparison with curl

I tried making curl easier to read by splitting content, and pipe so that you don’t have to manually escape Json

$Json = @{ 'username' = 'Nomad'; 'content' = 'https://memory-alpha.fandom.com/wiki/Nomad' } | ConvertTo-Json -Compress

$Json | curl -X POST -H 'Content-Type: application/json' -d '@-' $webhookUri