What's New

What’s New: May 2023: Dax Functions, and PBI Fabric

Dax Keeps pouring out new Functions.


So Many New DAX functions

CoPilot, and Fabric

Power BI Feature Summaries

Experiment Power BI Pwsh7+

Errors of 2023-01

Power BI

You can’t create a new table with the same name as an existing
query or item in the model.

The “New Table” button creates a new table named “Table”

This means if you create a query named “Table”, the UI cannot
create any new tables.
It’s before the rename step.

I discovered this also includes the names of disable queries.
At first I thought It was a cache—issue, but it’s not.

This is actually a good “bug”.
Which is better than if the opposite was true — letting you
create tables and expressions with ambiguous identifiers.

AI using PowerShellAI

Powershell /w the module PowerShellAI
# the original version that didn't parse 
# because ',' made number endings ambiguous
'1,000,2,000,3,000,0,000,1,000,2,000,3,000,0,000' -split '\,'
| Join-String -sep ' ' -SingleQuote
| Label 'original' -Before 1

'1000', '2000', '3000', '0', '1000', '2000', '3000'
| Join-String -sep ' ' { '{0:n0}' -f @( $_ -as 'int' ) } -SingleQuote
| Label 'should be' -Before 1 -After 1

$result ??= @{}
( $result.Steps1 ??= ai '# first 100 numbers modulous 4, multiplied by a factor of 1e6' )
| renderNice | Label 'Step1' -bef 1
label 'step3' -after 1 'This time it''s parsable, but, the numbers are not the same different.'
$result.Steps3 -split ',' -replace "'", '' | renderNice 
Experiment Power BI Power Query Uncategorized

Inspecting Function “subtypes” in Power Query

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

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

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

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

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

Metadata of the function type

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

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

Viewing Documenation.Examples

FunctionParameters Types

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

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

Your First Function types

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

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

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

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

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

The part that comes after meta operator is a regular record

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

        }, "<br>")

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


Query For the Screenshot

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

See More


Command Line Experiment Formatting

Experiments of 2022-09

Power BI / Power Query

Things to note

  • options is an record, when used this way it’s similar to Python’s kwargs
  • Merging two records with update the existing fields, adding new fields, if they do not yet exist
  • (in PowerQuery) the order of steps don’t change the final result (order of execution is the same) That’s why defaults after config works
  • Sometimes readability improves when placing large “blocks” like values_list out of order so the logic is on top
  • I replaced values *before* converting them to text so you have more control (before coercion )
Text.JoinSpecialValues_impl = (source as list, optional options as nullable record) as text =>
            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)" }
                        {"", "␠"}
                    } ) 

Power Query and Report.pbix (permalink) at github://ninmonkey


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

# Open git repos in browser
goto git microsoft/powerquery-parser | goto
goto | 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

  • [ ] new array ops, make a query like
  • = a1: e1 , except new functions could dynamically change the selection based on variable

Experiment Formatting

Experiments of 2022-08

Query to Summarize All Queries


Summarize ⁞ Queries ┐main_query.png

Using Inline Images and SVG in a Power BI Table

With the column set to Image Url, you’re able to

  • use an external image like
  • 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: <>
const cfg = {
    "GuildId": "180528040881815552",
    "Channel": "490008213056389120",

const apiUri = {
    "prefix": "",
    "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
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

$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

    Source = Record.RemoveFields(
        "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)
    #"Add Query Id"


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

CSS Column Selectors to modify an existing table

Refactoring Others Code

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

Run DAX in Your Browser: has a DAX Web App | What’s New

The announcement:

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

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


<a href="">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.

    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 =>
            posLastNewline = Text.PositionOf(string, "#(lf)", Occurrence.Last),
            posOffset = if posLastNewline <> -1 then posLastNewline else 0,
            deltaLen = Text.Length(string) - posOffset

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

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

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

    Source = #"wrap lines",
    validate_lengths =
            lines = Text.Split(Source, "#(lf)"),
            lengths = List.Transform(
                each [ String = _, Length = Text.Length(_) ])
                type table[String = text, Length = number],
                MissingField.Error )
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

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 See more: Skip Test Connection

// lookup city GUID - simplified
    exampleUrl = "",
    Headers = [
    BaseUrl = "",
    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}
    #"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:
    example wait-for loop:
    WikiRequest = (pageName as text) as any =>
            BaseUrl = "",
            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
    Queries = {"Cat", "DoesNot Exist fake page"},
    Items = List.Transform(
        each WikiRequest( _ )
    ResponseTable = Table.FromRecords(
        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}})
    #"Changed Type"

WebRequest: Wrapper with Better Defaults

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

        Example using this url:

                [ lattlong = "36.96,-122.02" ]

        Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':
            - Not using Query and Relative path cause refresh errors:

            - You can opt-in to Skip-Test:

            - Debugging and tracing the HTTP requests

        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 =>
                query = query ?? [],
                asRaw = asRaw ?? false, // toggles calling Json.Document() or not
                headers = headers ?? [

                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)
                    response = if asRaw then buffered else result,
                    status_code = metadata[Response.Status],
                    metadata = metadata

Chaining Web.Contents to Merge Many Queries

        response_locations = WebRequest(
            [ 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(
            (row as record) as any =>
                    woeid = Text.From(row[woeid]),
                    response = WebRequest(
                        "api/location/" & woeid,
            type any

Html.Table – Parsing with CSS Selectors

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

This fetches the current heading text on the blog

    Url = "",
    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(
            { "Page Header", ".section-featured-post .text-heading1 a" }

Parsing Element’s Attributes

    Url = "",
    Response = Web.Contents( Url ),
    The 3rd argument in "columnNameSelectorPairs" is the transformation function.
    by default it uses:

            each _[TextContent]
    HeaderAsElement = Html.Table(
        { { "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"}

Select Tables using your own CSS Selectors

CSS Row Selector
Results of the RowSelector
CSS Column Selector
Results of columnNameSelectorPairs
// Docs on Enum
    Source = "",
    // 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(
            { { "Name", type text }, { "Value", Int64.Type }, { "Description", type text} }

SQL Native Query

Parameterized SQL queries with Value.NativeQuery()

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


Cartesian Product

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


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

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

Convert Table to JSON

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

Json output:


Converting a list of items to a CSV string

    // 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, ", ")
    //output: "4, cat, 5/9/1999, 0.4"


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

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

Mapping Function Calls based on Type

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

    // 1] get a `type` from caller
    // 2] return a difference function based on the type
    GetTransformByType = (_type as type) as function =>
            // 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

    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 )
Power BI Power Query Quick Tips

Preserving Types When using “Add Custom Column” in Power Query

The default UI sets your column to type any.

You can use the optional argument of Table.AddColumn to set it to number
Or you can declare your function’s return type

Why doesn’t the original [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

See More