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