Category: Uncategorized

Categories
Adventures In Power Query Experiment Formatting Uncategorized What Not To Do

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

Bad Things

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.

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


(

              )
                            
                                             [    
     user  



]

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

let
    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

let
    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

let
    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 } 
in 
    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.
Details:
    Infinity

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

Categories
Uncategorized

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

Categories
Adventures In Power Query Uncategorized

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

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

Summary

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: https://httpbin.org/json
If you add a query using the Web UI, it combines “download raw bytes” and “convert to json” as one step. 


let
    Source = Json.Document(Web.Contents("https://httpbin.org", [RelativePath = "/json"] ))
in
    Source

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


let
    ResponseBytes = Web.Contents("https://httpbin.org", [RelativePath = "/json"] ),
    Source = Json.Document( ResponseBytes )
in
    Source

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      = "https://httpbin.org",
    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

Inspecting Function “subtypes” in Power Query

Related Links

Related Functions

Categories
Uncategorized

Animations 2022-10

VS Code feature

WindowsTerminal

windowsTerminal using "experimental.useBackgroundImageForWindow": true,

Vs Code Features

using multiple cursors

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:

Power Query M Primer (Part 20): Metadata

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

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

Related Links

Related Functions

Describing a Function’s Record Parameters
Power Query M Primer (Part 18): Type System III – Custom Types