Tag: Powershell

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

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

Tags Experiment, Powershell, What Not to Do

Experiment Formatting

Query to Summarize All Queries

Summarize.Query.pq

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

Tags Experiment, Power BI, Powershell, Pwsh

Experiment

Single File With Automatic Inline Testing

Addon: https://marketplace.visualstudio.com /items?itemName=pspester.pester-test
Plus Error Lens

Tags Animation, Experiment, Gif, Pester, Powershell, Pwsh

Experiment PowerShell Pwsh7+

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

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

Using Format-ControlChar

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

Tags ANSI-Control-Chars, Commandline, Console, Powershell

Command Line Experiment

Experiments / Answers of 2022-02

Post author By Jake Bolton
Post date 2022-02-20
No Comments on Experiments / Answers of 2022-02

File Selection CLI

Revisiting `BASH`

> 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

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

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

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

Tags Experiment, Power BI, Power Query, Powershell

PowerShell Quick Tips

Creating a Discord Webhook in a few lines of PowerShell

Post author By Jake Bolton
Post date 2021-01-22
No Comments on 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

Tags Discord, Powershell, Web API, WebHook

Command Line PowerShell Quick Tips References And Cheat Sheets

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

Post author By Jake Bolton
Post date 2021-01-20
No Comments on 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 }

Tags Commandline, Powershell, Reference, tip

Command Line Getting-Started PowerShell Pwsh7+ Quick Tips What's New

PowerShell : Prefixing lines with the Pipe operator |

Post author By Jake Bolton
Post date 2021-01-09
No Comments on 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

Tags Command Line, Inspection, Powershell, Pwsh, tip, What's New

Formatting PowerShell Snippets

Reusable Calculated Properties in PowerShell

Post author By Jake Bolton
Post date 2020-12-17
No Comments on Reusable Calculated Properties in PowerShell

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

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

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

Label 'basePath' $basePath

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

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

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

Tags Color, Command Line, Customization, Powershell, tip

Excel Power BI Power Query

Which Power Query Functions exist in Power BI but not Excel?

Post author By Jake Bolton
Post date 2020-12-16
No Comments on Which Power Query Functions exist in Power BI but not Excel?

The final number of missing identifiers*

The only function that **Power BI** is missing is `Facebook.Graph` (on my machine)

* This query checks for missing identifiers (which may be functions).
( You can filter by type if you convert `#shared` to a table instead of calling `Record.FieldNames()` )

Generating the list using `#shared`

To get a list of all identifiers (functions, variables, constants) I use the variable named #shared . Create a new blank query, then paste this

