Adventures In Power Query Power BI Power Query

Power Query Functions ( Part 1): Introduction to Optional Parameters

You can view the final .pq query here

3 Main Methods to Declare Optional Parameters

There’s 3 main ways you can declare optional parameters in your own functions.

  1. By setting your parameter type to nullable type.

    name as text becomes
    name as nullable text

    This version requires you to always pass something, even if it’s null.

  2. By setting the type to optional like

    optional name as nullable text . You can shorten it by skipping the nullable part
    optional name as text ( Because optional parameters are implicitly nullable )

    Unlike #1, you can skip optional parameters when calling functions.

  3. By using a record parameter.

    Usually it’s a optional options as record

    This lets you call a function with any combination of keyword arguments.

  4. Merging Default Values With the User’s Record

With record parameters you can merge the user’s record with your your own default values.
It lets the user pick and choose which defaults to override — compared to using nulls as an “all-or-nothing” approach. I’ll cover this in a future post.

Creating Your Own Custom Functions

Let’s write a wrapper that calls Text.Combine . It’s a simple example to see how the parameter types differ

    Join_Nullable = (texts as list, separator as nullable text) =>
        Text.Combine( texts, separator ),

    Join_Optional = (texts as list, optional separator as text) =>
        Text.Combine( texts, separator ),

Type 1: Using Nullable Parameters

Regular nullable parameters are still mandatory. Even if it’s null.

// input [1]
Join_Nullable( chars, ", " )

// input [2]
Join_Nullable( chars, null )

// input [3]
Join_Nullable( chars )

// output [1]
a, b, c, d, e, f, g, h

// output [2]

// output [3]
Error: 1 arguments were passed
to a function which expects 2.

Type 2: Optional Parameters

If functions end with optional parameters, you can skip them entirely.

// input [1]
Join_Optional( chars, ", " )

// input [2]dfsdf
Join_Optional( chars, null )

// input [3]
Join_Optional( chars )
// output [1]
a, b, c, d, e, f, g, h

// output [2]

// output [3]

Type 3 Examples: Using Optional Records

Text.JoinString = (strings as list, optional options as record) as text => let
        Prefix    = options[Prefix]?    ?? "",
        Suffix    = options[Suffix]?    ?? "",
        Delimiter = options[Delimiter]? ?? ","
        Prefix & Text.Combine( strings, Delimiter ) & Suffix

You can mix and match any combinations of the keys Prefix, Suffix, and Delimiter .

names = {"Jen", "Hubert", "Nobody", "Somebody" },

Default       = Text.JoinString( names ) ,
AsCsv         = Text.JoinString( names, [ Delimiter = ", "] ),

AsTablePipes  = Text.JoinString( names, [
    Prefix = "| ", Delimiter = " | ", Suffix = " |" ] ),

AsBullets     = Text.JoinString( names, [
    Prefix    = " #(2022) ",
    Delimiter = " #(cr,lf) #(2022) "

Examples In The Standard Library

Type 2 Examples: Optional Parameters

When you import columns with Table.TransformColumnTypes, the UI doesn’t include the culture parameter. Unless you choose “using locale”.

// it's declared as
     table as table, typeTransformations as list,
     optional culture as text) as table

// The GUI skips the culture parameter 
= Table.TransformColumnTypes( Source,{  {"Date", type text}} )

// if you click on "locale"
= Table.TransformColumnTypes( Source,{  {"Date", type text}}, "en-GB" )

Type 3 Examples: Using Optional Records

// It's declared as
DateTime.FromText( text as nullable text, optional options as any) as nullable datetime

// without the optional parameter
= DateTime.FromText("2010-12-31T01:30:25")

// They pass 2 keyword-style arguments.
= DateTime.FromText(
     "30 Dez 2010 02:04:50.369730", 
          Format = "dd MMM yyyy HH:mm:ss.ffffff", 
     ] )

Why does DateTime.FromText use options as any and not options as record ? In this case it’s for backwards compatibility. If they were writing it from scratch today, it could have used a record. Older versions used a type text parameter.

Adventures In Power Query Experiment Formatting Uncategorized What Not To Do

What Not To Do: When Letting Your Code Breathe Goes Bad

White Space before a function call is allowed

Whitespace between function calls and the name are allowed. Including newlines
These are equivalent statements:

= DoStuff( args )

// and
= DoStuff

args )

