Categories
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]
abcdefgh

// 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]
abcdefghfds

// output [3]
abcdefghfds

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]? ?? ","
    in
        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.TransformColumnTypes( 
     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", 
          Culture="de-DE" 
     ] )

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.

Categories
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

let
    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
        let
            mappingList = Table.ToRecords( mapping ),
            result = List.Accumulate(
                mappingList,
                source,
                (state, cur) =>
                    if Text.Contains( state, cur[Partial], Comparer.OrdinalIgnoreCase )
                    then cur[New Value] else state
            )
        in result

in
    Text.ReplacePartialMatches

Query For the Screenshot

let
    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 ) ) ) )
    
in
    type_ofType_ofRequiredType

Related Posts

Related Links

Related Functions

Categories
Command Line Experiment Formatting Power BI Power Query VS Code

Experiments of 2021-10

Pwsh: Fuzzy Select Colors With Preview

PS> Get-ChildItem fg: | Where Name -match ‘red’
  • queries the color provider with partial matches
  • pipes to Out-Fzf or fzf for the preview and selection

Bitwise operators

Generated using a Pwsh script

ShouldProcess formatting

More Fzf

PSReadline: Auto-expand Aliases in the CLI

  • alt+shift+( surrounds selection, or the entire command in parenthesis
  • alt+shift+% runs Invoke-Formatter to format the code, including alias replacement

Pester5 ForEach | `code` snippet

Using Notebooks: for Github issue queries

Created a list of queries for the release of https://vscode.dev
ninmonkey/VS Code on the Web – Cheat Sheet – Custom urls.md

Power Query Web.Contents wrapper for Web API / REST calls

  • WebRequest_Simple.pq
  • enables ManualStatusHandling for better errors ( This is important for any REST APIs)
  • detects whether it’s Json or html, and returns a json record if existing
  • Always displays the response as plain-text as response_text
  • Lets you inspect request info, like headers used.

Power Query: Import from an external file

Experimenting with console formatting

Select a ton of properties, saving names as an array

Hashtables: Command line formatting

DAX: Syntax highlight bracket pairs



    "[dax-language]": {
        "editor.bracketPairColorization.enabled": true, //colorize the pairs
        "editor.guides.bracketPairs": true, // colorize vertical lines
        "editor.matchBrackets": "always",
        "editor.minimap.enabled": false,
        "editor.lineNumbers": "off",
    },
},

Pwsh: Prompt that summarizes recent errors

Pwsh: Colorized Directory Listing

  • Using terminal wt, Pwsh as the shell, and module: pansies for color
  • The current directory is a gradient, the boldest part is the most important part of the path.
  • cd-ing to a directory will summarize the new directory, without “spamming” the user when there’s a lot of items
    • Folders first, sorted by most recently modified
    • Files second, sorted most recently
    • Icon shows filetype

DAX: Conditionally Toggle Button using Measure

WordPress Image scaling

link to 1:1 image
link to 1:1 image
Categories
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

Query

<a href="https://docs.microsoft.com/en-us/powerquery-m/list-accumulate">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.

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

Validating lengths of each line

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
Categories
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 https://blog.powp.co/my-power-query-web-contents-cheat-sheet-6a5bbfdce5eb

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 https://www.metaweather.com/api/location/search?query=london See more: Skip Test Connection

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

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: https://docs.microsoft.com/en-us/power-query/handlingstatuscodes
    example wait-for loop: https://docs.microsoft.com/en-us/power-query/waitretry#manualstatushandling
*/
let
    WikiRequest = (pageName as text) as any =>
        let
            BaseUrl = "https://en.wikipedia.org/wiki",
            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
            ]
        in
            final_result,
    Queries = {"Cat", "DoesNot Exist fake page"},
    Items = List.Transform(
        Queries,
        each WikiRequest( _ )
    ),
    ResponseTable = Table.FromRecords(
        Items,
        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}})
in
    #"Changed Type"

WebRequest: Wrapper with Better Defaults

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

    let
        /*
        Example using this 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" ]
            )

        Details on preventing "Refresh Errors", using 'Query' and 'RelativePath':
            - Not using Query and Relative path cause refresh errors:
                (https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/)

            - You can opt-in to Skip-Test:
                (https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/)

            - Debugging and tracing the HTTP requests
                (https://blog.crossjoin.co.uk/2019/11/17/troubleshooting-web-service-refresh-problems-in-power-bi-with-the-power-query-diagnostics-feature/)

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

Chaining Web.Contents to Merge Many Queries

    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

Html.Table – Parsing with CSS Selectors

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

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

Parsing Element’s Attributes

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

Select Tables using your own CSS Selectors

CSS Row Selector
Results of the RowSelector
CSS Column Selector
Results of 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: <https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Selectors>
    */

    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

SQL Native Query

Parameterized SQL queries with 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

Other

Cartesian Product

Table Cartesian Product
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

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

let
    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 } )
in
    Final

Convert Table to JSON

TableToJson = (source as table, optional encoding as nullable number) as text =>
        let
            encoding = encoding ?? 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}]

Converting a list of items to a CSV string

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

Functions

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

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

Mapping Function Calls based on Type

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: <https://docs.microsoft.com/en-us/power-query/helperfunctions#tablechangetype>
            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
Categories
Cheatsheet Command Line Excel Power BI Power Query PowerShell

Big List of Cheat sheets and References

Web

XPath

OData

HTTP

Other: Web

Encoding

Unicode.org

Emoji

Table of Contents: Emoji Charts Table

Apps

VS Code

Firefox

Windows Terminal

Excel

Other: Apps

  • .

git

Misc

Programming and Languages

PowerShell

gist/vexx32/PowershellLoopBehavior.md

Power Query

Dotnet / .Net

Linux

Bash

Environment Vars

Categories
Power BI Power Query

Web.Contents: Using Dynamic and Duplicate key names in a Query

Power BI Discord asked the question:
> How do you use duplicate keys, and dynamic urls with <a href="https://docs.microsoft.com/en-us/powerquery-m/web-contents">Web.Contents</a> ?
(They were using a web API that required duplicate keys)
After I wrote this, Chris Webb found an easier solution.

Requirements

  1. You can’t use a regular record because keys must be distinct. Query = [ Key1 = 1, Key1 = 10] will throw an error.
  2. You can’t put the **dynamic url** in the first argument Web.Contents or else refreshes can break

I built a RelativePath by Uri-escaping a list.

Building RelativePath by Uri-escaping a list

The 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 Returnq=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.

Easier Solution

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"}
]

Tips from Chris Webb

Using Optional Parameters with Query

Chris has a tip to optionally use null query parameters.
If set to an empty list, {} — the request drops the parameter.
Web.Contents(
    "http://jsonplaceholder.typicode.com/comments",
    [Query = [postId = {}] ]
)

Which results in the url: <a href="http://jsonplaceholder.typicode.com/comments">http://jsonplaceholder.typicode.com/comments</a>
This is a good place to use Power Query‘s null coalesce operator (Which isn’t in the official docs)

Query = [postId = myPostId ?? {}]

Capturing HTTP Requests without Fiddler

The Query Editor is an alternate to using Fiddler to capture web requests.
Categories
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

Categories
Excel Power BI Power Query

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.

That’s more than I expected.

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
Categories
Power BI Power Query References And Cheat Sheets What's New

Searching Constants Defined in Power Query

Searching Power Query Language Functions and Constants