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

Examples on Dax.Guide have a “try it” button to launch the editor
culture
converting from text
. TransformColumnTypes
using the optional parameter: Culture
If they were all the same culture, like en-GB
, you can choose column type Using Locale...
which will create a step like this:
= Table.TransformColumnTypes(
Source,
{{"DateString", type datetime}},
"en-GB"
)
cultures
Using a Lookup ColumnRecord.Field( record, key)
lets you lookup a record
field without hard-coding the name. That value is used by DateTime.FromText( string, culture)
let
// These datetimes are all the same time, using a different 'culture'
Source = #table(
type table[DateString = text, User = text],
{
{ "2/13/2021 7:01:46 PM", "Bob" },
{ "13/02/2021 19:01:46", "Jen" },
{ "13.02.2021 19:01:46", "Kate" }
}
),
Culture = [
Bob = "en-US",
Jen = "de-DE",
Kate = "en-GB"
],
// [User] column is mapped to a 'culture'
// then [DateString] is converted
DateTime_FromDynamicCulture = (row as record) as datetime =>
let
author = row[User],
culture = Record.Field( Culture, author),
result = DateTime.FromText( row[DateString], culture)
in
result,
convertDatetimes = Table.AddColumn(
Source, "DateTime_DynamicCulture",
each DateTime_FromDynamicCulture(_),
type datetime
)
in
convertDatetimes
Culture.Current
Get-Culture
in PowerShellusing Get-Culture
Searching for partial matches of english
DateTime.ToText
and Date.ToText
DateTime
format stringsHow 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
List.Accumulate
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
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
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
// 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"
WebRequest
: Wrapper with Better Defaultslet
/*
Example using the 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" ]
)
*/
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
) 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
// 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
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
CSS Selectors
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
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
RowSelector
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:
*/
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
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
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
JSON
TableTo_Json = (source as table, optional encoding as nullable number) as text =>
let
encoding = if encoding <> null then encoding else 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}]
list
of items to a CSV
stringlet
// 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
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
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:
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
Table of Contents: Emoji Charts Table
[CmdletBinding]
‘s methods@source:default
when clause
: code.visualstudio.com/keybindingsSnippets.json
Variables: https://code.visualstudio.com/docs/editor/userdefinedsnippets#_variablesDiscord Web Hooks are easier than you’d think. It uses a regular `HTTP` web request .
Go to: Server Settings -> Integrations -> View Webhooks
Then click create webhook.
copy webhook url
$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!
There are optional webhook arguments to customize the output: https://discord.com/developers/docs/resources/webhook#execute-webhook
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
The output of diff -q path1 path2
is pretty verbose. This function
If git
is installed, you may need to update your %PATH%
environment variable.
# in your profile
$Env:Path = "$Env:ProgramFiles\Git\usr\bin", $Env:Path -join ';'
Compare-Directory
This is an isolated version of Ninmonkey.Console: Compare-Directory
. I removed all dependencies except coloring is provided by the module PoshCode/Pansies.
function Invoke-NativeCommand {
<#
.synopsis
wrapper to both call 'Get-NativeCommand' and invoke an argument list
.example
PS> # Use the first 'python' in path:
Invoke-NativeCommand 'python' -Args '--version'
#>
param(
# command name: 'python' 'ping.exe', extension is optional
[Parameter(Mandatory, Position = 0)]
[string]$CommandName,
# Force error if multiple binaries are found
[Parameter()][switch]$OneOrNone,
# native command argument list
[Alias('Args')]
[Parameter(Position = 1)]
[string[]]$ArgumentList
)
$binCommand = Get-NativeCommand $CommandName -OneOrNone:$OneOrNone -ea Stop
& $binCommand @ArgumentList
}
function Compare-Directory {
<#
.SYNOPSIS
Compare Two directories using 'diff'
.EXAMPLE
Compare-Directory 'c:\foo' 'c:\bar\bat'
#>
[Alias('DiffDir')]
param(
# Path1
[Parameter(Mandatory, Position = 0)]
[string]$Path1,
# Path2
[Parameter(Mandatory, Position = 1)]
[string]$Path2,
# Output original raw text?
[Parameter()][switch]$OutputRaw
)
$Base1 = $Path1 | Get-Item -ea Stop
$Base2 = $Path2 | Get-Item -ea Stop
$Label1 = $Base1 | Split-Path -Leaf | New-Text -fg 'green'
$Label2 = $Base2 | Split-Path -Leaf | New-Text -fg 'yellow'
"Comparing:
Path: $Path1
Path: $Path2
" | Write-Information
$stdout = Invoke-NativeCommand 'diff' -args @(
'-q'
$Base1
$Base2
)
$outColor = $stdout
$outColor = $outColor -replace [regex]::Escape($path1), $Label1
$outColor = $outColor -replace [regex]::Escape($path2), $Label2
$outColor = $outColor -replace 'Only in', (New-Text 'Only In' -fg 'red')
$outColor = $outColor -replace 'Differ', (New-Text 'Differ' -fg 'red')
if ($OutputRaw) {
h1 'Raw' | Write-Information
$stdout
return
}
$outColor
}
function Get-NativeCommand {
<#
.synopsis
wrapper that returns Get-Item on a native command
.example
# if you want an error when multiple options are found
PS> Get-NativeCommand python -OneOrNone
.example
# note: this is important, $cmdArgs to be an array not scalar for '@' usage
$binPy = Get-NativeCommand python
$cmdArgs = @('--version')
& $binPy @cmdArgs
.example
#>
[cmdletbinding()]
param(
# Name of Native .exe Application
[Parameter(Mandatory, Position = 0, ValueFromPipeline)]
[object]$CommandName,
# One or None: Raise errors when there are more than one match
[Parameter()][switch]$OneOrNone
)
process {
try {
$query = Get-Command -Name $CommandName -All -CommandType Application -ea Stop
| Sort-Object Name
} catch [CommandNotFoundException] {
Write-Error "ZeroResults: '$CommandName'"
return
}
if ($OneOrNone -and $query.Count -gt 1) {
$query | Format-Table -Wrap -AutoSize -Property Name, Version, Source
Write-Error "OneOrNone: Multiple results for '$CommandName'"
return
}
if ($query.Count -gt 1) {
$query = $query | Select-Object -First 1
}
Write-Debug "Using Item: $($query.Source)"
$query
}
}
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 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.
??=
Assignment OperatorThis 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
??
Operator$AllFiles = $AllFiles ?? ( ls ~ -Depth 4 )
? 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 }
Power BI Discord asked the question:
> How do you use duplicate keys, and dynamic urls with Web.Contents
?
(They were using a web API that required duplicate keys)
After I wrote this, Chris Webb found an easier solution.
record
because keys must be distinct. Query = [ Key1 = 1, Key1 = 10]
will throw an error.Web.Contents
or else refreshes can breakI built a RelativePath
by Uri-escaping a list.
RelativePath
by Uri-escaping a listThe final query will request the url: https://www.google.com/search?q=dog&q=cat
This function generates the query string q=dog&q=cat
This Input | "q", { "dog", "cat" } |
Will Return | q=dog&q=cat |
let
QueryStr_UsingDuplicateKeys = (key as text, values as list) as text =>
// values are the 'value' of 'key'-> 'value' pairs
let
escapedList = List.Transform(
values,
each
key & "=" & Uri.EscapeDataString( Text.From(_) )
),
joinedArgs = Text.Combine(escapedList, "&")
in
joinedArgs
in
QueryStr_UsingDuplicateKeys
Now you can use Web.Contents
as normal.
let
BaseUrl = "https://www.google.com",
queryStr = QueryStr_UsingDuplicateKeys(
"q", {"dog", "cat"}
),
Options = [
RelativePath = "/search?" & queryStr,
Headers = [ Accept="application/json" ]
],
response_binary = Web.Contents(BaseUrl, Options)
in
response_binary
Note: BaseUrl
is for the static part of the url. Everything else should be in options[RelativePath]
or options[Query]
See docs: Web.Contents for details.
First try Chris’s method where you use Query
‘s Key-Value pairs a list
Results may vary. If it does not work, you can try this method.
I have not seen Query
publicly documented.
Query = [
q = {"dog", "cat"}
]
{}
— the request drops the parameter.Web.Contents(
"http://jsonplaceholder.typicode.com/comments",
[Query = [postId = {}] ]
)
Which results in the url: http://jsonplaceholder.typicode.com/comments
This is a good place to use Power Query‘s null coalesce
operator (Which isn’t in the official docs)
Query = [postId = myPostId ?? {}]
type any
.Table.AddColumn
to set it to number
[Num] * 2
work?Powerquery does not know what type will be returned by your function. That’s because each
is by definition a function that returns type any
each
expression in the Lexical Grammareach
functions