Record lookups also allow whitespace.
This is totally valid syntax wise. Not necessarily morally though.

    Func = () => [ 
        user = [ Name = "bob" ] 




Misleading Comments inside Lists, Records, and Function Calls are allowed

Comments do not affect parsing or execution.
Without syntax highlighting it looks like 4 is the final item in the list

    num = List.Count( { 0
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
    without syntax highlighting, it looks like the func call ended
    later, secretly do more
*/                                                  ,99 } )

in num

Now it’s slightly easier

    num = List.Count( { 0
       5 } & { 3, 4                                                  /*
    }) /* fake ending here  here * /
    without syntax highlighting, it looks like the func call ended
    later, secretly do more
*/                                                  ,99 } )

in num

The list operator .. allows a lot of expressions

You can use inline comments, resuming the list expression later

    num = List.Count( { 0
           5 } & { 3, 4 /*
    now do more */ } )
in num

You don’t have to wrap the try-catch expressions in parenthesis.

let l = { 
          try "foo" + 99 catch (e) => 3           ..
          try File.Contents( "invalid" ) catch () => 7 } 
    l = { 3..7 } // is true

This version gave an interesting error. I thought perhaps try-catch expressions doesn’t work for inline list indices ?

let g = { 
          try 10 catch (e) => 3           ..
          try 27 / 0 catch () => 53 }

in g
Expression.Error: The number is out of range of a 32 bit integer value.

But then realized division by 0 in power query does not throw an error record. It has the type number.


How to Import Dates Correctly Across Culture or Locales

Have you written a report where dates import right. But they break when ran on another machine? If you import right, you don’t have to set cultures in your report settings.

This Is Caused by Default Cultures

When you import columns with Table.TransformColumnTypes, the UI doesn’t include the culture parameter.

Transform without culture

If you pick Date and “Using Locale…” it will replace this

// replace this
= Table.TransformColumnTypes( Source,{  {"Date", type text}} )
// with this
= Table.TransformColumnTypes( Source,{  {"Date", type text}}, "en-GB" )

Now I can import a CSV that uses the culture “English UK” or “German” or others
And it still works when it’s ran on an “English-US” environment

When is Culture / Locale used?

Every time you convert dates and numbers to text, culture is involved. When you convert from text to numeric values, culture is involved.
If you don’t set the culture parameter, it falls back on the value Culture.Current . Mine uses “en-us”

Sample data to break things

Here’s a variety of dates to compare which breaks. They came from cultures using the named ShortDate “d” format string

Related Links

Adventures In Power Query Uncategorized

Capturing Metadata of your Web.Contents calls – Using REST APIs in Power Query


Any value in Power Query can store info in a metadata record. “Datasources” often have extra info. Web.Contents exposes the full request url including the Query variables, request headers, HTTP Status codes, and more.

You can view the final query here:

Finding the binary step

The core “trick” to making this work is calling Value.Metadata on the right step.

For Web.Contents and File.Contents you want the binary value they return, before converting the response

For Folder.Files and Folder.Contents you want the binary value in the column named [Content]

Screenshot of Find Folders Content
Finding the binary Content columns

Splitting Web.Contents into steps

Lets call this Rest API to some JSON:
If you add a query using the Web UI, it combines “download raw bytes” and “convert to json” as one step.

    Source = Json.Document(Web.Contents("", [RelativePath = "/json"] ))

To access the response, we need to split them up:

    ResponseBytes = Web.Contents("", [RelativePath = "/json"] ),
    Source = Json.Document( ResponseBytes )

Reading the Metadata

First call Value.Metadata on our first step. It returns a regular record.
You can drill down to copy important properties to the top level

Screenshot Viewing the metadata record in Power Query
Viewing the metadata record from Value.Metadata( Web.Contents( .. ))

    BaseUrl      = "",
    RawBytes     = Web.Contents( BaseUrl, [ RelativePath = "/json" ]), // type is: binary|error
    ResponseMeta = Value.Metadata( RawBytes ), // type is: record
    Json         = Json.Document( RawBytes ),  // type is: record/list/error

    // Now lets grab important fields by drilling into the ResponseMeta record
    StatusCode     = ResponseMeta[Response.Status]?, // type is: text

    // Content.Uri is a function that returns the full filepath for files,
    // For web requests you get the full url including the value of the query string
    FullRequestUrl = ResponseMeta[Content.Uri]() // type is: text

Decoding the response as raw text for debugging

Say your API returns HTML when you expect JSON
Some APIs will return errors as HTML, so if something isn’t right it’s worth checking.

That causes Json.Document to fail. Because we split steps up — RawText continues to work. It’s a quick way to verify if it’s actually returning CSV / JSON / HTML / etc.

If your files aren’t using UTF8 you’ll need to include the encoding parameter. TextEncoding.Utf8 is a good default for the web

RawText = Text.FromBinary( RawBytes ), // type is: text/error

Tip: The UI might use Lines.FromBinary, which means you need to combine them to one string. Text.FromBinary saves you a step.

Related Posts

Related Links

Related Functions

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 

Animations 2022-10

VS Code feature


windowsTerminal using "experimental.useBackgroundImageForWindow": true,

Vs Code Features

using multiple cursors

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

Function Parameters Types

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

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

Related Posts

Related Links

Related Functions

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