let
    IdentifierList = List.Sort(Record.FieldNames( #shared ))
in
    IdentifierList

I copy using Copy Entire List, then storing the results into a PowerShell variable. I Repeat the same with Power BI.

To find out exactly which functions are different, use the Power Shell operator -NotIn

# Find functions in PBI but not Excel
$MissingExcel = $PowerBI | ? { $_  -notin $Excel }

# Find functions in Excel but not PBI
$MissingPowerBI = $Excel | ? { $_  -notin $PowerBI }

The list of functions Power BI is Missing

Facebook.Graph

The list of functions Excel is Missing

Note: This is the list for today, on my machine. Run the #shared query to find any changes.

AI.ExecuteInProc
AI.ExecuteInternal
AI.ExternalSort
AI.GetAutoMLEntity
AI.SampleStratifiedWithHoldout
AI.TestConnection
AIFunctions.Capacities
AIFunctions.Contents
AIFunctions.ExecuteInternal
AIFunctions.GetAutoMLEntity
AIFunctions.PostProcess
AIInsights.Contents
AIInsights.ContentsGenerator
AML.Execute
AML.ExecuteBatch
Acterys.Contents
Actian.Contents
AmazonRedshift.Database
Anaplan.Contents
ApacheHiveLLAP.Database
ApacheSpark.Tables
Asana.Tables
AtScale.Cubes
AutomationAnywhere.Feed
AzureCostManagement.Contents
AzureCostManagement.Tables
AzureDataExplorer.Contents
AzureDataExplorer.Databases
AzureDevOpsServer.AccountContents
AzureDevOpsServer.AnalyticsViews
AzureDevOpsServer.Feed
AzureDevOpsServer.Views
AzureEnterprise.Contents
AzureEnterprise.Tables
AzureHiveLLAP.Database
AzureMLFunctions.Contents
AzureMLFunctions.Execute
AzureMLFunctions.ExecuteBatch
AzureSpark.Tables
AzureTimeSeriesInsights.Contents
BI360.Contents
BIConnector.Contents
Binary.Range
Cdm.Contents
Cdm.MapToEntity
Cds.Contents
Cds.Entities
Cherwell.SavedSearches
Cognite.Contents
CommonDataService.Database
Compression.Brotli
Compression.LZ4
Compression.None
Compression.Snappy
Compression.Zstandard
CustomerInsights.Contents
DataVirtuality.Database
DataWorld.Contents
DataWorld.Dataset
Databricks.Contents
Denodo.Contents
DocumentDB.Contents
Dremio.Databases
Dynamics365BusinessCentral.Contents
Dynamics365BusinessCentral.EnvironmentContents
Dynamics365BusinessCentralOnPremises.Contents
DynamicsNav.Contents
Emigo.Contents
Emigo.GetExtractFunction
EmigoDataSourceConnector.GetExtractFunction
EmigoDataSourceConnector.NavigationFunctionType
EntersoftBusinessSuite.Contents
Essbase.Cubes
Exasol.Database
FactSetAnalytics.AuthenticationCheck
FactSetAnalytics.Functions
Fhir.Contents
Foundry.Contents
Geography.FromWellKnownText
Geography.ToWellKnownText
GeographyPoint.From
Geometry.FromWellKnownText
Geometry.ToWellKnownText
GeometryPoint.From
Github.Contents
Github.PagedTable
Github.Tables
GoogleAnalytics.Accounts
GoogleBigQuery.Database
Graph.Nodes
HexagonSmartApi.ApplySelectList
HexagonSmartApi.ApplyUnitsOfMeasure
HexagonSmartApi.ExecuteParametricFilterOnFilterRecord
HexagonSmartApi.ExecuteParametricFilterOnFilterUrl
HexagonSmartApi.Feed
HexagonSmartApi.GenerateParametricFilterByFilterSourceType
HexagonSmartApi.GetODataMetadata
HexagonSmartApi.Typecast
HiveProtocol.HTTP
HiveProtocol.Standard
HiveProtocol.Type
Html.Table
IRIS.Database
Impala.Database
Indexima.Database
IndustrialAppStore.NavigationTable
InformationGrid.Contents
Intune.Contents
JamfPro.Contents
JethroODBC.Database
Kyligence.Database
Linkar.Contents
LinkedIn.SalesContracts
LinkedIn.SalesContractsWithReportAccess
LinkedIn.SalesNavigator
LinkedIn.SalesNavigatorAnalytics
LinkedIn.SalesNavigatorAnalyticsImpl
List.ConformToPageReader
MailChimp.Collection
MailChimp.Instance
MailChimp.Tables
MailChimp.TablesV2
MariaDB.Contents
MarkLogicODBC.Contents
Marketo.Activities
Marketo.Leads
Marketo.Tables
MicroStrategyDataset.Contents
MicroStrategyDataset.TestConnection
MicrosoftAzureConsumptionInsights.Contents
MicrosoftAzureConsumptionInsights.Tables
MicrosoftAzureConsumptionInsights.Test
MicrosoftGraphSecurity.Contents
Mixpanel.Contents
Mixpanel.Export
Mixpanel.FunnelById
Mixpanel.FunnelByName
Mixpanel.Funnels
Mixpanel.Segmentation
Mixpanel.Tables
Netezza.Database
PQ_Hi_World.Contents
Parquet.Document
Paxata.Contents
Pdf.Tables
PlanviewEnterprise.CallQueryService
PlanviewEnterprise.Feed
PlanviewProjectplace.Contents
PowerBI.Dataflows
PowerPlatform.Dataflows
ProductInsights.Contents
ProductInsights.QueryMetric
Projectplace.Feed
Python.Execute
QubolePresto.Contents
QuickBase.Contents
QuickBooks.Query
QuickBooks.Report
QuickBooks.Tables
QuickBooksOnline.Tables
R.Execute
Roamler.Contents
ShortcutsBI.Contents
Siteimprove.Contents
Smartsheet.Content
Smartsheet.Query
Smartsheet.Tables
Snowflake.Databases
Spark.Tables
SparkPost.GetList
SparkPost.GetTable
SparkPost.NavTable
SparkProtocol.Azure
SparkProtocol.HTTP
SparkProtocol.Standard
SparkProtocol.Type
Spigit.Contents
StarburstPresto.Contents
Stripe.Contents
Stripe.Method
Stripe.Tables
SurveyMonkey.Contents
SweetIQ.Contents
SweetIQ.Tables
Table.AddFuzzyClusterColumn
Table.CombineColumnsToRecord
Table.ConformToPageReader
Table.FuzzyGroup
TeamDesk.Database
TeamDesk.Select
TeamDesk.SelectView
Tenforce.Contents
TibcoTdv.DataSource
TimeSeriesInsights.Contents
Troux.CustomFeed
Troux.Feed
Troux.TestConnection
Twilio.Contents
Twilio.Tables
Twilio.URL
VSTS.AccountContents
VSTS.AnalyticsViews
VSTS.Contents
VSTS.Feed
VSTS.Views
Value.Alternates
Value.Expression
Value.Lineage
Value.Optimize
Value.Traits
Vena.Contents
Vertica.Database
VesselInsight.Contents
Web.BrowserContents
Webtrends.KeyMetrics
Webtrends.Profile
Webtrends.ReportContents
Webtrends.Tables
WebtrendsAnalytics.Tables
Witivio.Contents
WorkforceDimensions.Contents
WorkplaceAnalytics.Data
Zendesk.Collection
Zendesk.Tables

Tags Excel, Power BI, Power Query, Powershell