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

Check out the new tutorial at https://blog.powp.co/my-power-query-web-contents-cheat-sheet-6a5bbfdce5eb

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
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
PowerShell References And Cheat Sheets

Learning PowerShell

Getting started with PowerShell

Main

Grammar

Important Topics, Language quirks

For( ;; ) vs ForEach vs ForEach-Object

It’s first argument is a ScriptBlock so it appears like a control loop.

Notice return verses break in ForEach-Object . Remember that the { ... stuff ... } in this case is an anonymous function , not a language control statement
It is a parameter to the ForEach-Object . When not specified, it’s the -Process Parameter

Flow Control with language Keywords

Attributes for validation

Best Practices

Naming

Categories
Power BI Power Query References And Cheat Sheets

Resources to learn Power Query

Tutorials

Ben Gribaudo dives into Power Query topic by topic

Part 1: Introduction to let expressions
Part 3: Using and writing Functions
Part 8: Time: date, time, datetime, datetimezone and duration
Part 11-13: Tables
Part 15: Error handling
Part 16: Power Query primative types
Part 17: Facets
Part 18: Custom types


Chris Webb’s blog: Crossjoin.co.uk
Another high quality resource.
How to use Web API’s through Web.Content‘s Query and RelativePath parameters
He knew how to fix Web.Contents() refresh errors all the way back in 2016!

Documentation

Tools

VS Code extension: Power Query

If you use the advanced editor check this out.
It supports autocomplete Tool tips display optional parameters.

VS Code extension: Power Query uses PowerQuery-Parser and PowerQuery-Formatter

Query Diagnostics

Query Diagnostics in Power BI

Tips

You can copy -> paste multiple queries into a new Power BI Report (notice it included a required referenced query ListAsText even though I didn’t select it.
You can copy -> paste multiple queries into at text editor